Sorting My Database

[tomyknoker]

Ok I have apage which displays exactly what I want it too the code is as follows
<?php
$cat = $_GET['cat'];

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';

//navigation
include("nav.php");


//approved
$info = mysql_query("SELECT * FROM `tblmembers` WHERE `MemberApproved`='$cat'");

echo '<table border="1" cellpadding="3" cellspacing="1">
<tr valign="top">
<td>ID</td>
<td>First Name</td>
<td>Last Name</td>
<td>State</td>
<td>Application Date</td>
<td>Last Login</td>
</tr>';

if (mysql_num_rows($info) < 1) {
echo '<tr valign="top">
<td colspan="6">There are no members that match the query. Please go back and try again</td>
</tr>';
}

else {
while ($qry = mysql_fetch_array($info)) {

//create the layout
?>
<link href="cs_style.css" rel="stylesheet" type="text/css" />

<tr valign="top">
<td><?php echo $qry['ID']; ?></td>
<td><?php echo $qry['FirstName']; ?></td>
<td><?php echo $qry['LastName']; ?></td>
<td><?php echo $qry['State']; ?></td>
<td><?php echo date('d/m/Y', strtotime($qry['JoinDate'])); ?></td>
<td><?php echo($qry['loginDateTime']?date('d/m/Y H:i:s', strtotime($qry['loginDateTime'])):'N/A') ?></td>
</tr>
<?php
}
}

echo '</table>';
?> I have been also able to build (with a little help:)) a page which just displays all my tblmembers seperately, the beauty of this page is the headings are automatically populated and I can click them to sort by FirstName, LastName etc... Here is the code for that page
<link href="cs_style.css" rel="stylesheet" type="text/css" />
<?php

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';

/* set the allowed order by columns */
$default_sort = 'LastName';
$allowed_order = array ('JoinDate', 'FirstName','LastName');

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise,
* set it to what was passed in. */
if (!isset ($_GET['order']) ||
!in_array ($_GET['order'], $allowed_order)) {
$order = $default_sort;
} else {
$order = $_GET['order'];
}

/* construct and run our query */
$query = "SELECT * FROM tblmembers ORDER BY $order";
$result = mysql_query ($query);

/* make sure data was retrieved */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
echo "No data to display!";
exit;
}

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
/* check if the heading is in our allowed_order
* array. If it is, hyperlink it so that we can
* order by this column */
echo "<TD><b>";
if (in_array ($heading, $allowed_order)) {
echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
} else {
echo $heading;
}
echo "</b></TD>\n";
}
echo "</TR>\n";

/* reset the $result set back to the first row and
* display the data */
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
echo "<TR>\n";
foreach ($row as $column) {
echo "<TD>$column</TD>\n";
}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>If anyone at all could help me make the first lot of code do what the second does, but keeping all the info I need in the first bit of code would be great! I'm a newbie I've come this far but am hoping for any help! Thanks all in advance!

[thetestingsite]

Try something like the following.



<?php
$cat = $_GET['cat'];

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';

//navigation
include("nav.php");


/* set the allowed order by columns */

$default_sort = 'LastName';
$allowed_order = array ('JoinDate', 'FirstName','LastName');

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise,
* set it to what was passed in. */
if (!isset ($_GET['order']) ||
!in_array ($_GET['order'], $allowed_order)) {
$order = $default_sort;
} else {
$order = $_GET['order'];
}

/* construct and run our query */
$query = "SELECT * FROM tblmembers WHERE `MemberApproved`='$cat' ORDER BY $order";

$result = mysql_query ($query);

/* make sure data was retrieved */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
echo "No data to display!";
exit;
}

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
/* check if the heading is in our allowed_order
* array. If it is, hyperlink it so that we can
* order by this column */
echo "<TD><b>";
if (in_array ($heading, $allowed_order)) {
echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
} else {
echo $heading;
}
echo "</b></TD>\n";
}
echo "</TR>\n";

/* reset the $result set back to the first row and
* display the data */
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
echo "<TR>\n";
foreach ($row as $column) {
echo "<TD>$column</TD>\n";
}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>


That's basically it. Hope this helps.

[tomyknoker]

Yay well that worked! Is it possible to only make it show the FirstName, LastName, State, Application Date, LastLogin...

[tomyknoker]

Oh just clicked on any of the sort fields when I do that it reloads the page with only 2 members...

[tomyknoker]

I looked through all the code but can't see why it would only show 2 members when I click on the sort function...

[thetestingsite]

Ok, try this (notice the part in red that I added (or forgot to add before):



<?php
$cat = $_GET['cat'];

/* connect to the mysql database and use a query to get the members info */

include 'library/config.php';
include 'library/opendb.php';

//navigation
include("nav.php");


/* set the allowed order by columns */

$default_sort = 'LastName';
$allowed_order = array ('JoinDate', 'FirstName','LastName');

/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise,
* set it to what was passed in. */
if (!isset ($_GET['order']) ||
!in_array ($_GET['order'], $allowed_order)) {
$order = $default_sort;
} else {
$order = $_GET['order'];
}

/* construct and run our query */
$query = "SELECT * FROM tblmembers WHERE `MemberApproved`='$cat' ORDER BY $order";

$result = mysql_query ($query);

/* make sure data was retrieved */
$numrows = mysql_num_rows($result);
if ($numrows == 0) {
echo "No data to display!";
exit;
}

/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
/* check if the heading is in our allowed_order
* array. If it is, hyperlink it so that we can
* order by this column */
echo "<TD><b>";
if (in_array ($heading, $allowed_order)) {
echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading&cat=$cat\">$heading</a>";
} else {
echo $heading;
}
echo "</b></TD>\n";
}
echo "</TR>\n";

/* reset the $result set back to the first row and
* display the data */
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
echo "<TR>\n";
foreach ($row as $column) {
echo "<TD>$column</TD>\n";
}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>


Hope this helps.

[tomyknoker]

Ok yes that worked! I noticed though, for some reason I thought the sort had a DESC as well as ASC, so I could sort the reverse... And also how can I remove some of the fields, it shows all the columns of tblemembers...

[tomyknoker]

You're gonna hate me! But it also lost the way I had the date displayed, can I get that back?
echo date('d/m/Y', strtotime($qry['JoinDate']));
echo($qry['loginDateTime']?date('d/m/Y H:i:s', strtotime($qry['loginDateTime'])):'N/A')