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 IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Order by $sort desc ? 1

Status
Not open for further replies.
WizyWyg

WizyWyg

Technical User
Jan 31, 2001
854
JP
On the page that display's everything from the database, I have this coding:


<html><title> Accessory and Item Database</title>
<body leftmargin=&quot;0&quot; topmargin=&quot;0&quot; marginwidth=&quot;0&quot; marginheight=&quot;0&quot;>
<?php
$db = mysql_pconnect(&quot;localhost&quot;, &quot;root&quot;);
@mysql_select_db(&quot;acess&quot;,$db);

$q = &quot;SELECT user.username,accessory.* FROM user,accessory WHERE user.userId=accessory.userId ORDER BY item desc&quot;;
$result = mysql_query($q) or die(mysql_error().$q);
$num_rows = mysql_num_rows($result);

if($num_rows > 0) {
?>
<center>
<table border=1 width=700 bordercolor=&quot;#FFFFFF&quot; cellpadding=&quot;4&quot; cellspacing=&quot;0&quot;>
<tr>
<td width=700 bgcolor=&quot;#990000&quot; valign=&quot;top&quot; colspan=&quot;3&quot;><font size=&quot;4&quot; face=&quot;Arial, Helvetica, sans-serif&quot; color=&quot;#FFFFFF&quot;>
Accessory and Item Database</font></td>
</tr>
<tr>
<td width=400 bgcolor=&quot;#990000&quot; valign=&quot;top&quot;><font size=&quot;2&quot; face=&quot;Arial, Helvetica, sans-serif&quot; color=&quot;#FFFFFF&quot;><b><font size=&quot;3&quot;>Item</font></b></font></td>
<td width=150 valign=&quot;top&quot; bgcolor=&quot;#990000&quot;><font face=&quot;Arial, Helvetica, sans-serif&quot; size=&quot;2&quot; color=&quot;#FFFFFF&quot;><b><font size=&quot;3&quot;>Serial
Number</font></b></font></td>
<td width=150 bgcolor=&quot;#990000&quot;><font size=&quot;4&quot; face=&quot;Arial, Helvetica, sans-serif&quot;><b><font color=&quot;#FFFFFF&quot; size=&quot;3&quot;>On
Loan to</font></b></font></td>
</tr>
<?php

while($myrow = mysql_fetch_array($result)) {
echo &quot; <tr>
<td><font face=\&quot;Arial, Helvetica, sans-serif\&quot; size=2>&quot;.$myrow[&quot;item&quot;].&quot;</font></td>
<td><font face=\&quot;Arial, Helvetica, sans-serif\&quot; size=2>&quot;.$myrow[&quot;snumber&quot;].&quot;</font></td>
<td><font face=\&quot;Arial, Helvetica, sans-serif\&quot; size=2>&quot;.$myrow[&quot;username&quot;].&quot;</font></td>
</tr>\n&quot;;
}

?>
</table>
</center>
<?php
}
else {
echo &quot;No records were found!\n&quot;;
}
?>
</body>
</html>





How can I get the headings of the table (as links) so that when they click on &quot;ITEM&quot; it can reorder them by its name or if they Click on the &quot;SERIAL NUMBER&quot; it can re-order the display by SERIAL Number order?


I was given this as an answer (but im getting an SQL syntax error):


changed


$q = &quot;SELECT user.username,accessory.* FROM user,accessory WHERE user.userId=accessory.userId ORDER BY item desc&quot;;

to


$q = &quot;SELECT user.username,accessory.* FROM user,accessory WHERE user.userId=accessory.userId ORDER BY $sort desc&quot;;


and added this as a link (to Arrange by Serial Number)

<a href=&quot;<? echo $PHP_SELF; ?>?sort=snumber&quot;>Serial Number</a>

And the error I get:


You have an error in your SQL syntax near 'desc' at line 1SELECT user.username,accessory.* FROM user,accessory WHERE user.userId=accessory.userId ORDER BY desc
 
Sort by date Sort by votes
Code: 
$sorts = array(&quot;snumber&quot;,&quot;item&quot;);

