Need Help Sorting Table Columns with Database Fields

Dave 5 · Joined: 08 May 2004 Posts: 108 Location: Seattle

If you look at my page at http://www.geoworld.org/reference/people/ you'll see a table with a PHP script that gives it sortable columns. Suppose I modified this table so that only the FIRST column was displayed. Could the PHP script be modified so that you could still choose "Population" and the table would be rearranged by population, even though the Population column isn't visible? In other words, I'd like to be able to sort by database table fields, whether those fields are displayed as columns on my page or not.

If so, how should I modify the script below? Thanks.

[code:1]
<?php
$mycode = 'peo';
include (DATABASE CONNECTION);
echo '<head>';
include ($_SERVER['DOCUMENT_ROOT']."/a1/inc/head/ref.php");
echo '</head>';
?>
<body>
<div class="formdiv">
<form action="index.php" method="GET">
<select name="order">
<option value="1">Country, etc.</option>
<option value="2">Population</option>
<option value="3">Nationality</option>
<option value="4">Nationality: Plural</option>
<option value="5">Nationality: Adjective</option>
</select>
<input type="radio" name="direction" value="0">
Ascending
<input type="radio" name="direction" value="1">
Descending
<input type="submit" name="submit" value="Submit">
</form>
</div>
<?php
$colors = array( '#eee', '', '#ff9', '', '#cff', '', '#cfc', '' );
$n=0;
$size=count($colors);

$result = mysql_query('select count(*) from cia_people');
if (($result) && (mysql_result ($result , 0) > 0)) {
// continue here with the code that starts
//$res = mysql_query ("SELECT * FROM type.....
} else {
die('Invalid query: ' . mysql_error());
}
{

$order = isset($_REQUEST['order']) ? intval($_REQUEST['order']) : 0;
switch($order)
{
case 1:
$order = 'Name';
break;
case 2:
$order = 'Pop';
break;
case 3:
$order = 'Nationality';
break;
case 4:
$order = 'NationalityPlural';
break;
case 5:
$order = 'NationalityAdjective';
break;
case 6:
default:
$order = 'Name';
break;
}
if (isset($_REQUEST['direction']) && intval($_REQUEST['direction'])) {
// if (isset($_REQUEST['direction']) && intval($_REQUEST['direction'])) {
$direction = ' DESC';
} else {
$direction = '';
}

$res = mysql_query('SELECT IDArea, Name, Pop, Nationality, NationalityPlural, NationalityAdjective
FROM cia_people as c
WHERE c.Nationality is not null
ORDER BY c.' . $order . $direction);

echo '<table class="sortphp" id="tab_cia_people_peo">
<thead>
<tr><th>Country</th><th class="gray">Population</th><th>Nationality</th>
<th class="gray">Nat. (Plural)</th><th>Nat. (Adjective)</th></tr>
</thead>
<tbody>';
//
$rowcounter=0;
while ($row = mysql_fetch_array ($res)) {
$c=$colors[$rowcounter++%$size];
echo "<tr style=\"background-color:$c\"><". $_SERVER['PHP_SELF'] .'?id='. $row['IDArea'] ."><td>". $row['Name'] ."</td>
<td>" . number_format($row['Pop']) . "</td>
<td>". $row['Nationality'] ."</td>
<td>". $row['NationalityPlural'] ."</td>
<td>". $row['NationalityAdjective'] ."</td></tr>\n";
}
}
?>
</tr>
</tbody>

</table>

</body>
</html>

?>
[/code:1]

papercrane · Joined: 16 Jul 2004 Posts: 637 Location: California, US

Just don't display the other columns.....I don't see why you're having a problem with this. Remove them from the SELECT and remove their TD entries.

Dave 5 · Joined: 08 May 2004 Posts: 108 Location: Seattle

[quote="papercrane"]Just don't display the other columns.....I don't see why you're having a problem with this. Remove them from the SELECT and remove their TD entries.[/quote]

OK, problem solved. I had tried your suggestion earlier without success, but I just discovered a stupid mistake I made.

Thanks for the tip!