Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 4th, 2003, 12:47 PM
Authorized User
 
Join Date: Sep 2003
Location: London, , United Kingdom.
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Jams30
Default Query not returning correct results from database

Hi

I have created a simple form to do searches on, but on submission the results are not correct and I cannot work out why!!

I have 2 php pages the first is as follows:

<HTML>

<HEAD><TITLE>Search for your property</TITLE></HEAD>
<BODY>
<p align="right"><strong><<<<< BASIC SEARCH</strong></p>
<p> </p>
<p align="left"> <strong>[font=Century Gothic]

<?php

# FROM AND TO PRICES LISTBOXES

echo "<FORM METHOD=POST ACTION='propertyfinder.php'>";
echo "Basic property search test base";
echo "<BR><BR>";
echo "Price?";
echo "<BR><BR>";
echo "From: ";
echo "<SELECT NAME='From_Price'>";
$Bottom=array("85000", "95000", "110000", "120000", "130000", "140000", "150000", "160000", "170000", "180000", "190000", "220000");
echo "<OPTION VALUE=$Bottom[0]>$Bottom[0]</OPTION>";
echo "<OPTION VALUE=$Bottom[1]>$Bottom[1]</OPTION>";
echo "<OPTION VALUE=$Bottom[2]>$Bottom[2]</OPTION>";
echo "<OPTION VALUE=$Bottom[3]>$Bottom[3]</OPTION>";
echo "<OPTION VALUE=$Bottom[4]>$Bottom[4]</OPTION>";
echo "<OPTION VALUE=$Bottom[5]>$Bottom[5]</OPTION>";
echo "<OPTION VALUE=$Bottom[6]>$Bottom[6]</OPTION>";
echo "<OPTION VALUE=$Bottom[7]>$Bottom[7]</OPTION>";
echo "<OPTION VALUE=$Bottom>$Bottom</OPTION>";
echo "<OPTION VALUE=$Bottom[9]>$Bottom[9]</OPTION>";
echo "<OPTION VALUE=$Bottom[10]>$Bottom[10]</OPTION>";
echo "<OPTION VALUE=$Bottom[11]>$Bottom[11]</OPTION>";
echo "</SELECT>";

echo " ";

echo "To: ";
echo "<SELECT NAME='To_Price'>";
$Top=array("85000", "95000", "110000", "120000", "130000", "140000", "150000", "160000", "170000", "180000", "190000", "220000");
echo "<OPTION VALUE=$Top[0]>$Top[0]</OPTION>";
echo "<OPTION VALUE=$Top[1]>$Top[1]</OPTION>";
echo "<OPTION VALUE=$Top[2]>$Top[2]</OPTION>";
echo "<OPTION VALUE=$Top[3]>$Top[3]</OPTION>";
echo "<OPTION VALUE=$Top[4]>$Top[4]</OPTION>";
echo "<OPTION VALUE=$Top[5]>$Top[5]</OPTION>";
echo "<OPTION VALUE=$Top[6]>$Top[6]</OPTION>";
echo "<OPTION VALUE=$Top[7]>$Top[7]</OPTION>";
echo "<OPTION VALUE=$Top>$Top</OPTION>";
echo "<OPTION VALUE=$Top[9]>$Top[9]</OPTION>";
echo "<OPTION VALUE=$Top[10]>$Top[10]</OPTION>";
echo "<OPTION VALUE=$Top[11]>$Top[11]</OPTION>";
echo "</SELECT><BR><BR>";

# BEDROOMS DROP DOWN LIST BOX

echo "Minimum number of bedrooms?";
echo "<BR><BR>";
echo "<SELECT NAME='Bedrooms'>";
$Rooms=array("1", "2", "3", "4", "5", "6");
echo "<OPTION VALUE=$Rooms[0]>$Rooms[0]</OPTION>";
echo "<OPTION VALUE=$Rooms[1]>$Rooms[1]</OPTION>";
echo "<OPTION VALUE=$Rooms[2]>$Rooms[2]</OPTION>";
echo "<OPTION VALUE=$Rooms[3]>$Rooms[3]</OPTION>";
echo "<OPTION VALUE=$Rooms[4]>$Rooms[4]</OPTION>";
echo "<OPTION VALUE=$Rooms[5]>$Rooms[5]</OPTION>";
echo "</SELECT>";
echo "<BR><BR>";

# PROPERTY TYPE DROP DOWN LIST

echo "Property type?";
echo "<BR><BR>";
echo "<SELECT NAME='Prop_Type'>";
$Type=array("house", "bungalow", "flat", "caravan", "cardboard_box", "other");
echo "<OPTION VALUE=$Type[0]>$Type[0]</OPTION>";
echo "<OPTION VALUE=$Type[1]>$Type[1]</OPTION>";
echo "<OPTION VALUE=$Type[2]>$Type[2]</OPTION>";
echo "<OPTION VALUE=$Type[3]>$Type[3]</OPTION>";
echo "<OPTION VALUE=$Type[4]>$Type[4]</OPTION>";
echo "<OPTION VALUE=$Type[5]>$Type[5]</OPTION>";
echo "</SELECT>";
echo "<BR><BR>";

