Solved

Drop down script

Posted on 2007-04-04
26
238 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EditableGrid how to fetch rows from MySql in php 14 48
PHP function parameters defined 14 43
MySQL-Design Help 12 44
How do I post more than 1 item to php backend 24 32
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses how to create an extensible mechanism for linked drop downs.
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…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

730 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