Sorting table populated by SQL database..

[dpedroia]

My website contains a page called 'Browse Events', where events are listed along with their Event ID, Event Name, Sport, State / Location, Venue, and Date of Event. The code I am using to pull this information from my SQL database and create the table is as follows:

 

<?php

$q = mysql_query("SELECT * FROM events") or die(mysql_error());
$x=0;

echo"
<tr>
<th style=text-align:left>Event ID</th>
<th style=text-align:left>Event Name</th>
<th style=text-align:left>Sport</th>
<th style=text-align:left>State</th>
<th style=text-align:left>Venue</th>
<th style=text-align:left>Date</th>";

while($row = mysql_fetch_array($q)){

extract($row);

if ($x==0){
echo "</tr><tr>";
}

$x++;

if ($x == 100){
$x = 0;
}

echo "<td>$id</td>";
echo "<td>$eventname</td>";
echo "<td>$sport</td>";
echo "<td>$state</td>";
echo "<td>$venue</td>";
echo "<td>$month $day, $year</td>";
echo "<td></tr></td>";
}
echo"</table>";

?>

 

I am wanting to have all table headers sortable. So, when someone clicks any of the <th> items that column is sorted in ascending order first and subsequently descending order if it is clicked again (for the sake of time I'm looking to at least just start with ascending order).

 

Are there any ways to do this without having the page refresh each time? For the date, I'd like to sort the dates by month.

 

Thanks very much.

[Psycho]

If you don't want to refresh the page you will have to use JavaScript. There are some javascript only solutions that will allow you to sort a table.

http://www.kryogenix.org/code/browser/sorttable/

 

However, you could also use JavaScript + PHP (i.e. AJAX). This wouldn't be an optimal solution in my opinion because there can be a delay with AJAX, but the one benefit is that if the records changed since the user first loaded the page they would get the most up to date results when sorting the table.

[dpedroia]

Thanks.. I tried the sorttable package and it's technically 'working', however it is only outputting the table headers and not the table content that's been pulled from the database. I strictly followed the instructions, but I feel that because my code is broken up with the loop and if-statements between the table headers and table data cells this package is not going to work.

 

Is this evident from looking at my original code? Again, it's 'working' (i.e. I'm not implementing it incorrectly), and the clickable sort arrows do appear in the table headers, but the table content vanishes and is no longer visible.

 

Thank you again.

[Psycho]

The manner in which you create teh HTML (loops, if statements, etc) will have absolutely no bearing on whether the javascript will work or not.

 

If the javascript code works, which I assume it does since I could see it working on the page linked above, then there is something wrong in the output you are creating. Specifically, I would be looking at the structure of the table. Are all cells and row tags properly opened and closed?

 

EDIT: Yep, a quick look at your code shows that the tabletags are fugged up. This line makes no sense

echo "<td></tr></td>";

You don't seem to be using nested tables so there can't be a closing TD tag after a closing TR tag.

 

Try this:

$max_col = 100;
$query = "SELECT * FROM events";
$result = mysql_query($query) or die(mysql_error());
    
echo "<table>\n";
echo "<tr>\n";
echo "<th style=\"text-align:left\">Event ID</th>\n";
echo "<th style=\"text-align:left\">Event Name</th>\n";
echo "<th style=\"text-align:left\">Sport</th>\n";
echo "<th style=\"text-align:left\">State</th>\n";
echo "<th style=\"text-align:left\">Venue</th>\n";
echo "<th style=\"text-align:left\">Date</th>\n";
echo "</tr>\n";
    
$col = 0;
while($row = mysql_fetch_array($result))
{
    extract($row);
    $col++;
    
    //Open row if needed    
    if ($col%$max_col==1)
    {
        echo "<tr>\n";
    }
    
    echo "<td>{$id}</td>\n";
    echo "<td>{$eventname}</td>\n";
    echo "<td>{$sport}</td>\n";
    echo "<td>{$state}</td>\n";
    echo "<td>{$venue}</td>\n";
    echo "<td>{$month} {$day}, {$year}</td>\n";
    
    //Close row if needed    
    if ($col%$max_col==0)
    {
        echo "</tr>\n";
    }
}
//Close last row if not already done
if($col%$max_col!=0)
{
    echo "</tr>\n";
}
echo"</table>";

