smproph Posted January 16, 2011 Share Posted January 16, 2011 Currently I have where the User can search for records, then the records are displayed on a page. I then have a drop down box to where the user can Order by 2 different categories to make it easier for the user to see things but for some reason it is not working. Here is my code. Oh and the error I get is mysql_num_rows(): supplied argument is not a valid MySQL result resource in <?php $qry_str="SELECT * FROM timeslip WHERE 1 "; if($_POST['Initials']) { $Initials=$_POST['Initials']; $qry_str.="and Initials='$Initials' "; } if($_POST['Identifier']) { $Identifier=$_POST['Identifier']; $qry_str.="and Identifier LIKE '%$Identifier%' "; } if($_POST['Type']) { $Type=$_POST['Type']; $qry_str.="and Type LIKE '%$Type%' "; } if($_POST['Terms']) { $Terms=$_POST['Terms']; $qry_str.="and Terms LIKE '%$Terms%' "; } if($_POST['Memo']) { $Memo=$_POST['Memo']; $qry_str.="and Memo LIKE '%$Memo%' "; } if($_POST['date1']) { $date1=$_POST['date1']; $date2=$_POST['date2']; $start=date('Y-m-d', strtotime($date1)); $end=date('Y-m-d', strtotime($date2)); $qry_str.="and Date >= '$start' and Date <= '$end' "; } if($_POST['order1'] && $_POST['order2']) { $order1=$_POST['order1']; $order2=$_POST['order2']; $qry=$_POST['qry']; $qry_str="$qry ORDER BY $order1 ASC, $order2 ASC "; } if($_POST['order1']) { $order1=$_POST['order1']; $qry=$_POST['qry']; $qry_str="$qry ORDER BY $order1 ASC"; } if($_POST['order2']) { $order2=$_POST['order2']; $qry=$_POST['qry']; $qry_str="$qry ORDER BY $order2' ASC "; } $result=mysql_query($qry_str); $count=mysql_num_rows($result); if ($count>0){ ?> <form action="" method="post"> <input type="hidden" value="<?=$qry_str?>" name="qry"/> Order Results By <select name="order1" > <OPTION value="<?=$order1?>"><?=$order1?></OPTION> <OPTION value="Identifier">Identifier</OPTION> <OPTION value="Type">Type</OPTION> <OPTION value="Terms">Terms</OPTION> <OPTION value="Date">Date</OPTION> <OPTION value="Cost">Cost</OPTION> </select> Then <select name="order2" > <OPTION value="<?=$order2?>"><?=$order2?></OPTION> <OPTION value="Identifier">Identifier</OPTION> <OPTION value="Type">Type</OPTION> <OPTION value="Terms">Terms</OPTION> <OPTION value="Date">Date</OPTION> <OPTION value="Cost">Cost</OPTION> </select> <input type="submit" name="submit"> </form> Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/ Share on other sites More sharing options...
zenlord Posted January 16, 2011 Share Posted January 16, 2011 You forgot a '.' in your definition of $qry_str. Try '$qry_str.=' instead of '$qry_str=' The '.' adds the new content to the existing $qry_str. Without the '.' you're overwriting the content of $qry_str. Zl. Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160373 Share on other sites More sharing options...
smproph Posted January 16, 2011 Author Share Posted January 16, 2011 Well I am wanting to overwrite it when they do the order because the $_POST for their Order By has the SELECT statement from when they first searched already in it. i.e. $qry ORDER BY $order2 ASC. $qry is the SELECT statement originally used. I did add the '.' to see what it did and it did not work, same error. Just doubles my SELECT statement Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160374 Share on other sites More sharing options...
Muddy_Funster Posted January 16, 2011 Share Posted January 16, 2011 what is in $order1 and $order2? could you echo $qry_str before you run it and post what it contains at the point where it would be sent to the database? Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160415 Share on other sites More sharing options...
Pikachu2000 Posted January 16, 2011 Share Posted January 16, 2011 By leaving out the . you overwrite the entire query string. Throw in some error handling, and see what the query string actually holds. if( !$result = mysql_query($qry_str) ) { echo "<br>Query string: $qry_str<br>Failed with error: " . mysql_error() . '<br>'; // Replace with trigger_error() or generic message on production server } else { $count=mysql_num_rows($result); if ($count>0){ Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160417 Share on other sites More sharing options...
smproph Posted January 16, 2011 Author Share Posted January 16, 2011 This is the error I am getting: Query string: SELECT * FROM timeslip WHERE 1 and Initials=''GEC'' and Type LIKE ''%IPC%'' and Terms LIKE ''%HRT%'' ORDER BY Cost ASC Failed with error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GEC'' and Type LIKE ''%IPC%'' and Terms LIKE ''%HRT%'' ORDER BY Cost ASC' at line 1 $order1 and $order2 are the selections the user is choosing to order by. Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160420 Share on other sites More sharing options...
Pikachu2000 Posted January 16, 2011 Share Posted January 16, 2011 Looks like it's getting doubly single-quoted somehow, thereby breaking the syntax. Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160423 Share on other sites More sharing options...
smproph Posted January 16, 2011 Author Share Posted January 16, 2011 After changing it, it seems when I am echoing the qry_str that it is using all 3 if's. Here is the error Query string: SELECT * FROM timeslip WHERE 1 ORDER BY Date ASC, Cost ASC ORDER BY Date ASCORDER BY Cost ASC Failed with error: You 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 Date ASCORDER BY Cost ASC' at line 1 It is supposed to do the 1st IF, if the user chooses to sort by two. Uses second IF if he decides to sort by 1, and 3rd IF if user decides to choose by only the 2nd dropdown if($_POST['order1'] && $_POST['order2']) { $order1=$_POST['order1']; $order2=$_POST['order2']; $qry=$_POST['qry']; $qry_str.="ORDER BY $order1 ASC, $order2 ASC "; } if($_POST['order1']) { $order1=$_POST['order1']; $qry=$_POST['qry']; $qry_str.="ORDER BY $order1 ASC"; } if($_POST['order2']) { $order2=$_POST['order2']; $qry=$_POST['qry']; $qry_str.="ORDER BY $order2 ASC "; } Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160429 Share on other sites More sharing options...
Pikachu2000 Posted January 16, 2011 Share Posted January 16, 2011 Yes, it would; that's the way the logic is written. All 3 if() conditionals are evaluated, so if both conditions are true, all three conditionals return true. Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160432 Share on other sites More sharing options...
smproph Posted January 16, 2011 Author Share Posted January 16, 2011 How would I logically write it to where if only 1st value is submitted then do that, if both are submitted then do that Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160435 Share on other sites More sharing options...
Pikachu2000 Posted January 16, 2011 Share Posted January 16, 2011 Pseudo-code, but if this doesn't make sense, or you have any questions, let me know. if( condition1 && condition2 ) { // do something } elseif( condition1_only ) { // do something different } else { if( condition2_only ) { // do the third thing } } Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160437 Share on other sites More sharing options...
Muddy_Funster Posted January 16, 2011 Share Posted January 16, 2011 ... check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY Date ASCORDER BY Cost ASC' at line 1 ... Your issue here is the multiple ORDER BY statements. It should read "ORDER BY date ASC, Cost ASC". Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160448 Share on other sites More sharing options...
smproph Posted January 17, 2011 Author Share Posted January 17, 2011 The If, Else If statement worked great. Now the only problem I see left is the double single quote problem. It keeps adding " ' " on both sides of my variables I am searching for. Would I need to escape the single quotes some how? Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160458 Share on other sites More sharing options...
smproph Posted January 17, 2011 Author Share Posted January 17, 2011 Okay, I think I am almost there. But for some reason I am losing my $qry data. Here is the code and error. Look after Initials and you see '' instead of actual initials. Query string: "SELECT * FROM timeslip WHERE 1 and Initials=" ORDER BY "Date" ASC, "Cost" ASC Failed with error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT * FROM timeslip WHERE 1 and Initials=" ORDER BY "Date" ASC, "Cost" ASC' at line 1 if($_POST['Initials']) { $Initials=$_POST['Initials']; $qry_str.='and Initials="'.$Initials . '"'; } if($_POST['Identifier']) { $Identifier=$_POST['Identifier']; $qry_str.='and Identifier LIKE "%'.$Identifier . '%"'; } if($_POST['Type']) { $Type=$_POST['Type']; $qry_str.='and Type LIKE "%'.$Type . '%"'; } if($_POST['Terms']) { $Terms=$_POST['Terms']; $qry_str.='and Terms LIKE "%'.$Terms . '%"'; } if($_POST['Memo']) { $Memo=$_POST['Memo']; $qry_str.='and Memo LIKE "%'.$Memo . '%"'; } if($_POST['date1']) { $date1=$_POST['date1']; $date2=$_POST['date2']; $start=date('Y-m-d', strtotime($date1)); $end=date('Y-m-d', strtotime($date2)); $qry_str.="and Date >= $start and Date <= $end "; } if($_POST['order1'] && $_POST['order2']) { $order1=$_POST['order1']; $order2=$_POST['order2']; $qry=$_POST['qry']; $qry_str=' "'.$qry.'" ORDER BY "'.$order1 . '" ASC, "'.$order2 . '" ASC '; Quote Link to comment https://forums.phpfreaks.com/topic/224641-order-by-not-working/#findComment-1160484 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.