[SOLVED] Sorting Columns on click after query?

[BobcatM]

Thanks for looking.

 

I am trying to sort a column based on what the user clicks. (What heading they choose - Job #, Location, Date, Invoiced, so on..)

 

This is my code I am using right now to display the results:

 

<form>
                                              <input type="button" value="Back" onClick="window.location.href='index.php'">
<?

$username="?????";
$password="????????";
$database="cs_jobs";


mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM jobs ORDER BY jobnum DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);

mysql_close();


?>
                                            </form>
                                            
                                            
                                            
                                          <table width="975" border="1" cellspacing="1" cellpadding="1">
                                            <tr>
                                                <th width="67" nowrap bgcolor="#FF0000"><div align="center"><span class="style1"><font face="Arial, Helvetica, sans-serif">[b]<a href="viewjobs2.php?col=job_num">[/b]Job #</a></font></span></div></th>
                                              <th width="61" bgcolor="#FF0000"><div align="center"><span class="style1"><font face="Arial, Helvetica, sans-serif">Date</font></span></div></th>
                                              <th bgcolor="#FF0000"><div align="center"><span class="style1"><font face="Arial, Helvetica, sans-serif">Description</font></span></div></th>
                                              <th width="53" bgcolor="#FF0000"><div align="center"><span class="style1"><font face="Arial, Helvetica, sans-serif">PO #</font></span></div></th>
                                              <th width="64" bgcolor="#FF0000"><div align="center"><span class="style1"><font face="Arial, Helvetica, sans-serif">Status </font></span></div></th>
                                              <th width="100" bgcolor="#FF0000"><div align="center"><span class="style1"><font face="Arial, Helvetica, sans-serif">Comment</font></span></div></th>
                                              <th width="91" nowrap bgcolor="#FF0000"><div align="center"><span class="style3">Equip #</span></div></th>
                                              <th width="76" bgcolor="#FF0000"><div align="center"><span class="style1"><font face="Arial, Helvetica, sans-serif">Invoiced</font></span></div></th>
                                              <th width="84" bgcolor="#FF0000"><div align="center"><span class="style1"><font face="Arial, Helvetica, sans-serif">Division</font></span></div></th>
                                              <th width="65" bgcolor="#FF0000"><div align="center"><span class="style1"><font face="Arial, Helvetica, sans-serif">Name</font></span></div></th>
                                            </tr>
                                              <?
$i=0;
while ($i < $num) {

$jobnum=mysql_result($result,$i,"jobnum");
$location=mysql_result($result,$i,"location");
$description=mysql_result($result,$i,"description");
$ponum=mysql_result($result,$i,"ponum");
$status=mysql_result($result,$i,"status");
$date=mysql_result($result,$i,"date");
$comments=mysql_result($result,$i,"comments");
$equipnum=mysql_result($result,$i,"equipnum");
$invoiced=mysql_result($result,$i,"invoiced");
$division=mysql_result($result,$i,"division");
$name=mysql_result($result,$i,"name");
?>
                                              <tr>
                                                <td nowrap bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $location, $jobnum; ?></font></div></td>
                                                <td nowrap bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $date; ?></font></div></td>
                                                <td bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $description; ?></font></div></td>
                                                <td nowrap bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $ponum; ?></font></div></td>
                                                <td bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $status; ?></font></div></td>
                                                <td bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $comments; ?></font></div></td>
                                                <td bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $equipnum; ?></font></div></td>
                                                <td nowrap bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $invoiced; ?></font></div></td>
                                                <td nowrap bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $division; ?></font></div></td>
                                                <td nowrap bordercolor="#FF0000"><div align="center"><font face="Arial, Helvetica, sans-serif"><? echo $name; ?></font></div></td>
                                              </tr>
                                              <?


++$i;
} 

?>

 

<a href="viewjobs2.php?col=job_num">

 

I added this on the first column, but I keep getting an error.

 

Any help on how to do this would be much appriciated.

 

[Xil3]

What's the error message that you're getting?

 

I also noticed that you're not doing anything with the 'col' value that you're sending...

 

If you want to sort them based on the column title that they click on, then you may want to add this bit near the top:

 

.......
$database="cs_jobs";

$col = (isset($_GET['col']) ? $_GET['col'] : 'jobnum');

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM jobs ORDER BY $col DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
.......

 

Notice the change I made in your ORDER BY...

[BobcatM]

Xil3 - You are my hero.

 

 

That worked like dream.

 

 

Another quick question, is there a way to be able to choose if you want to sort it ASC or DESC on the same link?

 

And this code you gave me:

 

$col = (isset($_GET['col']) ? $_GET['col'] : 'jobnum');

 

What does ?

$_GET['col'] : 'jobnum')

do?

 

Thanks a million.

 

[trq]

?: is the ternary operator in php. This...

 

<?php $col = (isset($_GET['col']) ? $_GET['col'] : 'jobnum'); ?>

 

is equivelent to....

 

<?php
if (isset($_GET['col'])) {
  $col = $_GET['col'];
} else {
  $col = 'jobnum';
}
?>

[BobcatM]

Thank you both for the time you spend replying to me.

 

 

Got it all working. Very much appreciated.

 

--Bobcat