473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.
Expand|Select|Wrap|Line Numbers
  1. <html>
  2. <head>
  3. <style type="text/css">
  4. .page_numbers { 
  5.     width: 600px; 
  6.     padding: 5px 0px; 
  7.     float:left;
  8.     clear:left;
  9.     margin:0 auto; 
  10.  
  11. .page_numbers ul { 
  12.     margin: 0 auto; 
  13.     list-style-type: none; 
  14.     padding: 0px; 
  15.     text-align: center; 
  16.  
  17. .page_numbers li { 
  18.     display: inline; 
  19.     float: left; 
  20.     margin:1px; 
  21.     background: #a7a7a7; 
  22.     width:25px; 
  23.  
  24. .page_numbers li.current{ 
  25.   width:50px; 
  26.  
  27. .page_numbers li a { 
  28.     background: #fff; 
  29.     border: 1px solid #a7a7a7; 
  30.     padding: 1px; 
  31.     text-decoration: none; 
  32.     color: #000; 
  33.     font:bold 8px verdana,sans-serif; 
  34.     display:block; 
  35.  
  36. .page_numbers a.current, .page_numbers li a:hover { 
  37.     background: #a7a7a7; 
  38.     color: #fff; 
  39. }
  40. </style>
  41. </head>
  42. <body>
  43. <?php 
  44.  
  45. include "connection.php";
  46.  
  47. $min_price = $_POST['min_price'];
  48. $max_price = $_POST['max_price'];
  49. $property_type = $_POST['type'];
  50. $location = $_POST['location'];
  51. $min_bedrooms = $_POST['min_beds'];
  52. $keywords = $_POST['keywords'];
  53.  
  54.  
  55. //$min_bedrooms = 2;
  56.  
  57. $webpage = basename($_SERVER['PHP_SELF']); 
  58. function pagination_five($total_pages,$page){ 
  59.  
  60.     global $webpage, $min_price, $max_price, $property_type;
  61.     global $location, $min_bedrooms, $keywords;  
  62.  
  63.     $max_links = 10; 
  64.     $h=1; 
  65.     if($page>$max_links){ 
  66.         $h=(($h+$page)-$max_links); 
  67.     } 
  68.     if($page>=1){ 
  69.         $max_links = $max_links+($page-1); 
  70.     } 
  71.     if($max_links>$total_pages){ 
  72.         $max_links=$total_pages+1; 
  73.     } 
  74.     if($total_pages>1){ 
  75.     echo '<div class="page_numbers"><ul>'; 
  76.     if($page>"1"){ 
  77.         echo '<li class="current"><a href="'.$webpage.'?page=1">First</a></li> 
  78.             <li class="current"><a href="'.$webpage.'?page='.($page-1).'">Prev</a></li> 
  79.         '; 
  80.     } 
  81.  
  82.     if($total_pages!=1){ 
  83.         for ($i=$h;$i<$max_links;$i++){ 
  84.         if($i==$page){ 
  85.             echo '<li><a class="current">'.$i.'</a></li>'; 
  86.         } 
  87.         else{ 
  88.             echo '<li><a href="'.$webpage.'?page='.$i.'">'.$i.'</a> </li>'; 
  89.         } 
  90.         } 
  91.     } 
  92.  
  93.     if(($page >="1")&&($page!=$total_pages)){ 
  94.         echo '<li class="current"><a href="'.$webpage.'?page='.($page+1).'">Next</a></li> 
  95.             <li class="current"><a href="'.$webpage.'?page='.$total_pages.'">Last</a></li> 
  96.         '; 
  97.     } 
  98.     echo '</ul></div>';
  99.     }  
  100. }
  101.  
  102. $result = mysql_query("Select count(*) from search WHERE bedrooms = $min_bedrooms") 
  103. or die (mysql_error()); 
  104. $numrows = mysql_fetch_row($result); 
  105.  
  106. if(isset($_GET['pagenum'])?$page = $_GET['pagenum']:$page = 1); 
  107. $entries_per_page = 1;   
  108.  
  109. $total_pages = ceil($numrows[0]/$entries_per_page); 
  110. $offset = (($page * $entries_per_page) - $entries_per_page); 
  111.  
  112.     //after we have $total_pages and $page, we can include the  
  113.     //pagination style wherever we want on the page. 
  114.     //so far there is no output from the script, so we could have  
  115.     //pagination before or after the pages results 
  116.  
  117.     //before the results  
  118.  
  119. $result = mysql_query("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
  120.     (price BETWEEN '$min_price' AND '$max_price' AND bedrooms >= '$min_bedrooms' AND location = '$location' AND type = '$property_type') ORDER BY id ASC LIMIT  
  121.                        $offset,$entries_per_page");
  122.   if(!$result) die(mysql_error()); 
  123.      $err = mysql_num_rows($result); 
  124.        if($err == 0) die("No matches met your criteria."); 
  125.  
  126. while($row=mysql_fetch_array($result)){ 
  127.  
  128. $formatted = number_format($row['price'],2);
  129.  
  130. echo "<div id=\"right\"><img src=\"".$row['image']."\"><a href=\"detail.php\">".$row['title']."</a><h4>Offers over £".$formatted."</h4><p>".$row['type'].", ".$row['bedrooms']." bedrooms</p><br /></div>\n";
  131.  
  132.  
  133.   //or after the results 
  134.  
  135. pagination_five($total_pages,$page);  
  136.  
  137. ?>
  138. </body>
  139. </html>
  140.  
Oct 2 '07 #1
7 2397
dafodil
392 256MB
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:

Expand|Select|Wrap|Line Numbers
  1. $result = mysql_query("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
  2.     (price BETWEEN '$min_price' AND '$max_price' AND bedrooms >= '$min_bedrooms' AND location = '$location' AND type = '$property_type') ORDER BY id ASC LIMIT  
  3.                        $offset,$entries_per_page");
  4.  
Oct 2 '07 #2
Atli
5,058 Expert 4TB
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
Oct 2 '07 #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
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.
Oct 2 '07 #4
Atli
5,058 Expert 4TB
I thought this must be a problem with you SQL, so I copied it and re-formatted it so it could be read:
Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM search 
  3. WHERE (
  4.     price BETWEEN '$min_price' AND '$max_price' 
  5.     AND bedrooms >= '$min_bedrooms' 
  6.     AND location = '$location' 
  7.     AND type = '$property_type' 
  8.     AND keywords LIKE '%$keywords%'
  9. OR (
  10.     price BETWEEN '$min_price' AND '$max_price' 
  11.     AND bedrooms >= '$min_bedrooms' 
  12.     AND location = '$location' 
  13.     AND type = '$property_type'
  14. ORDER BY id ASC 
  15. LIMIT $offset,$entries_per_page
  16.  
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).
Oct 3 '07 #5
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
Oct 3 '07 #6
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.
Oct 3 '07 #7
Atli
5,058 Expert 4TB
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_globals directive, which, for security and performance reasons, is disabled by default.
Oct 3 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
2
by: Noel Wood | last post by:
Hello I keep getting a warning ... Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in "name of my php file" when I run this query.... SELECT Menu_Item,...
2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
4
by: Federico | last post by:
Hi everybody, I'm evaluating the possibility of using MySQL 5.0 as a database backend for an application we are doing and I'd like to have hardware requirements rough estimates and/or real world...
4
by: Hemant Shah | last post by:
Folks, I am having problem with an application that uses static SQL, the application basically browses through the table given start and end key most of the time it is processed from begining to...
3
by: Taras_96 | last post by:
Hi everyone, I'm having a bit of trouble understanding the purpose of escaping nulls, and the use of addcslashes. Firstly, the manual states that: "Strictly speaking, MySQL requires only...
7
by: eholz1 | last post by:
Hello PHP group, Could someone help me out? I have PHP 5.2, Apache 2.0, and MySQL 5.0 running on Linux (Redhat Fedora Core 6). All that works fine. I would like to be able to "call" a stored...
1
by: thepresidentis | last post by:
here is my problem, i have a website that compiles concert listings for users to search for shows in their local area, i ran into a problem with returning search results due to an improper format...
3
by: Shiriah | last post by:
I feel that this is a rather trivial concept that I cannot quite wrap my head around to make work in PHP. I consider myself an intermediate user in SQL (both MSSQL and MySQL) and somewhat novice in...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.