Jump to content

Archived

This topic is now archived and is closed to further replies.

wwfc_barmy_army

[Solved]Sort Table Column?

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.

Share this post


Link to post
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]

Share this post


Link to post
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.

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
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);
and
while ($qry = mysql_fetch_array($result))

Thanks.

Peter.

Share this post


Link to post
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!

Share this post


Link to post
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]


Share this post


Link to post
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.

Share this post


Link to post
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');

Share this post


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

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites

×

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.