# LOCATION INFORMATION

echo "Location?";
echo "<BR><BR>";
echo "<INPUT NAME='Location' TYPE='TEXT'>";
echo "<BR><BR>";
echo "<INPUT TYPE=SUBMIT VALUE='Find a property!!'>";
echo "</FORM>";
?>
</strong>
</BODY>
</HTML>

The second being:

<?php
include "./common_db2.inc";

echo "<p align='right'><strong><<<<< YOUR RESULTS</strong></p>";
echo "<p> </p>";
echo "[center]<p><strong>You have searched for a " . $_REQUEST['Prop_Type'] . " with " . $_REQUEST['Bedrooms'] . " bedroom(s) priced from $" . $_REQUEST['From_Price'] . " to $". $_REQUEST['To_Price'] . "</strong></p>";

if ($_REQUEST['From_Price'] > $_REQUEST['To_Price']) {
echo("<p><div align='center'>[font=Century Gothic]<strong>Your Starting Price is higher than your Maximum Price!</strong></div></p>");
echo "<p> </p>";
echo "<p> </p>";
echo "<strong><a href='listbox2.php'>< Amend my Search ></a></strong>";
exit();
}

$link_id = db_connect();
$result = mysql_query("SELECT * FROM prices WHERE price >= $_POST[From_Price] AND price <= $_POST[To_Price] AND bedrooms >= $_POST[Bedrooms] AND prop_type = '$_POST[Prop_Type]' ORDER BY price", $link_id);
$row = mysql_fetch_array($result);

if (!$row) {
echo("<p><div align='center'>[font=Century Gothic]<strong>Sorry, no matching properties found!</strong></div></p>");
echo "<p> </p>";
echo "<p> </p>";
echo "<strong><a href='listbox2.php'>< Try Another Search ></a></strong>";
exit();

}

while($query_data = mysql_fetch_row($result)) {
echo "Property no ",$query_data[0]," is a ",$query_data[3]," and is priced $",$query_data[1],","," and has ",$query_data[2]," bedroom(s)","<P>";
}

echo mysql_fetch_array($result);

echo "<strong><a href='listbox2.php'>< Try Another Search ></a></strong>";


?>

When I do a search on values from 110000 to 110000 I don’t get a result, nor do I get a message saying ‘Sorry, no matching properties found!’. This tells me that the query is finding a result despite it not being displayed. This also happens with other searches where results are inaccurate – my database has approx 60 records with a variety of values varying from 110000 to 205000.

Sorry that this post is so long, I thought that I should give as much information as possible!!

I anyone has any idea on where I’m going wrong, please let me know.

Many thanks in advance

Jamal


Reply With Quote
  #2 (permalink)  
Old November 4th, 2003, 01:56 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

First of all, you have an improper validity check. If mysql_query() returns FALSE, you'll get an error when calling mysql_fetch_array($result).

Your problem is that you ALWAYS throw away your first row of results. Look closely:

Quote:
quote:Originally posted by Jams30

$link_id = db_connect();
$result = mysql_query("SELECT * FROM prices ...");
$row = mysql_fetch_array($result);

if (!$row) {
   echo("Sorry, no matching properties found!");
   ...
   exit();
}

while($query_data = mysql_fetch_row($result))
{
    echo "Property no ",$query_data[0],"...";
    ...
}

echo mysql_fetch_array($result);

See the problem? mysql_fetch_array/row/assoc() all advance the internal result set pointer, so that each time it's called, it returns the next unfetched row in the result set, or FALSE if there are no more.

Your code does this:

1: Give me the first row in the result set.
2: If that first row is empty, exit.
3: While there are more rows in the result set, echo them to the user. The loop finishes when there are no more rows.
4: Echo the next row in the result set.

You should be able to immediately see at least TWO problems with this. The first being that you're throwing away the first row for your if(!$row) check. The second being that your while() loop iterates through ALL the rows until mysql_fetch_row() returns false, then you immediately call this: echo mysql_fetch_row($result);

Doesn't make sense.

Suggested rewrite:

Code:
$link_id = db_connect();
$result = mysql_query("SELECT * FROM prices ...");

if (FALSE === $result)
{
    echo("Sorry, no matching properties found!");
    ...
    exit();
}

while($query_data = mysql_fetch_row($result))
{
    echo "Property no ",$query_data[0]," ...;
}
Further suggestions:

You should use a period, not a comma, to concatenate strings. You should use mysql_fetch_array() or mysql_fetch_assoc() to return the result rows with string array indexes. Using numeric indexes will cause your application to break miserably if you modify the database schema for your tables, and is much less readable.

Compare yours:

echo "Property no ",$query_data[0]," is a ",$query_data[3]," and is priced $",$query_data[1],","," and has ",$query_data[2]," bedroom(s)","<P>";

with mine:

echo "Property no {$row['number']} "
   . "is a {$row['type'} "
   . "and is priced ${$row['price']},"
   . "and has {$row['rooms']} bedroom(s)<P>";


