Jump to content

[Solved]Sort Table Column?


wwfc_barmy_army

Recommended Posts

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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 15

Warning: 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 line
print "<td>$qry[dateadded]</td>";
print "<td>$qry[publisher]</td>";
print "</tr>";
}

print "</tbody></table>";
?>
<p>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>[/code]

Any advice?

Thanks.

Peter.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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>&nbsp;</p>
<p>&nbsp;</p>
</body>
</html>
[/code]


me!
Link to comment
Share on other sites

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]


Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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');
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.