Query not returning correct results from database

Jams30 · November 4th, 2003, 12:47 PM

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

nikolai · November 4th, 2003, 01:56 PM

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/

Jams30 · November 5th, 2003, 01:42 PM

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

nikolai · November 5th, 2003, 04:10 PM

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/

nikolai · November 5th, 2003, 04:12 PM

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/

Jams30 · November 7th, 2003, 08:55 AM

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