Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting my table of results. 1

Status
Not open for further replies.
d0nny

d0nny

IS-IT--Management
Dec 18, 2005
278
GB

I have a table listed on my page which is a result of a mySQL script. In the script I have ordered the results by Surname.
What I would now like to do is add links to my table header whereby the user can sort the table using other columns.

Can I do this using PHP?
 
Sort by date Sort by votes
Yes, you can make each table header a button, or link and upon submission check the value and then modify the "order by" clause of your sql statement accordingly.




----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
Thanks vacunita, but how do I do that?
 
Upvote 0 Downvote
Well we would need to see a bit of your code to be very specific, but for instance, supposing you want to use links for your headers:

Code: 
<table>
<td><a href="pagewithresults.php?sort=name">Name</a></td><td><a href="pagewithresults.php?sort=someotherfield">Some Other Filed</a></td>...

Then in your scrip look for the sort value in the $_GET superglobal:

Code: 
$sortit = isset($_GET['sort']) ? trim($_GET['sort']) : '';
switch($sortit)
switch ($action){
    case 'name':
$myquery="SELECT *FROM ..... ORDER BY NAME";
   break;
  case 'someotherfield':
$myquery="SELECT *FROM ..... ORDER BY someotherfield";
   break;
  default:
[green]\\Sort by a default field if no field is specified.[/green]
$myquery="SELECT *FROM ..... ORDER BY defaultfield";
}
(Thanks go out to Jpadie as I used his example for this)

Then just feed the query to your mysql_query call.

The Results will return ordered by whatever you choose, so your display script can then display them in that order.


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
Thanks again. I'll give it a try.

My results/display script is simply as you have it in the table, so I just need to add links as you have specified.

So, if my table has say 10 columns/fields, I would need 10 case statements, plus a default?
 
Upvote 0 Downvote
This is the code for the full page I'm using:

Code: 
mysql_connect("localhost", "db_user", "db_passwd") or die(mysql_error());
mysql_select_db("db_name") or die(mysql_error());

$result = mysql_query("SELECT * FROM db_table ORDER BY sname ASC");
		
	echo '<table align="center" border="0"><tr><td align="center"><img src="some_img.jpg" width="600" height="105" border="0" alt="#"></td></tr></table>';
	
	if ($result) {
		echo '<table align="center" border="1" style="font-family:verdana; font-size: 11px;">';
		echo '<tr><td align="center" colspan="10">Entries sorted by Surname</td></tr>';
		echo '<tr><td>Date</td><td>First Name</td><td>Surname</td><td>Email</td><td>Tele</td><td>City</td><td>Postcode</td><td>Job Title</td><td>Interested in?</td><td>CV</td></tr>';
		
		while($row = mysql_fetch_assoc($result)){
			echo "<tr><td>".$row['date']."</td><td>".$row['fname']."</td><td><a href='edit.php?id=$row[id]'>".$row['sname']."</a></td><td width='225'>".$row['email']."</td><td>".$row['tele']."</td><td>".$row['city']."</td><td>".$row['postcode']."</td><td>".$row['jobtitle']."</td><td>".$row['department']."</td><td><a href=".$row['url'].">CV File</a></td></tr>";
		}
		
		echo '</table>';
		mysql_close();
	} else {
		echo 'There was an error!';
	}
?>