[dpedroia]

The manner in which you create teh HTML (loops, if statements, etc) will have absolutely no bearing on whether the javascript will work or not.

 

If the javascript code works, which I assume it does since I could see it working on the page linked above, then there is something wrong in the output you are creating. Specifically, I would be looking at the structure of the table. Are all cells and row tags properly opened and closed?

 

EDIT: Yep, a quick look at your code shows that the tabletags are fugged up. This line makes no sense

echo "<td></tr></td>";

You don't seem to be using nested tables so there can't be a closing TD tag after a closing TR tag.

 

Try this:

$max_col = 100;
$query = "SELECT * FROM events";
$result = mysql_query($query) or die(mysql_error());
    
echo "<table>\n";
echo "<tr>\n";
echo "<th style=\"text-align:left\">Event ID</th>\n";
echo "<th style=\"text-align:left\">Event Name</th>\n";
echo "<th style=\"text-align:left\">Sport</th>\n";
echo "<th style=\"text-align:left\">State</th>\n";
echo "<th style=\"text-align:left\">Venue</th>\n";
echo "<th style=\"text-align:left\">Date</th>\n";
echo "</tr>\n";
    
$col = 0;
while($row = mysql_fetch_array($result))
{
    extract($row);
    $col++;
    
    //Open row if needed    
    if ($col%$max_col==1)
    {
        echo "<tr>\n";
    }
    
    echo "<td>{$id}</td>\n";
    echo "<td>{$eventname}</td>\n";
    echo "<td>{$sport}</td>\n";
    echo "<td>{$state}</td>\n";
    echo "<td>{$venue}</td>\n";
    echo "<td>{$month} {$day}, {$year}</td>\n";
    
    //Close row if needed    
    if ($col%$max_col==0)
    {
        echo "</tr>\n";
    }
}
//Close last row if not already done
if($col%$max_col!=0)
{
    echo "</tr>\n";
}
echo"</table>";

 

Ahh, relief. I took that code and modified it a little bit to the following:

 

<?php

$max_col = 100;
$query = "SELECT * FROM events";
$result = mysql_query($query) or die(mysql_error());
    
echo "<table class='sortable tableFormat'>\n";
echo "<tr>\n";
echo "<th style=\"text-align:left\">Event ID</th>\n";
echo "<th style=\"text-align:left\">Event Name</th>\n";
echo "<th style=\"text-align:left\">Sport</th>\n";
echo "<th style=\"text-align:left\">State</th>\n";
echo "<th style=\"text-align:left\">Venue</th>\n";
echo "<th style=\"text-align:left\">Date</th>\n";
echo "</tr>\n";

$col = 0;
while($row = mysql_fetch_array($result))
{
    extract($row);
    $col++;
    
    //Open row if needed    
    //if ($col%$max_col==1)
    //{
    //    echo "<tr>\n";
    //}
    
    echo "<tr>\n";
    echo "<td>$id</td>\n";
    echo "<td>$eventname</td>\n";
    echo "<td>$sport</td>\n";
    echo "<td>$state</td>\n";
    echo "<td>$venue</td>\n";
    echo "<td>$month $day, $year</td>\n";
echo "</tr>\n";
    
    //Close row if needed    
    //if ($col%$max_col==0)
    //{
    //    echo "</tr>\n";
    //}
}
//Close last row if not already done
//if($col%$max_col!=0)
//{
//    echo "</tr>\n";
//}
echo"</table>";

?>

 

Works like a charm. Thanks much for the heads up on the pointless and incorrect code.

 

Brian