if((!$sort)||(!in_array($sort,$sorts)) {
     $sort_by = $sorts[1];
}
else {
    $sort_by = $sorts[$sort];
}

this way you are testing to be sure that sort is actually being set.

Also, doublecheck your PHP_SELF to see what it is returning; make sure there is nothing funky going on there.

Chad ICQ: 54380631
online.dll
 
Upvote 0 Downvote
Where in the coding do i post that?
(I inserted after <?php in the table, and I get a parse error)
 
Upvote 0 Downvote
change
if((!$sort)||(!in_array($sort,$sorts)) {

to
if((!$sort)||(!in_array($sort,$sorts))) {

and place right above your query.

Chad. ICQ: 54380631
online.dll
 
Upvote 0 Downvote
Chad, one more on another one;


Similar to the one above, but I have info coming from 4 different tables to be displayed.

Need to be able to sort by title,snumber,system,employee,date,genre

my query:

$q = &quot;SELECT genre.genre, console.system, employee.employee, games.* FROM games LEFT JOIN console ON console.sysId=games.sysId LEFT JOIN employee ON employee.id=games.id LEFT JOIN genre ON genre.genreId=games.genreId ORDER BY title&quot;;


Can apply what you have above the same way? Or does it need to be adjusted a little?

 
Upvote 0 Downvote
Code: 
/*
  create an array with special keys that will be used
  in your links (ie ?sort=$sorts[0] in your code which
  would have the output of ?sort=serial).
*/
$sorts = array(&quot;serial&quot; => array(&quot;Serial Number&quot;,&quot;games.snumber&quot;),
               &quot;title&quot; => array(&quot;Title&quot;,&quot;games.title&quot;),
               &quot;system&quot; => array(&quot;System&quot;,&quot;console.system&quot;),
               &quot;employee&quot; => array(&quot;Employee&quot;,&quot;employee.employee&quot;),
               &quot;date&quot; => array(&quot;Date&quot;,&quot;games.date&quot;),
               &quot;genre&quot; => array(&quot;Genre&quot;,&quot;genre.genre&quot;));

if((!$sort)||(!$sorts[$sort][1])) {
     $sort = &quot;title&quot;;
}

$q = &quot;SELECT genre.genre, console.system, employee.employee, games.* FROM games LEFT JOIN console ON console.sysId=games.sysId LEFT JOIN employee ON employee.id=games.id LEFT JOIN genre ON genre.genreId=games.genreId ORDER BY $sorts[$sort][1]&quot;;

/*
  and and example of how to get the links going:
*/

foreach($sorts as $sortkey => $column) {
     echo &quot;<a href=\&quot;&quot;.$PHP_SELF.&quot;?sort=&quot;.$sortkey.&quot;\&quot;>&quot;.$column[0].&quot;</a> | \n&quot;;
}

You would then have something like this in your HTML:
<a href=&quot;/list.php?sort=serial&quot;>Serial Number</a> |
<a href=&quot;/list.php?sort=title&quot;>Title</a> |
<a href=&quot;/list.php?sort=system&quot;>System</a> |
<a href=&quot;/list.php?sort=employee&quot;>Employee</a> |
<a href=&quot;/list.php?sort=date&quot;>Date</a> |
<a href=&quot;/list.php?sort=genre&quot;>Genre</a> |

Chad. ICQ: 54380631
online.dll
 
Upvote 0 Downvote
Here is the code again a little more readable:

/*
create an array with special keys that will be used
in your links (ie ?sort=$sorts[0] in your code which
would have the output of ?sort=serial).
*/
$sorts = array(&quot;serial&quot; => array(&quot;Serial Number&quot;,&quot;games.snumber&quot;),
&quot;title&quot; => array(&quot;Title&quot;,&quot;games.title&quot;),
&quot;system&quot; => array(&quot;System&quot;,&quot;console.system&quot;),
&quot;employee&quot; => array(&quot;Employee&quot;,&quot;employee.employee&quot;),
&quot;date&quot; => array(&quot;Date&quot;,&quot;games.date&quot;),
&quot;genre&quot; => array(&quot;Genre&quot;,&quot;genre.genre&quot;));

if((!$sort)||(!$sorts[$sort][1])) {
$sort = &quot;title&quot;;
}

$q = &quot;SELECT genre.genre, console.system, employee.employee, games.* FROM games LEFT JOIN console ON console.sysId=games.sysId LEFT JOIN employee ON employee.id=games.id LEFT JOIN genre ON genre.genreId=games.genreId ORDER BY $sorts[$sort][1]&quot;;

/*
and and example of how to get the links going:
*/

foreach($sorts as $sortkey => $column) {
echo &quot;<a href=\&quot;&quot;.$PHP_SELF.&quot;?sort=&quot;.$sortkey.&quot;\&quot;>&quot;.$column[0].&quot;</a> | \n&quot;;
}

Chad. ICQ: 54380631
online.dll
 
Upvote 0 Downvote
Im getting this error when I insert the coding above:

You have an error in your SQL syntax near '[1]' at line 1SELECT genre.genre, console.system, employee.employee, games.* FROM games LEFT JOIN console ON console.sysId=games.sysId LEFT JOIN employee ON employee.id=games.id LEFT JOIN genre ON genre.genreId=games.genreId ORDER BY Array[1]
 
Upvote 0 Downvote
change the query to:

$q = &quot;SELECT genre.genre, console.system, employee.employee, games.* FROM games LEFT JOIN console ON console.sysId=games.sysId LEFT JOIN employee ON employee.id=games.id LEFT JOIN genre ON genre.genreId=games.genreId ORDER BY &quot;.$sorts[$sort][1];
ICQ: 54380631
online.dll
 
Upvote 0 Downvote
thanks, after some reworking of some of the coding, i got it to fit in my table.

Thanks again
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

rmak78
  • Locked
  • Helpful tip
LIMIT USERS ACCESS PER DAY BY IP
Replies
0
Views
177
rmak78
rmak78
bsanderson
  • Locked
  • Question
How do I add a link to change sort order?
Replies
2
Views
207
bsanderson
bsanderson
PeDa
  • Locked
  • Question
POST Content-lLength of ... bytes exceeds the limit of 8388608 bytes in Unknown on line 0
Replies
4
Views
447
feherke
feherke
PeDa
  • Locked
  • Question
Include URL with parameter in table generated by php
Replies
3
Views
376
PeDa
PeDa
Steve-vfp9user
  • Locked
  • Question
SHow full description of event
Replies
1
Views
111
Steve-vfp9user
Steve-vfp9user

Part and Inventory Search

Sponsor

Back
Top