Any ideas how I could integrate this?
I tried you method and broke my script. :-(
 
Upvote 0 Downvote
Code: 
mysql_connect("localhost", "db_user", "db_passwd") or die(mysql_error());
mysql_select_db("db_name") or die(mysql_error());

[green]\\********************[/green]
[blue]
$sortit = isset($_GET['sort']) ? trim($_GET['sort']) : '';
switch($sortit){
    case 'name':
$myquery="SELECT *FROM ..... ORDER BY date ASC";
   break;
  case 'fname':
$myquery="SELECT *FROM ..... ORDER BY fname ASC";
   break;
  case 'sname':
$myquery="SELECT *FROM ..... ORDER BY sname ASC";
   break;
etc...
  default:
\\Sort by a default field if no field is specified.
$myquery="SELECT *FROM ..... ORDER BY fname ASC";
}

[/blue]
[green]\\********************[/green]

$result = mysql_query($myquery);
        
    echo '<table align="center" border="0"><tr><td align="center"><img src="some_img.jpg" width="600" height="105" border="0" alt="#"></td></tr></table>';
    
    if ($result) {
        echo '<table align="center" border="1" style="font-family:verdana; font-size: 11px;">';
        echo '<tr><td align="center" colspan="10">Entries sorted by Surname</td></tr>';
        echo '<tr><td>[red]<a href="page.php?sort=date">[/red]Date[red]</a>[/red]</td><td>[red]<a href="page.php?sort=fname">[/red]First Name[red]</a>[/red]</td><td>Surname</td><td>Email</td><td>Tele</td><td>City</td><td>Postcode</td><td>Job Title</td><td>Interested in?</td><td>CV</td></tr>';
        
        while($row = mysql_fetch_assoc($result)){
            echo "<tr><td>".$row['date']."</td><td>".$row['fname']."</td><td><a href='edit.php?id=$row[id]'>".$row['sname']."</a></td><td width='225'>".$row['email']."</td><td>".$row['tele']."</td><td>".$row['city']."</td><td>".$row['postcode']."</td><td>".$row['jobtitle']."</td><td>".$row['department']."</td><td><a href=".$row['url'].">CV File</a></td></tr>";
        }
        
        echo '</table>';
        mysql_close();
    } else {
        echo 'There was an error!';
    }
?>

Yes you would need 10 cases. one for each column.

Alternatively you could generate it on the fly, but I think that's going to be a bit more complex than what you are ready for.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
Thanks again for your detailed response, but I can't get this to work. My page diesn't even get displayed.

I noticed the two examples you have written (one in a previous post) are slightly different.
In the first example, you have
Code: 
switch($sortit)
switch ($action){

And in the second example, you have
Code: 
switch($sortit){

Is this correct?
Or what else could be stopping this working?
 
Upvote 0 Downvote
My first example had an error, remove the

switch ($action)

Remember I'm typing all this straight into the reply box here, so theres a good possibility I might have some errors. It's not meant to be taken as is, its to exemplify what you can do.


I checked the code, had a few typos, that I fixed. This should work now.
Code: 
$sortit = isset($_GET['sort']) ? trim($_GET['sort']) : '';
switch($sortit){
    case 'name':
$myquery="SELECT *FROM ..... ORDER BY date ASC";
   break;
  case 'fname':
$myquery="SELECT *FROM ..... ORDER BY fname ASC";
   break;
  case 'sname':
$myquery="SELECT *FROM ..... ORDER BY sname ASC";
   break;
  default:
//Sort by a default field if no field is specified.
$myquery="SELECT *FROM ..... ORDER BY fname ASC";
break;
}











----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
vacunita
Many, many thanks for this and I apologise for taking your examples so literally.
I have no implemented this, along with some other suggestions you gave me and it's all working, so many thanks again.

 
Upvote 0 Downvote
Glad I could help.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
I've now got another mod request from our HR department!!
Everything is working as they requested and now they've thrown a spanner in the works! And they think this is easy!

Earlier, I asked about storing checkbox values which I got help on in the mySQL forum. Basically, these checkboxes store values for different departments in the business and users select areas on which they are interested in.
The HR people now want an email to be sent to the department head when the relavent checkbox is selected.

I have already included two email sections in my script which send a confirmation email to the user completing the form and also to the HR people, but I now need to send a conditional email to one or many department heads depending on which checkboxes are selected.
Jees! Any ideas?
 
Upvote 0 Downvote
this seems like a new question, so please start a new thread.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
396
SashaBuilder3
SashaBuilder3
mervgh
  • Locked
  • Question
My own custom error handler
Replies
1
Views
264
Chris Miller
Chris Miller
EdLentz
  • Locked
  • Question
I need a simple example of how to send a table of info .
Replies
8
Views
450
EdLentz
EdLentz
R
  • Question
How Can I Use MySQL To Send Data To An Email
Replies
1
Views
278
Chris Miller
Chris Miller
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
272
spamjim
spamjim

Part and Inventory Search

Sponsor

Back
Top