Hi

I am trying to figure out how the information posted by a form can be used to query a MySql database. I have a form with two list boxes, both listing prices - one list box is for 'from price' the other is for 'to price' both have ten entries that have been put into an arrays $Bottom and $Top respectively. The select names being From_Price and To_Price respectively.

When selecting both 'from' and 'to' values from the list boxes, the values are passed to the next page and are referred to by variables:

echo "<center>Your selection of properties are from $From_Price to $To_Price</center><br><br>";

This is fine and the results are as expected, however when trying to use these variables to query the database that I have built which currently contains just the one table (prices):

mysql> select * from prices;
+---------+--------+----------------------------+
| prop_no | price | description |
+---------+--------+----------------------------+
| 1 | 150000 | this is a test property 1 |
| 2 | 160000 | this is a test property 2 |
| 3 | 165000 | this is a test property 3 |
| 4 | 170000 | this is a test property 4 |
| 5 | 168000 | this is a test property 5 |
| 6 | 172000 | this is a test property 6 |
| 7 | 112000 | this is a test property 7 |
| 8 | 117000 | this is a test property 8 |
| 9 | 110000 | this is a test property 9 |
| 10 | 185000 | this is a test property 10 |
| 11 | 205000 | this is a test property 11 |
| 12 | 154000 | this is a test property 12 | ETC....

Using the following code:

...
$link_id = db_connect();
$result = mysql_query("select * from prices WHERE price >= $From_Price and price <= $To_Price order by price", $link_id);

while($query_data = mysql_fetch_row($result)) {
echo "Property no: ",$query_data[0]," is priced at £",$query_data[1]," and the description on this is: ",$query_data[2],"<BR>","<BR>";
...

I get nothing back as a result, if I replace the $From_Price and $To_Price variables with static values the query works returning only those specific values as it should, thus the connection to the database is ok.


Can anyone please advise me on how this might be possible, I have read book over and over but cannot work out how to link form values/variables with data in a MySql database.

Any help would be most appreciated.


Jamal

Well, if the query works using static values, have you double-checked the query you're actually sending to mysql?

$query = "SELECT * FROM prices WHERE price >= '$fromPrice' AND price <= '$toPrice' ORDER BY price";

echo "query is [[[$query]]]\n"; // check your query if it's still not working!!

$result = mysql_query($query);

if(!$result) echo "Query failed! MySQL said: " . mysql_error();

else
{
   while($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
      echo "Property no: {$row['prop_no']} is priced at £{$row['price']} "
         . "and the description on this is {$row['description']}<br /><br />\n";
   }
}


Additional unasked-for comments:

Notice that I use periods (.) to concatenate strings. Your script uses commas to separate string fragments. This doesn't concatenate strings, but rather has echo treat each fragment as a parameter. Echo() is NOT a function, but a language construct, but it can still accept parameters as if it were a function and figure things out. For clarity, you should use periods if you intend to concatenate strings.

Additionally, it's not necessary to break out of a string to concatenate a variable value. I've demonstrated how to use the curly-brace syntax within double-quoted strings to perform variable substitution. For more info, read the manual at:
  http://www.php.net/types.string

Finally, notice also that I use mysql_fetch_array() instead of mysql_fetch_row(). This gives you the convenience of accessing selected rows by their column name, and not by their position in the table. This is generally considered a much better way of doing things, since changes to your database schema are much less likely to break things.


Take care,

Nik
http://www.bigaction.org/