Sorting columns from MySQL query

[123dan321]

Hey everyone - I'm pretty new to PHP, and have been looking for an answer to this for awhile but haven't found something that answers my question.

 

I have a search form that is processed by a PHP page, which queries a MySQL database looking for matches, and spits out a table of results (pretty standard).  I'm trying to add an option for each column in the results table to sort by ascending/descending.  Right now, I'm trying to do something like the following:

 

For the actual column headers, I have something like this:

 

echo "<td>a href='" . $_SERVER['PHP_SELF'] . "?column_title=name&sort=asc'><img src='content/main/up-arrow.gif'></a><br>;"
echo "<a href='" . $_SERVER['PHP_SELF'] . "?column_title=name&sort=desc'><img src='content/main/down-arrow.gif'></a></td>"

 

Where 'name' is one of the columns, which could be sorted ascending or descending.

 

I then use the following query:

 

$column_title = $_GET['column_title'];
$order = $_GET['sort'];

if(!isset($column_title)) {
$column_title = "name";
}

$query = "SELECT * FROM table_name ORDER BY $column_title $order"

 

etc.

 

However, reloading the form-processing page, as this is doing, sends blank data to the server (since the variables originally defined from the search form no longer have form inputs defining them).  I'm guessing the problem is because the form uses POST as its method?  Basically I'm thinking I need a way to keep these variables intact, so all the data from the form gets re-sent to the server along with the updated ORDER BY statement.  Anyone know a good way to do this?  (Also any suggestions if I'm way off with my reasoning here or how I'm going about this -- I'm just starting out with PHP). Thanks a lot --

[redarrow]

you can add it to the query from the url

 

 

i no it the same as your example but bare i think it becouse you didnt use capital letters for the select clause of

DESC and ASC.

 

example.

<?php
$query = "SELECT * FROM table_name ORDER BY ".$_GET['column_title']." ".$_GET['sort']."";

try capital letters.

<?php
echo "<td>a href='" . $_SERVER['PHP_SELF'] . "?column_title=name&sort=ASC'><img src='content/main/up-arrow.gif'></a><br>;"
echo "<a href='" . $_SERVER['PHP_SELF'] . "?column_title=name&sort=DESC'><img src='content/main/down-arrow.gif'></a></td>"
?>

try it bare or echo the selct out to see the problam.

<?php
$query = "SELECT * FROM table_name ORDER BY ".$_GET['column_title']."  ".$_GET['sort']."";
?>

[redarrow]

are you also using  or die(mysql_error()) << for error problams.

[123dan321]

are you also using  or die(mysql_error()) << for error problams.

Hey, thanks for the quick replies -- I'm using it at times, but basically I have a page that comes up when no form data is sent to the server (separate issue I was having).  This is what loads when I try to sort my columns, I'm guessing because in the following query, for example,

 

$query = "SELECT * FROM table_name WHERE color='$_POST['color']'"

 

(where $_POST['color'] is the color selected on the form), $_POST['color'] is no longer defined, since I'm not resubmitting the form itself, I'm just reloading the PHP processing page with additional information in the ORDER BY statement?

[redarrow]

so have you add the

 

form name set to submit

 

<?php
if(isset($_POST['submit'])){

//code

}
?>

This way when the page loads only if the form is submitted then php is processed.

 

if the select is being populated from a url and there no condition around it then the select will be

used....

 

hope you understand.

[123dan321]

so have you add the

 

form name set to submit

 

<?php
if(isset($_POST['submit'])){

//code

}
?>

This way when the page loads only if the form is submitted then php is processed.

 

if the select is being populated from a url and there no condition around it then the select will be

used....

 

hope you understand.

 

Not sure I do - adding this will execute the code on the processing page only if its form has been submitted, is this right?  If so, I don't see how this would help the issue of losing the original variables from the form (if I'm right about what's happening) -- wouldn't the code just not execute, since the form isn't resubmitted when I reload the page with the new ORDER BY statement?