ORDER BY Question

[run2web]

I want to allow the user to show the results of a search and choose how they want the results to be ordered by.

I am assuming that this will need to be done using either a Switch or IF statement but I'm not really sure how to do it. Any suggestions? I would provide radio buttons for them to choose from or it could also be done using a dropdown box.

Possible Orders would be

OwnerID, LastName, FirstName, City, State, Zip, hidden.

Any help appreciated.

[planetsim]

well a simple sort function with those in the sort....

then just have a page sort which will have

which willl be

<?

header("LOCATION: file_name.php?order=$order");?>

variable coming from it order=blah

then in the mysql_query("SELECT * FROM tbl ORDER BY $order DESC"):
or you can even specify if they want the sort descending or ascending.

[run2web]

Originally posted by planetsim
well a simple sort function with those in the sort....

then just have a page sort which will have

which willl be

<?

header("LOCATION: file_name.php?order=$order");?>

variable coming from it order=blah

then in the mysql_query("SELECT * FROM tbl ORDER BY $order DESC"):
or you can even specify if they want the sort descending or ascending.

I already have a header statement. Won't this

<?

header("LOCATION: file_name.php?order=$order");?>

conflict with my other header statement? Can the header statement have two statements?

[planetsim]

Post all your code here... I tell you make a file called sort.php which will have a header in it...

then use header(""); to get to the same page but with the vars

[run2web]

Ok, it doesn't like my query statement. Here is what I have:

	 /********************* Begin Query for Owners Form ***************************/

$SubmitOwners= $_POST['SubmitOwners']; 
$AlterSearch= $_POST['AlterSearch'];	// search again with same values initially in form
$NewSearch= $_POST['NewSearch'];		// clear form

if($NewSearch)
{
	$ownerid = "";
	$last = "";
	$first = "";
	$city = "";
	$state = "";
	$zip = "";
	$hidden = "";
}
else // all empty
{
	$ownerid = $HTTP_POST_VARS['OwnerID']; 
	$last = $HTTP_POST_VARS['LastName']; 
	$first = $HTTP_POST_VARS['FirstName']; 
	$city = $HTTP_POST_VARS['City']; 
	$state = $HTTP_POST_VARS['State']; 
	$zip = $HTTP_POST_VARS['Zip']; 
	$hidden = $HTTP_POST_VARS['hidden']; 
} 

...blah...

$form_filled= $ownerid . $last . $first . $city . $state . $zip . $hidden;

