Output ordered by

Question

I have a table that puts all of the information from my database on a page. I want to add a drop down that says order by first name, last name, group, etc...

How do I accomplish this?

    <?php $query = "SELECT * FROM contacts WHERE type = 'lead'";
$result = mysql_query($query);
if (!$result) {
die('Invalid query: ' . mysql_error()); } ?>

    <table>
    <tr><td>ID</td>     <td>Name</td>       <td>Email</td></tr>
    <?php

    while ($row = mysql_fetch_assoc($result))
    {
        echo '<tr>';
        echo '<td>' . $row['id'] . '</td>';
        echo '<td>' . $row['name'] . '</td>';
        echo '<td>' . $row['email'] . '</td>';
        echo '<td>' . '<a href = "/admin/leads/convert-contact.php?id=' . $row[id] . '">Make Contact</a>';
        echo '</tr>';
    }
    echo '</table>';


?>

What have you tried so far ? Mysql_* functions are deprecated. Check mysqli_* or PDO. To your question: put there form with dropdown and submit button. To the dropdown add some values 1 2 3 etc. then you will make switch of this and you will add to query (1 - first name => order by first_name), (2 - last name - order by last_name) etc — DeiForm, Aug 09 '13 at 17:46
Please DeiForm, explain a little further on this sql query. How do I take the result from the select / option field and change my sql statement to order by "X". Can someone elaborate on what the sql statement should look like? — JML1179, Aug 09 '13 at 17:49

score 0 · Answer 1 · answered Aug 09 '13 at 17:48

SELECT *
  FROM Whatever
       ORDER BY CASE 
                  WHEN @order =  'first' THEN first_name
                  WHEN @order = 'last' THEN last_name
                  WHEN @order = 'group' THEN group_name
                  ELSE CAST(id AS Varchar)
                END

Note that all of the ordering columns must be type-compatible (e.g. all chars/varchars, or all numeric)

Muhammad Haseeb Khan · Answer 2 · 2013-08-09T17:54:48.857

 <form action='database.php' type='post'>
 <select name='orderby'>
 <option value='firstname'>First Name</option>
 <option value='secondname'>Second Name</option>
 <option value='group'> group</option>
 </select>
 </form>

in PHP file

if($_POST['orderby']=='firstname') $lastpart= 'ORDER BY firstname';
if($_POST['orderby']=='lastname') $lastpart= 'ORDER BY lastname';
if($_POST['orderby']=='group') $lastpart= 'GROUP BY field';

<?php $query = "SELECT * FROM contacts WHERE type = 'lead' $lastpart"; ----

Razorphyn · Answer 3 · 2013-08-09T18:04:12.897

Maybe:

<form action='this page url' type='post'>
    <select name='orderby'>
        <option value='0'>First Name</option>
        <option value='1'>Second Name</option>
        <option value='2'> Name</option>
        <option value='3'> Group</option>
    </select>
    <input type='submit' name='querygroup' value='Order'/>
</form>
<table>
<tr><td>ID</td>     <td>Name</td>       <td>Email</td></tr>

<?php 
    if(isset($_POST['querygroup'])){
        if(is_numeric($_POST['orderby'])){
            $query = "SELECT * FROM contacts WHERE type = 'lead' ORDER BY CASE '".$_POST['orderby']."' WHEN '0' THEN field_name WHEN '1' THEN field_name [other options] ELSE field_name END";
            $result = mysql_query($query);
            if (!$result) {
                die('Invalid query: ' . mysql_error()); 
            }

            while ($row = mysql_fetch_assoc($result)){
                echo '<tr>';
                echo '<td>' . $row['id'] . '</td>';
                echo '<td>' . $row['name'] . '</td>';
                echo '<td>' . $row['email'] . '</td>';
                echo '<td>' . '<a href = "/admin/leads/convert-contact.php?id=' . $row[id] . '">Make Contact</a>';
                echo '</tr>';
            }
        }
    }
?>
</table>

score 0 · Answer 4 · edited May 23 '17 at 12:05

A little bit of AJAX can make this a re-sortable output. As you can see from the following example, it's not that difficult.

The below code will put a drop-down on the page, and allow the user to choose sort order. Upon choosing, the sorted results will appear in the div. The user can then choose a different sort order, and those results will appear in the div.

HTML:

<html>
    <head>
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
        <script type="text/javascript">
            $(document).ready(function() {

                $('#mysel').change(function() {
                    var sby = $(this).val();
                    //alert(sby);
                    $.ajax({
                        type: "POST",
                        url: "another_php_file.php",
                        data: 'sortby=' + sby,
                        success: function(thedata) {
                            $("#results").html(thedata) ;
                        }
                    });
                });

            }); //END $(document).ready()

        </script>
    </head>
<body>

    <div id="results"></div>
    <select id="mysel">
        <option value = '0'>Choose One</option>
        <option value = 'last'>Sort by Last Name</option>
        <option value = 'first'>Sort by First Name</option>
    </select>


</body>
</html>

another_php_file.php

<?php

    $order = $_POST['sortby'];

    // Do your database login here

    $output = '<table><tr><td>ID</td><td>Name</td><td>Email</td></tr>';

    $result = mysql_query("SELECT * FROM contacts WHERE type = 'lead' ORDER BY '$order'");
    $num_rows = mysql_num_rows($result);

    while ($row = mysql_fetch_assoc($result)) {
        $output .= '<tr>';
        $output .= '<td>' . $row['id'] . '</td>';
        $output .= '<td>' . $row['name'] . '</td>';
        $output .= '<td>' . $row['email'] . '</td>';
        $output .= '<td>' . '<a href = "/admin/leads/convert-contact.php?id=' . $row[id] . '">Make Contact</a>';
        $output .= '</tr>';
    }
    $output .= '</table>';

    echo $output;

If you are new to AJAX, here are some very simple examples to give you the hang of it. They are well worth the 15 or 20 mins to play around with.

A simple example

More complicated example

Populate dropdown 2 based on selection in dropdown 1

Output ordered by

4 Answers4