Jump to content


Photo

[Solved]Sort Table Column?


  • Please log in to reply
12 replies to this topic

#1 wwfc_barmy_army

wwfc_barmy_army
  • Members
  • PipPipPip
  • Advanced Member
  • 320 posts

Posted 30 September 2006 - 05:43 PM

Hello,

I have this code:

<?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>";
?>


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:

<thead><tr><td>Site Name</td><td>Site Link</td><td>Site Download</td><td>Date Added</td><td>Publisher</td></tr></thead><tbody>


Any advice is appreciated.

Thanks.

Peter.

#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 30 September 2006 - 05:54 PM

In headers listing:
<a href="myscript.php?sortby=name">name</a>
<a href="myscript.php?sortby=blah">blah</a>
etc..
then in your script, before the sql query, do something like this:
$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');

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 wwfc_barmy_army

wwfc_barmy_army
  • Members
  • PipPipPip
  • Advanced Member
  • 320 posts

Posted 30 September 2006 - 06:13 PM

Thanks for your reply. Although i am getting this error:

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


My code changed from when i posted, here is the new stuff:
<!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>

Any advice?

Thanks.

Peter.

#4 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 30 September 2006 - 06:28 PM

Your using single quotes so you need to enclose your $column value or dot it -> ' . $column . '

Instead of this...

$result = mysql_query('SELECT * FROM site ORDER BY $column DESC');

do this...

$result = mysql_query('SELECT * FROM site ORDER BY {$column} DESC');


Single quotes do not interpolate

#5 wwfc_barmy_army

wwfc_barmy_army
  • Members
  • PipPipPip
  • Advanced Member
  • 320 posts

Posted 30 September 2006 - 06:33 PM

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.

#6 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 30 September 2006 - 06:52 PM

try this...

<!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>


me!

#7 wwfc_barmy_army

wwfc_barmy_army
  • Members
  • PipPipPip
  • Advanced Member
  • 320 posts

Posted 30 September 2006 - 07:03 PM

Thanks. Got it now :)

Thanks.

Peter.

#8 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 01 October 2006 - 06:25 AM

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:

<a href="myscript.php?sortid=0">name</a>
<a href="myscript.php?sortid=1">blah</a>
etc..

and then in your query code you can do this:


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");



Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#9 wwfc_barmy_army

wwfc_barmy_army
  • Members
  • PipPipPip
  • Advanced Member
  • 320 posts

Posted 01 October 2006 - 08:30 AM

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:)):
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]
{

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.


#10 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 01 October 2006 - 04:02 PM

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("SELECT * FROM site ORDER BY $column DESC");

not single quotes:

$result = mysql_query('SELECT * FROM site ORDER BY $column DESC');

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');
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#11 wwfc_barmy_army

wwfc_barmy_army
  • Members
  • PipPipPip
  • Advanced Member
  • 320 posts

Posted 01 October 2006 - 06:24 PM

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.

#12 ponsho

ponsho
  • Members
  • PipPip
  • Member
  • 24 posts

Posted 01 October 2006 - 06:34 PM

if (isset($_GET['sortid'])) { 
   switch ($_GET['sortid']) {
      case 0 : $column = 'name'; break;
      case 1 : $column = 'editorrating'; break;
      default : $column = 'name';
   }
}

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.

switch ($_GET['sortid']) {
   case 0 : $column = 'name'; break;
   case 1 : $column = 'editorrating'; break;
   default : $column = 'name';
}


#13 wwfc_barmy_army

wwfc_barmy_army
  • Members
  • PipPipPip
  • Advanced Member
  • 320 posts

Posted 01 October 2006 - 06:37 PM

Ahh. Thanks mate! :)

Peter.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users