[SOLVED] why wont my query ORDER BY

[djjamiegee]

hiya

 

i have the following code working how i want it to apart from the order by max price bit.

 

any suggestions???

 

  <?php
$type=($_POST['type']=="") ? "" : "type = '{$_POST['type']}' AND";
$bedrooms=$_POST['bedrooms'];
$maxprice=($_POST['maxprice']=="") ? "" : "maxprice < '{$_POST['maxprice']}' AND";
$area=($_POST['area']=="") ? "" : "area = '{$_POST['area']}' ORDER BY";

//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("*****","*****","*****"); 

//select which database you want to edit
mysql_select_db("****"); 


//POST the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search 
$result = mysql_query("SELECT * FROM properties WHERE $type bedrooms >= '$bedrooms' AND $maxprice $area maxprice");

//how many properties found
$number=mysql_num_rows($result);
echo "$number properties found"; 
echo "<br><br>";	

if (mysql_num_rows($result) > 0) {
//grab all the content
while($r=mysql_fetch_array($result))
{	
   $type=$r["type"];
   $bedrooms=$r["bedrooms"];
   $maxprice=$r["maxprice"];
   $area=$r["area"];
   $first_address=$r["first_address"];
   $link=$r["link"];
   $image=$r["image"];
   $postcode=$r["postcode"];
   $lease=$r["lease"];
   $desc=$r["desc"];
  
  //display the row

echo "<table border='0' cellpadding='5' align='center' width='650' class='search'>";
echo "<tr>";
echo "<td width='100'><img src=\"" . $r["image"] .  "\"></td>";
echo "<td colspan='5' valign='top'>$desc</td>";
echo "<td align='right'><a href=\"" . $r["link"] . "\"><img src=\"info.png" . "\" border=0 alt=\"" . "\"></a></td>";
echo "</tr>";
echo "<tr>";
echo "<td width='100' rowspan='2' align='center'><font size='3'><b>£$maxprice</b></font></td>";
echo "<td width='130' colspan='2' align='left'><b>No of Bedrooms:</b> $bedrooms</td>";
echo "<td width='130' colspan='2' align='left'><b>Location:</b> $area</td>";
echo "<td width='240' colspan='2' align='left'><b>Lease Type:</b> $lease</td>";
echo "</tr>";
echo "<td width='130' colspan='2' align='left'><b>Property Type:</b> $type</td>";
echo "<td width='130' colspan='2' align='left'><b>Street:</b> $first_address</td>";
echo "<td width='240' colspan='2' align='left'><b>Post Code:</b> $postcode</td>";
echo "</tr>";
echo "</table>";
echo "<br>";
}}

else {
echo "Sorry no properties were found using your search requirements, Please narrow your prefrences to return more results.<br><br>";
}

?>

[Philip]

Well, there are a few things wrong with that query.

 

first of all, it's always a bad idea to have direct user input into a database.

 

$type=($_POST['type']=="") ? "" : "type = '{$_POST['type']}' AND";
$bedrooms=$_POST['bedrooms'];
$maxprice=($_POST['maxprice']=="") ? "" : "maxprice < '{$_POST['maxprice']}' AND";
$area=($_POST['area']=="") ? "" : "area = '{$_POST['area']}' ORDER BY";

//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("*****","*****","*****"); 

//select which database you want to edit
mysql_select_db("****"); 


//POST the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search 
$result = mysql_query("SELECT * FROM properties WHERE $type bedrooms >= '$bedrooms' AND $maxprice $area maxprice");

 

Change that to:

$type=($_POST['type']=="") ? "" : "type = '{$_POST['type']}' AND";
$bedrooms=$_POST['bedrooms'];
$maxprice=($_POST['maxprice']=="") ? "" : "maxprice < '{$_POST['maxprice']}' AND";
$area=($_POST['area']=="") ? "" : "area = '{$_POST['area']}' ORDER BY";

//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("*****","*****","*****"); 

//select which database you want to edit
mysql_select_db("****"); 


//POST the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search 

$Query = "SELECT * FROM properties WHERE $type bedrooms >= '$bedrooms' AND $maxprice $area maxprice";
echo $Query;
$result = mysql_query($Query) or die(mysql_error());

 

Maybe then you'll see the problem - especially when maxprice isn't empty, but area is.... You'd get something like "maxprice < 500000 AND maxprice"

 

[djjamiegee]

hiya

 

thanks for your reply

 

i have added that and if all the boxes have a choice selected it does the order by maxprice bu if like you said area is empty is doesnt....im still curious to why tho??

 

many thanks

jamie

[Philip]

Because on the end of $area you have ORDER BY, instead of in your query.

 

If you change your original script to this:

$type=($_POST['type']=="") ? "" : "AND type = '{$_POST['type']}'";
$bedrooms=$_POST['bedrooms'];
$maxprice=($_POST['maxprice']=="") ? "" : "AND maxprice < '{$_POST['maxprice']}' AND";
$area=($_POST['area']=="") ? "" : "AND area = '{$_POST['area']} ";

//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("*****","*****","*****"); 

//select which database you want to edit
mysql_select_db("****"); 


//POST the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search 
$result = mysql_query("SELECT * FROM properties WHERE bedrooms >= '$bedrooms' $type $maxprice $area ORDER BY maxprice");

Does it fix it?

 

By the way, you still need to fix the way it is setup with regards to the AND's. I might have maxprice, but not area, and it'll show: "maxprice < 500000 AND ORDER BY maxprice" which is incorrect syntax.

 

EDIT: fixed the AND problem, moving the and's to the font of the variables will allow it to work properly.

[djjamiegee]

hiya

 

i just tried it and all i get on my page now is

 

SELECT * FROM properties WHERE bedrooms >= '1' AND ORDER BY maxpriceYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY maxprice' at line 1

 

cheers

 

jamie

[Philip]

Because on the end of $area you have ORDER BY, instead of in your query.

 

If you change your original script to this:

$type=($_POST['type']=="") ? "" : "AND type = '{$_POST['type']}'";
$bedrooms=$_POST['bedrooms'];
$maxprice=($_POST['maxprice']=="") ? "" : "AND maxprice < '{$_POST['maxprice']}' AND";
$area=($_POST['area']=="") ? "" : "AND area = '{$_POST['area']} ";

//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("*****","*****","*****"); 

//select which database you want to edit
mysql_select_db("****"); 


//POST the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search 
$result = mysql_query("SELECT * FROM properties WHERE bedrooms >= '$bedrooms' $type $maxprice $area ORDER BY maxprice");

Does it fix it?

 

By the way, you still need to fix the way it is setup with regards to the AND's. I might have maxprice, but not area, and it'll show: "maxprice < 500000 AND ORDER BY maxprice" which is incorrect syntax.

 

EDIT: fixed the AND problem, moving the and's to the font of the variables will allow it to work properly.

 

read my edit, and update the code to that

[djjamiegee]

hiya

 

were getting there i have added that an it works better only thing now is when i change the area box my choice i get an error

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in E:\domains\d\djjamiegee.com\user\htdocs\pps\finderresults.php on line 56

properties found

 

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in E:\domains\d\djjamiegee.com\user\htdocs\pps\finderresults.php on line 60

Sorry no properties were found using your search requirements, Please narrow your prefrences to return more results.

 

many thanks jamie

[Philip]

Change:

$result = mysql_query("SELECT * FROM properties WHERE bedrooms >= '$bedrooms' $type $maxprice $area ORDER BY maxprice");

 

to:

$result = mysql_query("SELECT * FROM properties WHERE bedrooms >= '$bedrooms' $type $maxprice $area ORDER BY maxprice") or die(mysql_error());