djjamiegee Posted February 22, 2009 Share Posted February 22, 2009 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>"; } ?> Link to comment https://forums.phpfreaks.com/topic/146303-solved-why-wont-my-query-order-by/ Share on other sites More sharing options...
Philip Posted February 22, 2009 Share Posted February 22, 2009 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" Link to comment https://forums.phpfreaks.com/topic/146303-solved-why-wont-my-query-order-by/#findComment-768094 Share on other sites More sharing options...
djjamiegee Posted February 22, 2009 Author Share Posted February 22, 2009 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 Link to comment https://forums.phpfreaks.com/topic/146303-solved-why-wont-my-query-order-by/#findComment-768101 Share on other sites More sharing options...
Philip Posted February 22, 2009 Share Posted February 22, 2009 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. Link to comment https://forums.phpfreaks.com/topic/146303-solved-why-wont-my-query-order-by/#findComment-768102 Share on other sites More sharing options...
djjamiegee Posted February 22, 2009 Author Share Posted February 22, 2009 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 Link to comment https://forums.phpfreaks.com/topic/146303-solved-why-wont-my-query-order-by/#findComment-768105 Share on other sites More sharing options...
Philip Posted February 22, 2009 Share Posted February 22, 2009 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 Link to comment https://forums.phpfreaks.com/topic/146303-solved-why-wont-my-query-order-by/#findComment-768107 Share on other sites More sharing options...
djjamiegee Posted February 22, 2009 Author Share Posted February 22, 2009 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 Link to comment https://forums.phpfreaks.com/topic/146303-solved-why-wont-my-query-order-by/#findComment-768112 Share on other sites More sharing options...
Philip Posted February 22, 2009 Share Posted February 22, 2009 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()); Link to comment https://forums.phpfreaks.com/topic/146303-solved-why-wont-my-query-order-by/#findComment-768153 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.