wwfc_barmy_army Posted September 30, 2006 Share Posted September 30, 2006 Hello,I have this code:[quote]<?php$result = mysql_query('SELECT name, sitelink, sitedownload, dateadded, publisher FROM site ORDER BY name DESC');print "<table border=1 class=list>";echo("<thead><tr><td>Site Name</td><td>Site Link</td><td>Site Download</td><td>Date Added</td><td>Publisher</td></tr></thead><tbody>");while ($qry = mysql_fetch_array($result)){print "<tr>";print "<td>$qry[name]</td>";print "<td><a href=$qry[sitelink]>$qry[sitelink]</a></td>";print "<td><a href=$qry[sitedownload]>$qry[sitedownload]</a></td>";print "<td>$qry[dateadded]</td>";print "<td>$qry[publisher]</td>";print "</tr>";}print "</tbody></table>";?>[/quote]It is currently listed by 'name' although, i am trying to find a way that i can make it so that when i click on one the of table headers it sorts that column. The headers are here:[quote]<thead><tr><td>Site Name</td><td>Site Link</td><td>Site Download</td><td>Date Added</td><td>Publisher</td></tr></thead><tbody>[/quote]Any advice is appreciated.Thanks.Peter. Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/ Share on other sites More sharing options...
.josh Posted September 30, 2006 Share Posted September 30, 2006 In headers listing:[code]<a href="myscript.php?sortby=name">name</a><a href="myscript.php?sortby=blah">blah</a>etc..[/code]then in your script, before the sql query, do something like this:[code]$allowed = array('name','blah');$column = (in_array($_GET['sortby'], $allowed)) ? $_GET['sortby'] : 'Name';$result = mysql_query('SELECT name, sitelink, sitedownload, dateadded, publisher FROM site ORDER BY $column DESC');[/code] Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101488 Share on other sites More sharing options...
wwfc_barmy_army Posted September 30, 2006 Author Share Posted September 30, 2006 Thanks for your reply. Although i am getting this error:[quote]Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\public_html\RPG\list.php on line 15Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\public_html\RPG\list.php on line 19[/quote]My code changed from when i posted, here is the new stuff:[code]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><?php include("includes/dbconnect.php"); ?><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /><title>View Sites</title></head><body><?php$allowed = array('name','Blah');$column = (in_array($_GET['sortby'], $allowed)) ? $_GET['sortby'] : 'name';$result = mysql_query('SELECT * FROM site ORDER BY $column DESC');//$result = mysql_query('SELECT * FROM site ORDER BY name DESC');$qry = mysql_fetch_array($result);print "<table border=1 class=list>";echo("<thead><tr><td><a href=list.php?sortby=name>Site Name</a></td><td>Editor Rating</td><td>Visitor Rating</td><td>Date Added</td><td>Publisher</td></tr></thead><tbody>");while ($qry = mysql_fetch_array($result)){print "<tr>";print "<td><a href=site.php?id=$qry[id]>$qry[name]</td>";switch ($qry['editorrating']) { case 0: $image = "0.png"; break; case 1: $image = "1.png"; break; case 2: $image = "2.png"; break; case 3: $image = "3.png"; break; case 4: $image = "4.png"; break; case 5: $image = "5.png"; break; case 12: $image = "05.png"; break; case 15: $image = "15.png"; break; case 25: $image = "25.png"; break; case 35: $image = "35.png"; break; case 45: $image = "45.png"; break;}echo "<td><img src=\"images/{$image}\" /></td>";print "<td></td>";// edited lineprint "<td>$qry[dateadded]</td>";print "<td>$qry[publisher]</td>";print "</tr>";}print "</tbody></table>";?><p> </p><p> </p></body></html>[/code]Any advice?Thanks.Peter. Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101499 Share on other sites More sharing options...
printf Posted September 30, 2006 Share Posted September 30, 2006 Your using single quotes so you need to enclose your $column value or dot it -> ' . $column . 'Instead of this...[code]$result = mysql_query('SELECT * FROM site ORDER BY $column DESC');[/code]do this...[code]$result = mysql_query('SELECT * FROM site ORDER BY {$column} DESC');[/code]Single quotes do not [b]interpolate[/b] Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101506 Share on other sites More sharing options...
wwfc_barmy_army Posted September 30, 2006 Author Share Posted September 30, 2006 I've tried that but it still says the same.Note: The lines that the error is coming from are:$qry = mysql_fetch_array($result);andwhile ($qry = mysql_fetch_array($result))Thanks.Peter. Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101510 Share on other sites More sharing options...
printf Posted September 30, 2006 Share Posted September 30, 2006 try this...[code=php:0]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"> <head><?php include ( 'includes/dbconnect.php' ); ?> <meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1' /> <title>View Sites</title> </head> <body><?php$allowed = array ( 'name', 'Blah' );$column = ( in_array ( $_GET['sortby'], $allowed ) ? $_GET['sortby'] : 'name' );$result = mysql_query ( 'SELECT * FROM site ORDER BY ' . $column . ' DESC' ) or die ( 'Query Error: ' . mysql_error () );if ( mysql_num_rows ( $result ) > 0 ){ echo " <table border=1 class=list>"; echo " <thead> <tr> <td><a href=list.php?sortby=name>Site Name</a></td> <td>Editor Rating</td> <td>Visitor Rating</td> <td>Date Added</td> <td>Publisher</td> </tr> </thead> <tbody>"; while ( $qry = mysql_fetch_assoc ( $result ) ) { echo " <tr>"; echo " <td><a href=site.php?id=" . $qry['id'] . ">" . $qry['name'] . "</td>"; echo " <td><img src='images/" . $qry['editorrating'] . ".png' /></td>"; echo " <td></td>"; echo " <td>" . $qry['dateadded'] . "</td>"; echo " <td>" . $qry['publisher'] . "</td>"; echo " </tr>"; } echo " </tbody> </table>";}else{ echo ' <div>no results found for column: ' . $column . '</div>';}?> <p> </p> <p> </p> </body></html>[/code]me! Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101523 Share on other sites More sharing options...
wwfc_barmy_army Posted September 30, 2006 Author Share Posted September 30, 2006 Thanks. Got it now :)Thanks.Peter. Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101528 Share on other sites More sharing options...
.josh Posted October 1, 2006 Share Posted October 1, 2006 now that you got it working, i thought i would mention that that's a quick and dirty way of doing it. i included the in_array statement as a security measure against certain hacking techniques, but there are additional things you can do for added security. For instance, instead of passing your actual field names to be used in your query where the whole world can see what your database fieldnames are, you might want to instead do like sortid=1 sortid=2, etc... and then in your code, assign $column to the column name, based on the sortid. like for instance this:[code]<a href="myscript.php?sortid=0">name</a><a href="myscript.php?sortid=1">blah</a>etc..[/code]and then in your query code you can do this:[code]if (isset($_GET['sortid'])) { switch ($_GET['sortid']) { case 0 : $column = 'name'; break; case 1 : $column = 'blah'; break; default : $column = 'name'; }}$result = mysql_query("SELECT name, sitelink, sitedownload, dateadded, publisher FROM site ORDER BY $column DESC");[/code] Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101698 Share on other sites More sharing options...
wwfc_barmy_army Posted October 1, 2006 Author Share Posted October 1, 2006 Thanks for the great reply! I have managed to kinda implementate this now, although i've got one problem, if i enter the page normally i get this error:Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\public_html\RPG\list.php on line 28(Line 28 being the one in bold below - Part of the code below)(i have commented out the other lines until i get it working properly:)):[code]if (isset($_GET['sortid'])) { switch ($_GET['sortid']) { case 0 : $column = 'name'; break; case 1 : $column = 'editorrating'; break; default : $column = 'name'; }}$result = mysql_query("SELECT * FROM site ORDER BY $column DESC");//$allowed = array ( 'name', 'editorrating', 'dateadded', 'publisher' );//$column = ( in_array ( $_GET['sortby'], $allowed ) ? $_GET['sortby'] : 'name, editorrating, dateadded, publisher');//$result = mysql_query ( 'SELECT * FROM site ORDER BY ' . $column . ' DESC' ) or die ( 'Query Error: ' . mysql_error () );//$result = mysql_query('SELECT * FROM site ORDER BY name DESC');[b]while ($qry = mysql_fetch_array($result))[/b]{[/code]So i get to the page but included '?sortid=0' and it worked fine, it looks like for some reason it gets confused about it and it looks like the actual word $column is getting passed.Any ideas on how i can get around this?Thanks.Peter. Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101721 Share on other sites More sharing options...
.josh Posted October 1, 2006 Share Posted October 1, 2006 well if nothin' else you can do this:$result = mysql_query("SELECT * FROM site ORDER BY " . $column . " DESC");though i'm not sure why it doesn't work as is. your code shows double quotes but the only reason i can really think of as to why it's literally using '$column' instead of the $column's value, is if you are in fact using single quotes. check to make sure that you are using double quotes:$result = mysql_query([color=green][b][size=15pt]"[/size][/b][/color]SELECT * FROM site ORDER BY $column DESC[color=green][b][size=15pt]"[/size][/b][/color]);not single quotes:$result = mysql_query([color=red][b][size=15pt]'[/size][/b][/color]SELECT * FROM site ORDER BY $column DESC[color=red][b][size=15pt]'[/size][/b][/color]);as mentioned by printf, when you use single quotes, variables used inside the single quotes will be parsed literally as a string. If you want it to be parsed to where the value of the variable is used instead of the variable name itself, then you need to use double quotes. If you are still stuck on using single quotes, then you need to close it and make a concactonated (closing the quotes, using dots, open new quotes) version of it like so:$result = mysql_query('SELECT * FROM site ORDER BY ' . $column . ' DESC'); Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101845 Share on other sites More sharing options...
wwfc_barmy_army Posted October 1, 2006 Author Share Posted October 1, 2006 The value is being passed although only after you click on one of the headers it then works. It just looks like it doesn't have a value for if you go to the page without any sortid in the URL.I can see there is this:default : $column = 'name';But this doesn't seem to work. Any ideas?Thanks.Peter. Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101942 Share on other sites More sharing options...
ponsho Posted October 1, 2006 Share Posted October 1, 2006 [code]if (isset($_GET['sortid'])) { switch ($_GET['sortid']) { case 0 : $column = 'name'; break; case 1 : $column = 'editorrating'; break; default : $column = 'name'; }}[/code]This is what you have on your code so if no sortid is set the condition is false, so you just have to remove that condition like this.[code]switch ($_GET['sortid']) { case 0 : $column = 'name'; break; case 1 : $column = 'editorrating'; break; default : $column = 'name';}[/code] Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101947 Share on other sites More sharing options...
wwfc_barmy_army Posted October 1, 2006 Author Share Posted October 1, 2006 Ahh. Thanks mate! :)Peter. Quote Link to comment https://forums.phpfreaks.com/topic/22607-solvedsort-table-column/#findComment-101948 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.