//echo "[$form_filled]<br>";
if(!$SubmitOwners or !$form_filled) // to allow Show All remove the or ...
{
    // Show the search form

...blah...

<form name="Owners" id="Owners" method="post" enctype="application/x-www-form-urlencoded" action="<?php echo $PHP_SELF; ?>">
		  <?
			include 'report_admin_table_form.php';
		  ?>

...blah...

}
else
{    // show any results

$query_owners = "SELECT * FROM $own ORDER BY $order DESC WHERE 1 "; 
// if the OwnerID has a value 
if ($_POST['OwnerID']){ 
$query_owners = $query_owners." and OwnerID LIKE '$ownerid%'"; 
} 
// if the LastName has a value 
if ($_POST['LastName']){ 
$query_owners = $query_owners." and LastName LIKE '$last%'"; 
} 
// if the first name has a value 
if ($_POST['FirstName']){ 
$query_owners = $query_owners." and FirstName LIKE '$first%'"; 
} 	
// if the City has a value 
if ($_POST['City']){ 
$query_owners = $query_owners." and City LIKE '$city%'"; 
} 
//if State has a value 
if ($_POST['State']){ 
$query_owners = $query_owners. " and State  = '$state'"; 
} 
// if the Zip has a value 
if ($_POST['Zip']){ 
$query_owners = $query_owners." and Zip LIKE '$zip%'"; 
} 
// if the hidden has value 
if ($_POST['hidden']){ 
$query_owners = $query_owners." and hidden LIKE '$hidden%'"; 
} 

//run the query 
$result_owner = mysql_query($query_owners);  

$results= mysql_numrows($result_owner); //LINE 299
$item= 0;

And this is my function, which is located in sort.php:

<?php header("location: report_admin_html_nu.php?order=$order"); ?> 
<?php 
function Order ($order) {
$order = "OwnerID";
$order = "LastName";
$order = "FirstName";
$order = "City";
$order = "State";
$order = "Zip";
$order = "hidden";
	return $order;
}
?>

When I run the query I get this error:

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /home/hagerstown1/www/HagerstownCemetery/report_admin_html_nu.php on line 299

All of my variables are passed from the include files.

😕

[gtibok]

<form ...>
Search for <input type=text name=search><br>
Order results by
<select name="order_by">
<option value="owner"> owner</option>
<option value="LastName"> LastName</option>
<option value="FirstName"> FirstName</option>
<option value="City"> City</option>
<option value="State"> State</option>
</select>
.....
</form>

---

the sql query:

$query = "SELECT * FROM ORDER BY '$order_by' ";

[Weedpacket]

planetsim; what's the reason for the header()?

What I often do if I've got a table, which can be sorted on various columns, to to make the column headers links to the same page, with the "?orderby=whatever" querystrings attached.

When I display the table, I look to see if $_GET['orderby'] is set, and set to a valid column name. If it is, then I add the ORDER BY clause to the SQL query.

[planetsim]

sorry that i misled you.. i didnt read the question right at all.. me sound so dumb now🙁

[run2web]

Planetsim: No Problem, it was an idea that I hadn't tried. I appreciate the input.

gtibok and weedpacket: I am not able to add an ORDER BY to my SELECT statement because of the WHERE 1

$query_owners = "SELECT * FROM $own WHERE 1 "; 

Anytime that I try to add ORDER BY to this statement I get this error:

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /home/hagerstown1/www/HagerstownCemetery/report_admin_html_nu.php on line 299

which is referring to this line:

$result_owner = mysql_query($query_owners);  

$results= mysql_numrows($result_owner); //LINE 299
$item= 0;

😕 What can I do differently? :mad:

[herve]

Hi,

I think you should rewrite your SQL query
in such a way to avoid this "WHERE 1" thing

just add a "WHERE " (alone)

add the correct condition

if ($_POST['OwnerID']){
$query_owners = $query_owners." (OwnerID LIKE '$ownerid%') AND";
}

(adding ( and ) and putting the AND at the end)

At the end, delete the last "AND" with substr()

and finally add the "ORDER BY" you need

That should be OK

regards,
Hervé.

[run2web]

But wouldn't that mess up the numrows statement?

$results= mysql_numrows($result_owner);

[Weedpacket]

What is the "WHERE 1" clause for?

Oh, I see. Well "1=1" might be better than "1", and either way I don't think it would have any bearing on num_rows (num_rows wouldn't be very useful if it couldn't count the number of rows in a query that had been ordered ... that would just make no sense: why should num_rows care?). But the smart thing to do would be to only have a WHERE clause if there are genuine WHERE conditions that need satisfying.

That is to say, instead of using

 if ($_POST['FirstName']){ 
    $query_owners = $query_owners." and FirstName LIKE '$first%'"; 
    } 

start with
$where_clauses=array();
and have

 if ($_POST['FirstName']){ 
    $where_clauses[] = "FirstName LIKE '$first%'"; 
    } 

(Note the lack of an AND)
And then check

if(count($where_clauses)!=0)
{ $query_owners .= " WHERE ".join(' AND ', $where_clauses);
}

(A bonus to this is that you can offer the choice to join clauses with OR instead, if that takes your fancy).

Then of course attach any " ORDER BY whatever" clause.

[run2web]

Weedpacket:

That gives me the num_rows error again. 😕

Here is my code that I entered:

	<?
}
else
{    // show any results

$query_owners = "SELECT * FROM $own WHERE ";   
$where_clauses=array(); 
// if the OwnerID has a value 

if ($_POST['OwnerID']){ 
$where_clauses[] = "OwnerID LIKE '$ownerid%'"; 
}
// if the LastName has a value 

if ($_POST['LastName']){ 
$where_clauses[] = "LastName LIKE '$last%'"; 
}
// if the first name has a value 

if ($_POST['FirstName']){ 
$where_clauses[] = "FirstName LIKE '$first%'"; 
}
// if the City has a value 

if ($_POST['City']){ 
$where_clauses[] = "City LIKE '$city%'"; 
}
//if State has a value 
	if ($_POST['State']){ 
$where_clauses[] = "State LIKE '$state%'"; 
}
// if the Zip has a value 

if ($_POST['Zip']){ 
$where_clauses[] = "Zip LIKE '$zip%'"; 
}
// if the hidden has value 

if ($_POST['hidden']){ 
$where_clauses[] = "hidden LIKE '$hidden%'"; 
}

//run the query 
$result_owner = mysql_query($query_owners);  
if(count($where_clauses)!=0)
{ $query_owners .= " WHERE ".join(' AND ', $where_clauses);
}
$results= mysql_numrows($result_owner);
$item= 0;

I have put the last statement

if(count($where_clauses)!=0)
	{ $query_owners .= " WHERE ".join(' AND ', $where_clauses);

both above and below the

$result_owner = mysql_query($query_owners);  

and it still says: Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource
:mad:

[run2web]

Originally posted by Weedpacket
What is the "WHERE 1" clause for?

Oh, I see. Well "1=1" might be better than "1", and either way I don't think it would have any bearing on num_rows (num_rows wouldn't be very useful if it couldn't count the number of rows in a query that had been ordered ... that would just make no sense: why should num_rows care?). But the smart thing to do would be to only have a WHERE clause if there are genuine WHERE conditions that need satisfying.

So would I then remove the "WHERE" from the query statement?

$query_owners = "SELECT * FROM $own ";   

    $where_clauses=array(); 

since the WHERE is in the last statement

Originally posted by Weedpacket

if(count($where_clauses)!=0)
{ $query_owners .= " WHERE ".join(' AND ', $where_clauses);
}

[Weedpacket]

Yes, it means you'll only be adding a WHERE statement if you actually have WHERE clauses to add.

[Mce]

on the line directly following the mysql_query() put a line:

if(!$result_owner) echo "Error: ".mysql_error()."<br>\nSTMT: $query_owners<br>\n";

Then post the output...

-Mce

[run2web]

Originally posted by Mce
Then post the output...

Error: You have an error in your SQL syntax near ') WHERE hidden LIKE 'N%'' at line 1
STMT: SELECT * FROM Owners ORDER BY 'Order(') WHERE hidden LIKE 'N%'

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/hagerstown1/www/HagerstownCemetery/report_admin_html_nu.php on line 353

[run2web]

Moving forward but still problems

Weedpacket: Your code works but it still will not let me put ORDER BY in the code. I probably have something out of place or not calling something right. The way that it is currently posting the page, it is not sorting at all.

Here is what I have for the different parts. I hope this doesn't get too confusing.

Main Page:

 <?
}
else
{    // show any results

$query_owners = "SELECT * FROM $own  ";   //Order By '$order'
    $where_clauses=array(); 
    // if the OwnerID has a value 

if ($_POST['OwnerID']){ 
$where_clauses[] = "OwnerID LIKE '$ownerid%'"; 
}
// if the LastName has a value 

if ($_POST['LastName']){ 
$where_clauses[] = "LastName LIKE '$last%'"; 
}
// if the first name has a value 

if ($_POST['FirstName']){ 
$where_clauses[] = "FirstName LIKE '$first%'"; 
}
// if the City has a value 

if ($_POST['City']){ 
$where_clauses[] = "City LIKE '$city%'"; 
}
//if State has a value 
    if ($_POST['State']){ 
$where_clauses[] = "State LIKE '$state%'"; 
}
// if the Zip has a value 

if ($_POST['Zip']){ 
$where_clauses[] = "Zip LIKE '$zip%'"; 
}
// if the hidden has value 

if ($_POST['hidden']){ 
$where_clauses[] = "hidden LIKE '$hidden%'"; 
}

//run the query 
$result_owner = mysql_query($query_owners);  
if(count($where_clauses)!=0)
{ $query_owners .= " WHERE ".join(' AND ', $where_clauses);
}

//run the query 
$result_owner = mysql_query($query_owners);  
if(!$result_owner) echo "Error: ".mysql_error()."<br>\nSTMT: $query_owners<br>\n"; 

$results= mysql_num_rows($result_owner);
$item= 0;
?>
<form name="SearchMore" id="SearchMore" method="post" enctype="application/x-www-form-urlencoded" action="<?php echo $PHP_SELF; ?>">
<table width="1300" border="1" align="center" cellpadding="3" cellspacing="1" bordercolor="#666666" summary="Owner Report">
<tr bordercolor="#000000" class="admintable"> 
  <td colspan="10" bgcolor="#C3C3C3" class="black"> <p><font size="5"><strong>Report 
      for Owners</strong></font></p></td>
</tr>
<tr bgcolor="#CCCCCC" class="admintr"> 
  <td width="21" align="center" bgcolor="#CCCCCC" class="black"><strong>ID</strong></td>
  <td width="131" bgcolor="#CCCCCC" class="black"><strong>Last Name</strong></td>
  <td width="131" bgcolor="#CCCCCC" class="black"><strong>First Name</strong></td>
  <td width="189" bgcolor="#CCCCCC" class="black"><strong>Street Address</strong></td>
  <td width="131" bgcolor="#CCCCCC" class="black"><strong>City</strong></td>
  <td width="50" align="center" bgcolor="#CCCCCC" class="black"><strong>State</strong></td>
  <td width="65" align="center" bgcolor="#CCCCCC" class="black"><strong>Zip 
    Code</strong></td>
  <td width="165" align="center" bgcolor="#CCCCCC" class="black"><strong>E-Mail</strong></td>
  <td width="333" align="center" bgcolor="#CCCCCC" class="black"><strong>Notes</strong></td>
</tr>
  </table>
	<?

if($results > 0)    
{
  WHILE ($row=mysql_fetch_array($result_owner)) :  
 	 $item++;        // add one to the item count
	// $row is the result that was gotten from the $query_owner
	// Print the relevant information 
    $ownerid = $row['OwnerID'];
	$last = $row['LastName'];
    $first = $row['FirstName'];
	$street = $row['StreetAddress'];
    $city = $row['City'];
    $state = $row['State'];
    $zip = $row['Zip'];
	$email = $row['Email'];
	$notes = $row['Notes'];
    $hidden = $row['hidden'];    

// display the result with the same code used to make the form
		include 'report_admin_table_show.php';    

      ENDWHILE;
		 }
    else    // no results
    {
		?>

Form Include File:

<!-- Choose Query Type -->
  <?php include('sort.php'); ?>
  <tr bgcolor="#edecfe"> 
    <td width="190" class="black"><strong>Order search results by:</strong></td>
    <td width="381" class="blue"><select name="OrderBy" class="input">
        <?php echo Order($order); 
	//use if using sort.php -- add to name 
	?>
        <option value="OwnerID">Owner ID</option>
        <option value="LastName">Last Name</option>
        <option value="FirstName">First Name</option>
        <option value="City">City</option>
        <option value="State">State</option>
        <option value="Zip">Zip Code</option>
        <option value="hidden">Hidden</option>
      </select></td>
  </tr>
</table>

Sort.php

<?php function Order ($order) {
$order = "OwnerID";
$order = "LastName";
$order = "FirstName";
$order = "City";
$order = "State";
$order = "Zip";
$order = "hidden";
	return $order;
}
?>

I think I have it all here.

P.S. I was just messing with the search and when I click the Alter Search button it is also populating the search form with the last entry in the report, even though the original search was for all entries. 😕 Probably a separate issue.

[Mce]

Originally posted by run2web


Error: You have an error in your SQL syntax near ') WHERE hidden LIKE 'N%'' at line 1
STMT: SELECT * FROM Owners ORDER BY 'Order(') WHERE hidden LIKE 'N%'

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/hagerstown1/www/HagerstownCemetery/report_admin_html_nu.php on line 353

Well the problem here seems to be that ORDER BY 'Order(') I didn't see where that's comming from in your snippet, but that would definately be the cause for the ORDER BY not working.
The ORDER BY needs to be given a field name and Iif you have a field named order you're going to have to change it.

[run2web]

Originally posted by Mce


Well the problem here seems to be that ORDER BY 'Order(') I didn't see where that's comming from in your snippet, but that would definately be the cause for the ORDER BY not working.
The ORDER BY needs to be given a field name and Iif you have a field named order you're going to have to change it.

I don't have a field named Order.

I am using a function and calling it. The problem is whenever I add ORDER BY to the SELECT statement I get the error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/hagerstown1/www/HagerstownCemetery/Copy_of_report_admin_html_nu.php on line 316

Line 316 is

$results= mysql_num_rows($result_owner);

I am calling the function like this:

$query_owners = "SELECT * FROM $own ORDER BY Order('$order')";

However it doesn't seem to matter what I do. As soon as I try to do the ORDER BY I get the error above. I KNOW that this has to work but why it isn't I have no idea! :mad: 😕