?
Solved

Drop down script

Posted on 2007-04-04
26
Medium Priority
?
241 Views
Last Modified: 2013-12-13
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?
0
Comment
Question by:jvuz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
  • 3
26 Comments
 
LVL 28

Expert Comment

by:gamebits
ID: 18849744
I use the header in the table as a link to sort by that field.
0
 
LVL 21

Author Comment

by:jvuz
ID: 18849780
Can you please explain a bit more?
0
 
LVL 28

Assisted Solution

by:gamebits
gamebits earned 200 total points
ID: 18849822
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Author Comment

by:jvuz
ID: 18849832
OK, I understand that, but how do you script this?
0
 
LVL 28

Expert Comment

by:gamebits
ID: 18849914
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
 

Expert Comment

by:Sailo100
ID: 18849927
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
 

Accepted Solution

by:
Sailo100 earned 1800 total points
ID: 18849939
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
 
LVL 21

Author Comment

by:jvuz
ID: 18849946
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
 

Expert Comment

by:Sailo100
ID: 18849972
Hmm ok.... try this instead :

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

0
 
LVL 21

Author Comment

by:jvuz
ID: 18850023
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
 
LVL 21

Author Comment

by:jvuz
ID: 18850042
This is the message I get when I choose articles

Unknown column 'articles' in 'order clause'
0
 

Expert Comment

by:Sailo100
ID: 18850093
Is there any relationshop between these 2 tables?
0
 

Expert Comment

by:Sailo100
ID: 18850114
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
 
LVL 21

Author Comment

by:jvuz
ID: 18850135
"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
 
LVL 21

Author Comment

by:jvuz
ID: 18850149
Then I get:

Column 'id' in field list is ambiguous
0
 

Expert Comment

by:Sailo100
ID: 18850162
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
 
LVL 21

Author Comment

by:jvuz
ID: 18850212
Sorry, now I get an other message:

Illegal mix of collations (latin1_bin,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
0
 

Expert Comment

by:Sailo100
ID: 18850294
You need to make sure both column charsets are the same for name and author in the 2 tables.

0
 
LVL 21

Author Comment

by:jvuz
ID: 18850365
Both tables are in latin1_swedish_ci
0
 
LVL 21

Author Comment

by:jvuz
ID: 18850395
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
 

Expert Comment

by:Sailo100
ID: 18850406
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
 
LVL 21

Author Comment

by:jvuz
ID: 18850445
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
 

Expert Comment

by:Sailo100
ID: 18850542
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
 
LVL 21

Author Comment

by:jvuz
ID: 18855875
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
 
LVL 21

Author Comment

by:jvuz
ID: 18856492
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
 

Expert Comment

by:Sailo100
ID: 18856574
Glad to help!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question