Order By Not Working

[smproph]

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>

 

[zenlord]

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.

[smproph]

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

[Muddy_Funster]

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?

[Pikachu2000]

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){

[smproph]

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.

[Pikachu2000]

Looks like it's getting doubly single-quoted somehow, thereby breaking the syntax.

[smproph]

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 ";
}

 

[Pikachu2000]

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.

[smproph]

How would I logically write it to where if only 1st value is submitted then do that, if both are submitted then do that

[Pikachu2000]

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
     }
}

[Muddy_Funster]

...

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".

[smproph]

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?

[smproph]

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 ';