Solved

Drop down script

Posted on 2007-04-04
26
227 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
  • 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 50 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
 
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 450 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
This article discusses how to create an extensible mechanism for linked drop downs.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
The viewer will learn how to dynamically set the form action using jQuery.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now