order by problem
Moderator: General Moderators
-
sirTemplar
- Forum Commoner
- Posts: 65
- Joined: Wed Dec 18, 2002 1:57 am
order by problem
i have a php script that outputs records from a seach form. the output automatically orders the records according to SURNAME since i have ORDER BY surname on my sql query. what i would like to do is to put on the output page the option to change the order of the search result into some other order, rg. by NAME, BIRTHDAY etc. any help how to do that? thanks 
This is a php scripting issue rather than mysql.
You could add a flag to the query string which calls your script, ie another GET var which is used by your script to specify a sort order.
myscript.php?yourvar1=x&yourvar2=y&new_sort_var=value
You could use the actual column name as the value, assuming it's url safe - if not urlencode (and decode later..) or use some kind of mapping: 1 = name, 2=birthday, etc.
If using the col name as a value, adjust your code to:
Finally, you would hyperlink column titles to repeat the search with a new sort order.
You could add a flag to the query string which calls your script, ie another GET var which is used by your script to specify a sort order.
myscript.php?yourvar1=x&yourvar2=y&new_sort_var=value
You could use the actual column name as the value, assuming it's url safe - if not urlencode (and decode later..) or use some kind of mapping: 1 = name, 2=birthday, etc.
If using the col name as a value, adjust your code to:
Code: Select all
<?php
$sort_order = $_GET['new_sort_var'];
$mysql = "SELECT ..etc .. ORDER BY $sort_order";
?>
Last edited by McGruff on Thu Aug 11, 2005 4:22 am, edited 1 time in total.
-
sirTemplar
- Forum Commoner
- Posts: 65
- Joined: Wed Dec 18, 2002 1:57 am
order by
thanks. do you mean doing something like this?
will this be enough? CNOMEN, NOMRL, PROVRELCD, NATDAT1 etc. are the options i want to include for ordering.
how do i make the buttons?
Code: Select all
<?php
$sort_order = $_GET['new_sort_var'];
$query_count = "SELECT * FROM table WHERE
(CNOMEN LIKE '%$CNOMEN%'
AND NOMRL LIKE '%$NOMRL%'
AND PROVRELCD LIKE '%$PROVRELCD%'
AND NATDAT1 LIKE '%$NATDAT1%'
AND PROFTDAT1 LIKE '%$PROFTDAT1%'
AND PROFSDAT1 LIKE '%$PROFSDAT1%'
AND SACDAT1 LIKE '%$SACDAT1%'
AND CETO_NAME LIKE '%$CETO_NAME%'
AND IDNUMF LIKE '%$IDNUMF%'
AND SDATFIN1 = ' '
AND MORTDAT1 = ' ')
ORDER BY $sort_order";
?>how do i make the buttons?
<form method="POST" action="">
<font face="Verdana"><input type="radio" value="V1" checked name="R1"><font size="2">Order
by CNOMEN<br>
</font><input type="radio" name="R1" value="V2"><font size="2">Order by NOMRL<br>
</font><input type="radio" name="R1" value="V3"><font size="2">Ordre by
NATDAT1</font></font></p>
<p><input type="submit" value="Submit" name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
There are two ways to approach this.
You can add another field to the search form which allows the user to choose a sort order at the same time as entering the search terms.
However, you said you wanted to be able to sort in the output (ie search results) page.
Let's say you have a table layout with column headers 'name' 'surname' etc..
Make the column headers form submit buttons. Add hidden fields for all the POST vars which define the search, and one which sets the sort order. Clicking a button would repeat the search with a new sort order.
If you are using sessions, you could, in the search script, build an array of search results and make that a session var. Now, you can create a new script to sort the results array and hyperlink the headers to that.
The former would probably be my first choice. With a big result set, you could have a very large array to chuck around as a session var.
You can add another field to the search form which allows the user to choose a sort order at the same time as entering the search terms.
However, you said you wanted to be able to sort in the output (ie search results) page.
Let's say you have a table layout with column headers 'name' 'surname' etc..
Make the column headers form submit buttons. Add hidden fields for all the POST vars which define the search, and one which sets the sort order. Clicking a button would repeat the search with a new sort order.
If you are using sessions, you could, in the search script, build an array of search results and make that a session var. Now, you can create a new script to sort the results array and hyperlink the headers to that.
The former would probably be my first choice. With a big result set, you could have a very large array to chuck around as a session var.