listbox and multiple php/mysql queries

BIOSTALL

Hey, i've run into a bit of a problem which i can't seem to solve. Basically i'm making a forum and i'm working on a section that enables you to view information about the other users signed up the forum in one big table. I have 6 columns and a listbox that enables you to sort the data regarding the selected item chosen (ie. if postcount is chosen the table will be sorted by post count) Anyway, here's my code for the listbox:

<select name="menusortby" id="menusortby" onChange="MM_jumpMenu('parent',this,0)">
<option value=""></option>
<option value="ForumViewAllMembers.php?sortby=0">Member Username</option>
<option value="ForumViewAllMembers.php?sortby=1">Member Since</option>
<option value="ForumViewAllMembers.php?sortby=2">Post Count</option>
<option value="ForumViewAllMembers.php?sortby=3">Last Active Date</option>
<option value="ForumViewAllMembers.php?sortby=4">Last Post Date</option>
<option value="ForumViewAllMembers.php?sortby=5">Last Post Title</option>
</select>

As you can see, a variable 'sortby' is assigned a value regarding the option chosen. Depending on what the variable holds will depend what sql query is run: heres my php:

<?php
	   $sort = ($_GET['sortby']);

   if ($sort=0) {
   $result = mysql_query("SELECT * FROM Forum_Users ORDER BY username",$connection);
   }
   if ($sort=1) {
   $result = mysql_query("SELECT * FROM Forum_Users ORDER BY membersince",$connection);
   }
   if ($sort=2) {
   $result = mysql_query("SELECT * FROM Forum_Users ORDER BY postcount DESC",$connection);
   }
   if ($sort=3) {
   $result = mysql_query("SELECT * FROM Forum_Users ORDER BY lastactivedate DESC",$connection);
   }
   if ($sort=4) {
   $result = mysql_query("SELECT * FROM Forum_Users ORDER BY lastpostdate DESC",$connection);
   }
   if ($sort=5) {
   $result = mysql_query("SELECT * FROM Forum_Users ORDER BY lastpostsubject",$connection);
   }

	while ($row=mysql_fetch_array($result)){
	$uname = $row['username'];
	$membersince = $row['membersince'];
	$postcount = $row['postcount'];
	$lastactivedate = $row['lastactivedate'];
	$lastpostdate = $row['lastpostdate'];
	$lastposttitle = $row['lastpostsubject'];

	$displayblock .= "<tr><td><div align='center'>$uname</div></td><td><div align='center'>$membersince</div></td><td><div align='center'>$postcount</div></td><td><div align='center'>$lastactivedate</div></td><td><div align='center'>$lastpostdate</div></td><td><div align='center'>$lastposttitle</div></td></tr>";
	}
	echo $displayblock;
   ?>

I know my choice of if loops is inefficient but i'm unsure of another way to do it. Anyway, the problem i have is that whatever option is chosen, the table still will not sort and i can't figure out why. Like if you choose postcount, you can see it should sort the table in descending order but it doesn't. Sorry if this is a bit complicated but i just cant figure it out so if anyone can see something wrong with this, please tell me.

Thank you, BIOSTALL

leatherback

Hi,

without going into the rest of your code.. You are completely off. Sorry. Didn't mean to be rude. But let me explain a bit:

In your option form, all you need to do is pass the number, and the form should have the action:

<form action="ForumViewAllMembers.php" method = post>
<select name="menusortby" id="menusortby" onChange="MM_jumpMenu('parent',this,0)">
<option value=""></option>
<option value="0">Member Username</option>
<option value="1">Member Since</option>
<option value="2">Post Count</option>
<option value="3">Last Active Date</option>
<option value="4">Last Post Date</option>
<option value="5">Last Post Title</option>
</select>
...
</form>

Then in the PhP section:


// create an array with the sort options, where the key is linked to the numbers in the form

$orderoptions = array(
0 => "username",
1 => "membersince"
)

// get the submitted value for sortby
$sort = ($_POST['sortby']);

// create the query. Use the array, in combination with the selected option to get the right order colum
$query = "SELECT * FROM Forum_Users ORDER BY $oderoptions[$sort]";
$result = mysql_query($query,$connection);

Hope this helps you to get on your way..

BIOSTALL

Number 1: $sort = ($_POST['sortby']); << where does the value 'sortby' come from when the form is submitted??

Number 2: If you look at the queries in my original post you can see i've got a mixture of ASC and DESC orderings. What determines this in the method you've suggested??

Thanks,

BIOSTALL

leatherback

Hi,

The snippet I wrote was an example of the way to go.

Two questions:
1- Sorry, the sortby SHOULD HAVE BEEN menusortby. I took it from your script. It reflects te name of the variable you used in the dropdown box. This is POSTed to the next page (See form method) and is therefor available in the $_POST variable.

The sorting issue. In the array you can place anything you want. So you could also do:

$orderoptions = array(
0 => "username ASC",
1 => "membersince DESC"
)

bradgrafelman

Also, you could try using [man]switch/man instead, like so:

<?php 
       $sort = ($_GET['sortby']); 

   switch($sort) {

		case 0:
			$append = "username";
			break;

		case 1:
			$append = "membersince"; 
			break;

		case 2:
			$append = "postcount DESC";
			break;

		// and so on...

   } 

   $result = mysql_query("SELECT * FROM Forum_Users ORDER BY $append");

    while ($row=mysql_fetch_array($result)){ 
    $uname = $row['username']; 
    $membersince = $row['membersince']; 
    $postcount = $row['postcount']; 
    $lastactivedate = $row['lastactivedate']; 
    $lastpostdate = $row['lastpostdate']; 
    $lastposttitle = $row['lastpostsubject']; 

    $displayblock .= "<tr><td><div align='center'>$uname</div></td><td><div align='center'>$membersince</div></td><td><div align='center'>$postcount</div></td><td><div align='center'>$lastactivedate</div></td><td><div align='center'>$lastpostdate</div></td><td><div align='center'>$lastposttitle</div></td></tr>"; 
    } 
    echo $displayblock;
?>

EDIT: The solution in leatherback's previous post will work also. Just offering a different approach to the matter.

BIOSTALL

Thanks for your time, i'll look into your method 🙂

Can i ask though, just out of curiosity, is there a way that my method could work??

Thanks again, BIOSTALL

bradgrafelman

Certainly. Look at all of your IF statements:

if ($sort=0) {

Let me translate that into English:

"If you are able to change the value of $sort to 0, do this:"

You, however, need a comparison operator (manual page: [man]operators.comparison[/man]), like so:

if ($sort==0) {