Drop down script

Hello,

through the next code I get data from a db and put it in a table in a specific order (in this case: id)

<table border="1" cellpadding="0" cellspacing="0" width="100%">
                                     <tr>
                                           <td width="25%"><b>&nbsp;ID</b></td>
                                          <td width="25%"><b>&nbsp;Username</b></td>
                                          <td width="25%"><b>&nbsp;Level</b></td>
                                          <td width="20%"><b>&nbsp;Articles</b></td>
                                    </tr>
                               <?php
                                     $res=mysql_query("SELECT id, name, level FROM users ORDER by id") or die(mysql_error());
                                    while($r = mysql_fetch_array($res)) {
                                    $amount=mysql_query("SELECT count(article) FROM articles WHERE author='$r[name]'");
                        echo '<tr><td>&nbsp;'.$r['id'].'</td><td>&nbsp;'.$r['name'].'</td><td>&nbsp;'.$r['level'].'</td><td>&nbsp;'.mysql_result($amount, 0).'</td></tr>';                  
                                    }
                               ?>
                               </table>

Now, for different ordering, I could create different pages, but I was wondering if it is possible to do this through for instance a dropdown list. Is this possible?
LVL 21
jvuzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gamebitsCommented:
I use the header in the table as a link to sort by that field.
0
jvuzAuthor Commented:
Can you please explain a bit more?
0
gamebitsCommented:
Ok, if I understand you rigth you display the information in a table with these headers

ID | Username | Level | Articles

and they are now sorted by ID,

What I do is for example Username would be a link to query the database, the exact same query except the ORDER BY clause would be username, you display the exact same table but the sorting is made from the username.

You can do that for every headers that you want to be able to be sorted by, all the user has to do is click on the header of his choice to sort the entry using that criteria (filter)
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

jvuzAuthor Commented:
OK, I understand that, but how do you script this?
0
gamebitsCommented:
You would have to replace the ORDER by id with a variable ($sort), so change this

