Control the organization of data table by headers

Kay1021 · 05-13-2009, 07:02 PM

I have a table that contains data from my database. I was wondering if there was a way that I could make the headers (name, date joined, expiry date and fee) control the order of the data in both ascending and descending order. And make the which header is organizing the data currently look different (ie. when data is organized by name...name is bold while other headers are not). I've tried searching for how to do this...but have had no luck with actually accomplishing it.

I'd appreciate any help.

Thanks

Here's my code:

PHP Code:


			
<?php 

print' 

<table class="myTable"> 

    <thead> 

      

    <th scope="col"><h2>Name</h2></th> 

    <th scope="col"><h2>Phone Number</h2></th> 

    <th scope="col"><h2>Email</h2></th> 

    <th scope="col"><h2>Date Joined</h2></th> 

    <th scope="col"><h2>Expiry Date</h2></th> 

    <th scope="col"><h2>Fee Paid</h2></th> 

    <th scope="col"><h2>Edit</h2></th> 

    <th scope="col"><h2>Delete</h2></th> 

      

    </thead>'; 

      

      

    $sql = "SELECT name, phone, email, date_joined, expiry_date, fee FROM customer ORDER BY name";

      

      

    $qry = mysql_query($sql); 

      

    if (mysql_num_rows($qry) > 0) { 

        while ($rs = mysql_fetch_assoc($qry)) { 

              

              

            print' 

            <td>' . $rs['name'] . '</td> 

            <td>' . $rs['phone'] . '</td> 

            <td>' . $rs['email'] . '</td> 

            <td>' . $rs['date_joined'] . '</td> 

            <td>' . $rs['expiry_date'] . '</td> 

            <td>' . $rs['fee'] . '</td> 

            <td><a href="edit.php">Edit</a></td> 

            <td><a href="delete.php">Delete</a></td><tr>'; 

        } 

    } else { 

        print '<h4>no result found</h4>'; 

    } 

print' 

</table>'; 

?>

allworknoplay · 05-13-2009, 07:29 PM

There's a lot of javascripts out there that can do this. Of course I don't know of any offhand but I've played with a lot in the past.

If you only want to do it the PHP way, well you would make each a link that references the page. So when a user clicks on the link, it re-posts the page and provides added data to your SQL query like ASC or DESC for example...

Since you clicked on the header, you can now change the color to show that this header is highlighted and clicked on...

The JS way though is a bit smoother because it doesn't have to reload the page, it just takes data that has already been outputted...

I'm not that very good at JS though so you'd have to get a response from the JS experts around here..

Wildhoney · 05-13-2009, 09:51 PM

Take the following as an example. Hopefully it should explain it well, and give you also a working example to build upon

php Code:

/* The original query with blanks to be filled in. */
$szSQL = "SELECT * FROM members ORDER by %s %s";

/* Set the defaults if invalid fields are supplied, or none at all. */
$szDefaultField = 'name';
$szDefaultOrder = 'asc';

/* Set the restrictions for both so users can't enter just anything. */
$aAllowedFields = array('name', 'username');
$aAllowedOrders = array('desc', 'asc');

/* Set to the ones specified, but if we have none, set to the defaults. */
$szField = isset($_GET['field']) ? strtolower($_GET['field']) : $szDefaultField;
$szOrderBy = isset($_GET['order']) ? strtolower($_GET['order']) : $szDefaultOrder;

/* Check if we're allowed this particular field in the query. */
if (!in_array($szField, $aAllowedFields))
{
    $szField = $szDefaultField;
}

/* Check if we're allowed this particular ordering in the query. */
if (!in_array($szOrderBy, $aAllowedOrders))
{
    $szOrderBy = $szDefaultOrder;
}

/* Build the new query with the ordering on field and ASC/DESC. */
$szSQL = sprintf($szSQL, $szField, $szOrderBy);

/* Query to execute... */
printf("Executing Query: %s", $szSQL);

Here is the form to be used in conjunction with this code:

html4strict Code:

<h1>Orders</h1>

<ul>
    <li><a href="?field=name&order=asc">Order by Name Ascending</a></li>
    <li><a href="?field=username&order=asc">Order by Username Ascending</a></li>
    <li><a href="?field=name&order=desc">Order by Name Descending</a></li>
    <li><a href="?field=username&order=desc">Order by Username Descending</a></li>
</ul>

allworknoplay · 05-13-2009, 09:59 PM

BTW, WH:

Did your avatar come from a bigger picture somewhere or did you make it up yourself? It kinda looks like it has a matrix background, it would be a cool wallpaper if you had a higher resolution pic of it...

Wildhoney · 05-13-2009, 11:01 PM

It's not THAT big a picture, sadly.

Artist: http://fredeinaudi.com/main.html

allworknoplay · 05-13-2009, 11:03 PM

LOL that is such a freaky picture, I love it!