tags:

views:

178

answers:

2

I know it's such a basic thing, but a google search hasn't shown me how to resort the rows after clicking the 'th' links.

I've got this:

<table border="1">
  <tr>
    <th>Type:</th>
    <th>Description:</th>
    <th>Recorded Date:</th>
    <th>Added Date:</th>
  </tr>

<?php 
while($row = mysql_fetch_array($result)){
    ?>
    <tr>
        <td><?php echo $row['type'] ?></td>
        <td><?php echo $row['description'] ?></td>
        <td><?php echo $row['recorded_date'] ?></td>
        <td><?php echo $row['added_date'] ?></td>
    </tr>
    <br /> 


  <?php 
}
mysql_close();
?>
</table>

I need to be able to click 'type' and sort alphabetically, and click on either 'Recorded Date' or 'Added Date' and sort by date. I see I need to have the mySql queries do this, but do I set them up as conditionals with the a href's or what? Thanks!

+7  A: 

The easiest way to do this would be to put a link on your column headers, pointing to the same page. In the query string, put a variable so that you know what they clicked on, and then use ORDER BY in your SQL query to perform the ordering.

The HTML would look like this:

<th><a href="mypage.php?sort=type">Type:</a></th>
<th><a href="mypage.php?sort=desc">Description:</a></th>
<th><a href="mypage.php?sort=recorded">Recorded Date:</a></th>
<th><a href="mypage.php?sort=added">Added Date:</a></th>

And in the php code, do something like this:

<?php

$sql = "SELECT * FROM MyTable";

if ($_GET['sort'] == 'type')
{
    $sql .= " ORDER BY type";
}
elseif ($_GET['sort'] == 'desc')
{
    $sql .= " ORDER BY Description";
}
elseif ($_GET['sort'] == 'recorded')
{
    $sql .= " ORDER BY DateRecorded";
}
elseif($_GET['sort'] == 'added')
{
    $sql .= " ORDER BY DateAdded";
}

$>

Notice that you shouldn't take the $_GET value directly and append it to your query. As some user could got to MyPage.php?sort=; DELETE FROM MyTable;

Kibbee
Hmm. I'm getting a Parse error: syntax error, unexpected T_IS_EQUAL for that first 'if' statement
Joel
Yeah, my syntax probably isn't perfect. I think it's fixed now.
Kibbee
I can't edit it myself, but I found the errors. Main one is you need $sql not sql. Also, my table names are different on a couple columns. But, THANK you for doing this! It got me with the right answer!
Joel
editted to fix the syntax problems. PHP isn't a language I use everyday. Haven't used it in a while. Those $ always get me. I always found it really annoying to have to start variables with a special character. Can't think of why it would be required. No other language I can think of requires anything like that.
Kibbee
+1  A: 

That's actually pretty easy, here's a possible approach:

<table>
    <tr>
        <th>
            <a href="?orderBy=type">Type:</a>
        </th>
        <th>
            <a href="?orderBy=description">Description:</a>
        </th>
        <th>
            <a href="?orderBy=recorded_date">Recorded Date:</a>
        </th>
        <th>
            <a href="?orderBy=added_date">Added Date:</a>
        </th>
    </tr>
</table>
<?php
$orderBy = array('type', 'description', 'recorded_date', 'added_date');

$order = 'type';
if (isset($_GET['orderBy']) && in_array($_GET['orderBy'], $orderBy)) {
    $order = $_GET['orderBy'];
}

$query = 'SELECT * FROM aTable ORDER BY '.$order;

// retrieve and show the data :)
?>

That'll do the trick! :)

GuidoH
Why the -1 vote?edit: -3.. :/ Why!?
GuidoH
There's definitely **NO** possible SQL injection, look closer! Whitelisted the possible fields to order by.
GuidoH
I didn't downvote, but for some reason, I don't remember the checks being there when I first saw your answer. However I may just have missed it, and some others may have missed it as well. I like your style better than mine, so I'm giving you an upvote too.
Kibbee
Thank you both for your help! Upvotes all around!
Joel
@Kibbee you didn't mistake it for Sarfraz's attempt did you? Which he's now deleted but had sql injection issues?
No, I still see Sarfraz's answer. I might have had the 2 confused. For some reason I remember a straight concatenation of the query and the $_GET. Guess I just missed something, others may have also.
Kibbee