<?php
$res=mysql_query("SELECT id, name, level FROM users ORDER by id") or die(mysql_error());
                                    while($r = mysql_fetch_array($res)) {

to  
<?php
$res=mysql_query("SELECT id, name, level FROM users ORDER by $sort") or die(mysql_error());
                                    while($r = mysql_fetch_array($res)) {

This

<td width="25%"><b>&nbsp;Username</b></td>

would become

<a href=thepage.php?sort=username>Username</a>

On receiving page you use the get method

$sort = $_GET['username'];

With a little css you can style the header so they don't look like a link (remove the underline, overide the default color, etc.)

Just a little sentence saying to click on the header to sort by is all it is needed to direct the users.
0
Sailo100Commented:
Try this :

<?php
if (isset($_GET[order])) $order = $_GET[order];
else $order = 'id';
?>
<table border="1" cellpadding="0" cellspacing="0" width="100%">
  <tr colspan="4">
      <form method="GET" action="<?=$_SERVER['PHP_SELF'];?>" name="orderform" id="orderform">
        <select name="order" onchange="document.orderform.submit();">
          <option value="id" <?php if ($order == "id") echo 'selected="selected"';?>>ID</option>
          <option value="username" <?php if ($order == "username") echo 'selected="selected"';?>>Username</option>
          <option value="level" <?php if ($order == "level") echo 'selected="selected"';?>>Level</option>
          <option value="articles" <?php if ($order == "articles") echo 'selected="selected"';?>>Articles</option>
        </select>
    </form>  
  </tr>
  <tr>
    <td width="25%"><b>&nbsp;ID</b></td>
    <td width="25%"><b>&nbsp;Username</b></td>
    <td width="25%"><b>&nbsp;Level</b></td>
    <td width="20%"><b>&nbsp;Articles</b></td>
  </tr>
  <?php
      $res=mysql_query("SELECT id, name, level FROM users ORDER by $order") or die(mysql_error());
      while($r = mysql_fetch_array($res)) {
      $amount=mysql_query("SELECT count(article) FROM articles WHERE author='$r[name]'");
      echo '<tr><td>&nbsp;'.$r['id'].'</td><td>&nbsp;'.$r['name'].'</td><td>&nbsp;'.$r['level'].'</td><td>&nbsp;'.mysql_result($amount, 0).'</td></tr>';                  
      }
      ?>
</table>
0
Sailo100Commented:
Sorry my bad...... try this

<?php
if (isset($_GET[order])) $order = $_GET[order];
else $order = 'id';
?>
<table border="1" cellpadding="0" cellspacing="0" width="100%">
  <tr>
  <td colspan="4">
      <form method="GET" action="<?=$_SERVER['PHP_SELF'];?>" name="orderform" id="orderform">
        <select name="order" onchange="document.orderform.submit();">
          <option value="id" <?php if ($order == "id") echo 'selected="selected"';?>>ID</option>
          <option value="username" <?php if ($order == "username") echo 'selected="selected"';?>>Username</option>
          <option value="level" <?php if ($order == "level") echo 'selected="selected"';?>>Level</option>
          <option value="articles" <?php if ($order == "articles") echo 'selected="selected"';?>>Articles</option>
        </select>
    </form>  
  </td>
  </tr>
  <tr>
    <td width="25%"><b>&nbsp;ID</b></td>
    <td width="25%"><b>&nbsp;Username</b></td>
    <td width="25%"><b>&nbsp;Level</b></td>
    <td width="20%"><b>&nbsp;Articles</b></td>
  </tr>
  <?php
      $res=mysql_query("SELECT id, name, level FROM users ORDER by $order") or die(mysql_error());
      while($r = mysql_fetch_array($res)) {
      $amount=mysql_query("SELECT count(article) FROM articles WHERE author='$r[name]'");
      echo '<tr><td>&nbsp;'.$r['id'].'</td><td>&nbsp;'.$r['name'].'</td><td>&nbsp;'.$r['level'].'</td><td>&nbsp;'.mysql_result($amount, 0).'</td></tr>';                  
      }
      ?>
</table>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jvuzAuthor Commented:
Thanx,

Sailo100, I get this error:

Use of undefined constant order - assumed 'order' in...
on the following line:
if (isset($_GET[order])) $order = $_GET[order];
0
Sailo100Commented:
Hmm ok.... try this instead :

if (isset($_GET["order"])) $order = $_GET["order"];
else $order = 'id';

0
jvuzAuthor Commented:
Thanx Sailo, that works now, except for articles. The problem is that all the other (id, name,  level) Iget from the users table, but articles, Iget from the table articles. For that I use this query:

$amount=mysql_query("SELECT count(article) FROM articles WHERE author='$r[name]'");
0
jvuzAuthor Commented:
This is the message I get when I choose articles

Unknown column 'articles' in 'order clause'
0
Sailo100Commented:
Is there any relationshop between these 2 tables?
0
Sailo100Commented:
Try this :

      $res=mysql_query("SELECT id, name, level, count(article) As articles FROM users LEFT JOIN articles ON (users.name = articles.author) ORDER by $order") or die(mysql_error());
      while($r = mysql_fetch_array($res)) {
      echo '<tr><td>&nbsp;'.$r['id'].'</td><td>&nbsp;'.$r['name'].'</td><td>&nbsp;'.$r['level'].'</td><td>&nbsp;'.$r['articles'].'</td></tr>';
      }
0
jvuzAuthor Commented:
"Is there any relationshop between these 2 tables?"
Yes there is, in table articles, the field "author" is a name from the table "name" in table users.
0
jvuzAuthor Commented:
Then I get:

Column 'id' in field list is ambiguous
0
Sailo100Commented:
That means id appears in both tables.... you can fix with users.id in the query :

      $res=mysql_query("SELECT users.id, name, level, count(article) As articles FROM users LEFT JOIN articles ON (users.name = articles.author) ORDER by $order") or die(mysql_error());
      while($r = mysql_fetch_array($res)) {
      echo '<tr><td>&nbsp;'.$r['id'].'</td><td>&nbsp;'.$r['name'].'</td><td>&nbsp;'.$r['level'].'</td><td>&nbsp;'.$r['articles'].'</td></tr>';
      }
0
jvuzAuthor Commented:
Sorry, now I get an other message:

Illegal mix of collations (latin1_bin,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
0
Sailo100Commented:
You need to make sure both column charsets are the same for name and author in the 2 tables.

0
jvuzAuthor Commented:
Both tables are in latin1_swedish_ci
0
jvuzAuthor Commented:
OK, that's sorted, now I get:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
0
Sailo100Commented:
Ok try this :

     $res=mysql_query("SELECT users.id, name, level, count(article) As articles FROM users LEFT JOIN articles ON (users.name = articles.author) GROUP BY users.id ORDER by $order") or die(mysql_error());
0
jvuzAuthor Commented:
Great! And how do I sort in descending order for the articles. Normally its by adding "desc', but I think my way of doing it here, isn't correct.
0
Sailo100Commented:
Ok... you could add extra option to the drop down for doign descending.....


<?php
if (isset($_GET["order"])) {
      $order = $_GET["order"];
      if (substr($order,0,4) == "desc") {
            $desc = "DESC";
            $order = substr($order,4,strlen($order)-4);
      }
      else $desc = "";
}
else $order = 'id';

?>

<table border="1" cellpadding="0" cellspacing="0" width="100%">
  <tr colspan="4">
      <form method="GET" action="<?=$_SERVER['PHP_SELF'];?>" name="orderform" id="orderform">
        <select name="order" onchange="document.orderform.submit();">
          <option value="id" <?php if ($order == "id") echo 'selected="selected"';?>>ID</option>
          <option value="username" <?php if ($order == "username") echo 'selected="selected"';?>>Username</option>
          <option value="level" <?php if ($order == "level") echo 'selected="selected"';?>>Level</option>
          <option value="articles" <?php if ($order == "articles") echo 'selected="selected"';?>>Articles</option>
          <option value="descid" <?php if ($order == "descid") echo 'selected="selected"';?>>ID Descending</option>
          <option value="descusername" <?php if ($order == "descusername") echo 'selected="selected"';?>>Username Descending</option>
          <option value="desclevel" <?php if ($order == "desclevel") echo 'selected="selected"';?>>Level Descending</option>
          <option value="descarticles" <?php if ($order == "descarticles") echo 'selected="selected"';?>>Articles Descending</option>
        </select>
    </form>  
  </tr>
  <tr>
    <td width="25%"><b>&nbsp;ID</b></td>
    <td width="25%"><b>&nbsp;Username</b></td>
    <td width="25%"><b>&nbsp;Level</b></td>
    <td width="20%"><b>&nbsp;Articles</b></td>
  </tr>
  <?php
$res=mysql_query("SELECT users.id, name, level, count(article) As articles FROM users LEFT JOIN articles ON (users.name = articles.author) GROUP BY users.id ORDER by $order $desc") or die(mysql_error());      while($r = mysql_fetch_array($res)) {
      echo '<tr><td>&nbsp;'.$r['id'].'</td><td>&nbsp;'.$r['name'].'</td><td>&nbsp;'.$r['level'].'</td><td>&nbsp;'.$r['articles'].'</td></tr>';
      }
      ?>
</table>
0
jvuzAuthor Commented:
I just want that when I choose articles, it is ordered 'desc'. The rest is good. For the moment everything is ordered (by default, which is good) asc, I just want articles (the last item) to be sorted desc. Is that possible?
0
jvuzAuthor Commented:
OK, found it:

Changed this:
<option value="articles" <?php if ($order == "articles") echo 'selected="selected"';?>>&nbsp;Articles&nbsp;&nbsp;</option>

by <option value="articles desc" <?php if ($order == "articles desc") echo 'selected="selected"';?>>&nbsp;Articles&nbsp;&nbsp;</option>

And that worked
0
Sailo100Commented:
Glad to help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.