MySQL query not returning expected results.

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • daveeboi
    New Member
    • Oct 2007
    • 18
    #1

    MySQL query not returning expected results.

    ..I have been strugling to get this part of my site working correctly even though I can't see anything wrong with my code. I am trying to search a database and display paged results. But everytime I perform a valid test expecting a few results to appear I am left with the No Matches message, so it's not a coding error as such. The code shown below is what I have for my search.php, please could someone put me out of my misery.
    [code=php]
    <html>
    <head>
    <style type="text/css">
    .page_numbers {
    width: 600px;
    padding: 5px 0px;
    float:left;
    clear:left;
    margin:0 auto;
    }

    .page_numbers ul {
    margin: 0 auto;
    list-style-type: none;
    padding: 0px;
    text-align: center;
    }

    .page_numbers li {
    display: inline;
    float: left;
    margin:1px;
    background: #a7a7a7;
    width:25px;
    }

    .page_numbers li.current{
    width:50px;
    }

    .page_numbers li a {
    background: #fff;
    border: 1px solid #a7a7a7;
    padding: 1px;
    text-decoration: none;
    color: #000;
    font:bold 8px verdana,sans-serif;
    display:block;
    }

    .page_numbers a.current, .page_numbers li a:hover {
    background: #a7a7a7;
    color: #fff;
    }
    </style>
    </head>
    <body>
    <?php

    include "connection.php ";

    $min_price = $_POST['min_price'];
    $max_price = $_POST['max_price'];
    $property_type = $_POST['type'];
    $location = $_POST['location'];
    $min_bedrooms = $_POST['min_beds'];
    $keywords = $_POST['keywords'];


    //$min_bedrooms = 2;

    $webpage = basename($_SERV ER['PHP_SELF']);
    function pagination_five ($total_pages,$ page){

    global $webpage, $min_price, $max_price, $property_type;
    global $location, $min_bedrooms, $keywords;

    $max_links = 10;
    $h=1;
    if($page>$max_l inks){
    $h=(($h+$page)-$max_links);
    }
    if($page>=1){
    $max_links = $max_links+($pa ge-1);
    }
    if($max_links>$ total_pages){
    $max_links=$tot al_pages+1;
    }
    if($total_pages >1){
    echo '<div class="page_num bers"><ul>';
    if($page>"1"){
    echo '<li class="current" ><a href="'.$webpag e.'?page=1">Fir st</a></li>
    <li class="current" ><a href="'.$webpag e.'?page='.($pa ge-1).'">Prev</a></li>
    ';
    }

    if($total_pages !=1){
    for ($i=$h;$i<$max_ links;$i++){
    if($i==$page){
    echo '<li><a class="current" >'.$i.'</a></li>';
    }
    else{
    echo '<li><a href="'.$webpag e.'?page='.$i.' ">'.$i.'</a> </li>';
    }
    }
    }

    if(($page >="1")&&($page! =$total_pages)) {
    echo '<li class="current" ><a href="'.$webpag e.'?page='.($pa ge+1).'">Next</a></li>
    <li class="current" ><a href="'.$webpag e.'?page='.$tot al_pages.'">Las t</a></li>
    ';
    }
    echo '</ul></div>';
    }
    }

    $result = mysql_query("Se lect count(*) from search WHERE bedrooms = $min_bedrooms")
    or die (mysql_error()) ;
    $numrows = mysql_fetch_row ($result);

    if(isset($_GET['pagenum'])?$page = $_GET['pagenum']:$page = 1);
    $entries_per_pa ge = 1;

    $total_pages = ceil($numrows[0]/$entries_per_pa ge);
    $offset = (($page * $entries_per_pa ge) - $entries_per_pa ge);

    //after we have $total_pages and $page, we can include the
    //pagination style wherever we want on the page.
    //so far there is no output from the script, so we could have
    //pagination before or after the pages results

    //before the results

    $result = mysql_query("SE LECT * FROM search WHERE (price BETWEEN '$min_price' AND '$max_price' AND bedrooms >= '$min_bedrooms' AND location = '$location' AND type = '$property_type ' AND keywords LIKE '%$keywords%') OR
    (price BETWEEN '$min_price' AND '$max_price' AND bedrooms >= '$min_bedrooms' AND location = '$location' AND type = '$property_type ') ORDER BY id ASC LIMIT
    $offset,$entrie s_per_page");
    if(!$result) die(mysql_error ());
    $err = mysql_num_rows( $result);
    if($err == 0) die("No matches met your criteria.");

    while($row=mysq l_fetch_array($ result)){

    $formatted = number_format($ row['price'],2);

    echo "<div id=\"right\"><i mg src=\"".$row['image']."\"><a href=\"detail.p hp\">".$row['title']."</a><h4>Offers over £".$formatted." </h4><p>".$row['type'].", ".$row['bedrooms']." bedrooms</p><br /></div>\n";

    }

    //or after the results

    pagination_five ($total_pages,$ page);

    ?>
    </body>
    </html>
    [/code]
    Last edited by ak1dnar; Oct 2 '07, 03:28 AM. Reason: Added [code=php] to the code
    Tags: None
    • dafodil
      Contributor
      • Jul 2007
      • 389
      #2
      Try some simple searching first, the problem might be in the data inside your tables. Simplify this part first so that you'll know the error:

      [code="php"]
      $result = mysql_query("SE LECT * FROM search WHERE (price BETWEEN '$min_price' AND '$max_price' AND bedrooms >= '$min_bedrooms' AND location = '$location' AND type = '$property_type ' AND keywords LIKE '%$keywords%') OR
      (price BETWEEN '$min_price' AND '$max_price' AND bedrooms >= '$min_bedrooms' AND location = '$location' AND type = '$property_type ') ORDER BY id ASC LIMIT
      $offset,$entrie s_per_page");
      [/code]

        Comment

        • Atli
          Recognized Expert Expert
          • Nov 2006
          • 5062
          #3
          Hi.

          I agree. I can't see anything wrong with the PHP code itself so the SQL query seems to be the cause of your problem.

          Have you tested it on the database?

          P.S.
          I've changed the title of this thread to better describe it's topic.
          Using good, descriptive titles that follow the Posting Guidelines will increase your chances of getting you questions answered!

          Moderator

            Comment

            • daveeboi
              New Member
              • Oct 2007
              • 18
              #4
              Originally posted by Atli
              Hi.

              I agree. I can't see anything wrong with the PHP code itself so the SQL query seems to be the cause of your problem.

              Have you tested it on the database?

              P.S.
              I've changed the title of this thread to better describe it's topic.
              Using good, descriptive titles that follow the Posting Guidelines will increase your chances of getting you questions answered!

              Moderator
              Thanks for the title changeand the advice. I put some echo statements throughout my code to see what the variables were just before entering sql statements and all the variables passed should have given results but still give 'No Matches'. I have triple checked that all the names in the sql statements match that of table fields and now I am totally stuck. I really do not know what is wrong with my code at all.

                Comment

                • Atli
                  Recognized Expert Expert
                  • Nov 2006
                  • 5062
                  #5
                  I thought this must be a problem with you SQL, so I copied it and re-formatted it so it could be read:
                  [code=mysql]
                  SELECT *
                  FROM search
                  WHERE (
                  price BETWEEN '$min_price' AND '$max_price'
                  AND bedrooms >= '$min_bedrooms'
                  AND location = '$location'
                  AND type = '$property_type '
                  AND keywords LIKE '%$keywords%'
                  )
                  OR (
                  price BETWEEN '$min_price' AND '$max_price'
                  AND bedrooms >= '$min_bedrooms'
                  AND location = '$location'
                  AND type = '$property_type '
                  )
                  ORDER BY id ASC
                  LIMIT $offset,$entrie s_per_page
                  [/code]
                  But, that looks all right to.
                  (Are you sure there is any data in the tables? :P)


                  Try removing the LIMIT clause, see what happens.
                  If that doesn't work, try reducing the search criteria until you find the problem.

                  Note, the ORDER BY id ASC is redundant. This is the default behavior. (as far as I know).

                    Comment

                    • robbiesmith79
                      New Member
                      • Oct 2007
                      • 4
                      #6
                      And I noticed a part of your code that could be optimized.

                      1st.

                      $min_price = $_POST["min_price"];
                      $max_price = $_POST["max_price"];
                      etc.

                      = !YUCK!

                      extract($_POST) ; // easy simple way of taking everything from the $_POST array and automatically generates all the variables with their respective $_POST names. The downside is that it takes all the variables in the $_POST array and automatically generates all the variables with their respective $_POST name... if you only need say one of the variables, then you're wasting memory with excessive overuse.

                      i.e. $min_price is already set

                        Comment

                        • daveeboi
                          New Member
                          • Oct 2007
                          • 18
                          #7
                          Thanks for the help, I eventually got the problem sorted by numbering my drop down list option values, so it must've been to do with that.

                            Comment

                            • Atli
                              Recognized Expert Expert
                              • Nov 2006
                              • 5062
                              #8
                              Originally posted by robbiesmith79
                              And I noticed a part of your code that could be optimized.

                              1st.

                              $min_price = $_POST["min_price"];
                              $max_price = $_POST["max_price"];
                              etc.

                              = !YUCK!

                              extract($_POST) ; // easy simple way of taking everything from the $_POST array and automatically generates all the variables with their respective $_POST names. The downside is that it takes all the variables in the $_POST array and automatically generates all the variables with their respective $_POST name... if you only need say one of the variables, then you're wasting memory with excessive overuse.

                              i.e. $min_price is already set
                              Hi.

                              I would recommend against this. It may save you a few lines of code but you are allowing user input into your code without any validation. As well as creating potentially unused variables, as you pointed out.
                              From a security standpoint, you should always use the super-globals ($_POST. $_GET, etc...).

                              What you are suggesting is basically the same as enabling the register_global s directive, which, for security and performance reasons, is disabled by default.

                                Comment

                                Previous template Next
                                Working...