There is simply no reason (or excuse!!) for string snippets like this to be concatenated:

  priced $",$query_data[1],","," and has ",$query_data[2]," bedroom(s)","<P>";

Look at those commas in the middle and tell me honestly whether they make sense! And the tail end of the string... why is <P> not just part of the string?

Remember -- whitespace is YOUR FRIEND. The more your code is well-formatted and organized, the easier it is to read and comprehend and spot mistakes.



Take care,

Nik
http://www.bigaction.org/
Reply With Quote
  #3 (permalink)  
Old November 5th, 2003, 01:42 PM
Authorized User
 
Join Date: Sep 2003
Location: London, , United Kingdom.
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Jams30
Default

Hi Nik

Thanks for your help, I have modified my code so that it looks like this:

<?php
include "./common_db2.inc";

echo "<p align='right'><strong><<<<< YOUR RESULTS</strong></p>";
echo "<p> </p>";
echo "[center]<p><strong>You have searched for a " . $_REQUEST['Prop_Type'] . " with " . $_REQUEST['Bedrooms'] . " bedroom(s) priced from $" . $_REQUEST['From_Price'] . " to $". $_REQUEST['To_Price'] . "</strong></p>";

if ($_REQUEST['From_Price'] > $_REQUEST['To_Price']) {
echo("<p><div align='center'>[font=Century Gothic]<strong>Your Starting Price is higher than your Maximum Price!</strong></div></p>");

echo "<strong><a href='listbox2.php'>< Amend my Search ></a></strong>";
exit();
}

$link_id = db_connect();
$result = mysql_query("SELECT * FROM prices WHERE price >= $_POST[From_Price] AND price <= $_POST[To_Price] AND bedrooms >= $_POST[Bedrooms] AND prop_type = '$_POST[Prop_Type]' ORDER BY price", $link_id);

if (FALSE === $result)
{
    echo "Sorry, no matching properties found!";
    echo "<strong><a href='listbox2.php'>< Try Another Search ></a></strong>";
    exit();
}

while($query_data = mysql_fetch_row($result))
{
echo "Property no $query_data[0] " . "is a $query_data[3] " . "and is priced $$query_data[1] " . "and has $query_data[2] bedroom(s)<P>";
}

echo mysql_fetch_array($result);

echo "<strong><a href='listbox2.php'>< Try Another Search ></a></strong>";

?>

Admittedly, using periods to concatenate the string makes for far easier reading. The search now performs as it should do, however I cannot get the validity check (as highlighted) to perform as intended where if no records are found matching the search criteria a message is echoed to the page with words to that effect.

I'm still quite vague on how this all works and am wondering if it's a case of going through the book again until I get a better understanding of it all? Any ideas/suggestions would be appreciated as I'm feeling a little lost in terms of my progress with PHP as a first timer!!

Thanks again

Jamal

Reply With Quote
  #4 (permalink)  
Old November 5th, 2003, 04:10 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Oh crap, okay. That's my fault.

Result will be FALSE only when there's a problem running the query. If the query runs correctly, but there are no rows in the result set, $result will be a valid result resource associated with an empty result set.

The correct version is this:

if (FALSE === $result)
{
    echo "There was a problem with the database query.\n";
    echo "MySQL said: " . mysql_error();
    exit;
}
else if (mysql_num_rows($result) == 0)
{
    echo "Sorry, no matching properties found!";
    echo "<strong><a href='listbox2.php'>< Try Another Search ></a></strong>";
    exit();
}


For more information, read these relevant manual pages:
  http://www.php.net/mysql_query
  http://www.php.net/mysql_num_rows



Take care,

Nik
http://www.bigaction.org/
Reply With Quote
  #5 (permalink)  
Old November 5th, 2003, 04:12 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also -- you should remove this line:

 echo mysql_fetch_array($result);

because it's useless. mysql_fetch_array() at that point in your script is guaranteed to return FALSE, which is converted to an empty string when you echo it.


Take care,

Nik
http://www.bigaction.org/
Reply With Quote
  #6 (permalink)  
Old November 7th, 2003, 08:55 AM
Authorized User
 
Join Date: Sep 2003
Location: London, , United Kingdom.
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Jams30
Default

Thanks again Nik!!

Using your suggestions, I have managed to get the code working the way that I'd like it to. I'm now in the process of taking it apart again, so that I can get a better understanding of it all!


Regards

Jamal

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Temp Table Query not returning results to page rlull SQL Server 2000 1 November 15th, 2005 04:50 PM
Query returning 0 results although SQL is valid aquagal77 Classic ASP Databases 4 March 31st, 2005 11:06 AM
SQL count query not giving correct results hman SQL Language 2 March 16th, 2005 07:06 AM
SQL count query not giving correct results hman SQL Server 2000 1 March 15th, 2005 01:15 PM
Returning Oracle SELECT query results to DataSet gp_mk ADO.NET 0 December 17th, 2003 11:08 AM



All times are GMT -4. The time now is 10:23 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.