[RESOLVED] strange sorting results with php/mysql script

[chrisdee]

Hello. I have made a php script that prints out the content of an mysql table. In addition I have been able to make the script sort the content of the table by clicking on the table column titles.

However I think the sorting seems strange. Some of the content in the table is stripped/removed away when I click/sort on the different columns, and the table width changes. This also seems to happend in phpmyadmin when I sort the table there.

I wonder if someone could look at my script and see if it looks ok.
I wonder if it's my script that's wrong, or maby it is the mysql table itself ?

<?php           echo "<a href=./>Home</a>";
// mysql connect
mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("pdb");

// variables
$sort   = $_GET['sort'];
if(empty($sort)) { $sort = "id"; }

// query table pdb
$query  = mysql_query("SELECT * FROM pdb ORDER BY $sort ASC");
$keys   = mysql_fetch_array($query, MYSQL_ASSOC);

// html table
echo "<table border=1>";

// html table column keys
echo "<tr>";
foreach(array_keys($keys) as $key) {
        if($sort==$key) {
                echo "<td bgcolor=lightgreen><a href=" . $_SERVER['PHP_SELF'] . "?sort=" . $key . ">" . $key . "</a></td>";
        } else {
                echo "<td bgcolor=lightblue><a href=" . $_SERVER['PHP_SELF'] . "?sort=" . $key . ">" . $key . "</a></td>";
        }
}
echo "</tr>";

// html table content
while ($rows = mysql_fetch_array($query, MYSQL_ASSOC)) {
        echo "<tr>";
        foreach($rows as $row) {
                echo "<td>" . $row . "</td>";
        }
        echo "</tr>";
}
echo "</table>";

// close mysql connection
mysql_free_result($query);
?> 

[chrisdee]

Nevermind, I found the solution. It was my script that was wrong. I had to create a mysql query both for listing the array keys and content. The corrected script is below and is now working🙂.

<?php           echo "<a href=./>Home</a>";
// mysql connect
mysql_connect("localhost", "user", "pass") or die(mysql_error());
mysql_select_db("pdb");

// variables
$sort   = $_GET['sort'];
if(empty($sort)) { $sort = "id"; }

// query table pdb
$query_key      = mysql_query("SELECT * FROM pdb ORDER BY $sort");
$query_content  = mysql_query("SELECT * FROM pdb ORDER BY $sort");
$keys           = mysql_fetch_array($query_key, MYSQL_ASSOC);

// html table
echo "<table border=1>";

// html table column keys
echo "<tr>";
foreach(array_keys($keys) as $key) {
        if($sort==$key) {
                echo "<td bgcolor=lightgreen><a href=" . $_SERVER['PHP_SELF'] . "?sort=" . $key . ">" . $key . "</a></td>";
        } else {
                echo "<td bgcolor=lightblue><a href=" . $_SERVER['PHP_SELF'] . "?sort=" . $key . ">" . $key . "</a></td>";
        }
}
echo "</tr>";

// html table content
while ($rows = mysql_fetch_array($query_content, MYSQL_ASSOC)) {
        echo "<tr>";
        foreach($rows as $row) {
                echo "<td>" . $row . " ";
        }
        echo "</tr>";
}
// end html table
echo "</table>";

// close mysql connection
mysql_free_result($query_key);
mysql_free_result($query_content);
?>

[Derokorian]

You could also have chosen to instead use do { } while(); syntax so that you still only had one query:

<?php           echo "<a href=./>Home</a>"; 
// mysql connect 
mysql_connect("localhost", "user", "pass") or die(mysql_error()); 
mysql_select_db("pdb"); 

// variables 
$sort   = $_GET['sort']; 
if(empty($sort)) { $sort = "id"; } 

// query table pdb 
$query  = mysql_query("SELECT * FROM pdb ORDER BY $sort ASC"); 
$rows   = mysql_fetch_array($query, MYSQL_ASSOC); 

// html table 
echo "<table border=1>"; 

// html table column keys 
echo "<tr>"; 
foreach(array_keys($rows) as $key) { 
        if($sort==$key) { 
                echo "<td bgcolor=lightgreen><a href=" . $_SERVER['PHP_SELF'] . "?sort=" . $key . ">" . $key . "</a></td>"; 
        } else { 
                echo "<td bgcolor=lightblue><a href=" . $_SERVER['PHP_SELF'] . "?sort=" . $key . ">" . $key . "</a></td>"; 
        } 
} 
echo "</tr>"; 

// html table content 
do { 
        echo "<tr>"; 
        foreach($rows as $row) { 
                echo "<td>" . $row . "</td>"; 
        } 
        echo "</tr>"; 
} while ($rows = mysql_fetch_array($query, MYSQL_ASSOC));
echo "</table>"; 

// close mysql connection 
mysql_free_result($query); 
?>