I need to pull out information from 2 tables in a mysql database, and then be able to sort by specific fields. I know how to do this if I only do a SELECT in one table, and therefore I thought it might be easier to do it with an array, where all the info is joined.
The code I use:
<?php
include("includes/template.top.php");
/ Setting the default ORDER BY field /
/
Added note for the viewers:
As the code below here shows, the three (3) fields I would like to be 'sortable' is:
groupPrefix, groupName and groupCount
Further down the HTML Anchor that sorts is written.
/
if ($field != "groupPrefix" && $field != "groupCount") {
$field = "groupName";
}
/ Setting the default sorting direction, and reversing when set /
if (!$sort) {
$sort = "ASC";
}
elseif ($sort == "DESC") {
$sort = "ASC";
}
elseif ($sort == "ASC") {
$sort = "DESC";
}
/ Querying the Groups table in the database /
$qGroups = mysql_query("SELECT FROM Groups ORDER BY groupId ASC");
/ Fetching and processing the result /
while ($r = mysql_fetch_array($qGroups)) {
/ Querying the GroupMembers table for a total number of members of the current group /
$groupCount = mysql_result(mysql_query("SELECT COUNT() FROM GroupMembers WHERE groupId=".$r["groupId"]),0);
/ Joining the info in the array /
/ Added note for the viewers: should I drop the, (in my limited mysql knowledge terms), auto-incremented primary key of the array, and use groupId as primary key instead? /
$groupArray[] = array(
"groupId" => $r["groupId"],
"groupName" => $r["groupName"],
"groupPrefix" => $r["groupPrefix"],
"groupCreated" => $r["groupCreated"],
"groupCount" => $groupCount
);
}
/ Writing the header /
/ Added note for the viewers: Here is the three (3) HTML Anchors that sorts the array /
echo "
<table>
<tr>
<th><a href=\"".$PHP_SELF."?field=groupPrefix&order=".$order."\" title=\"Order by group prefix, ".$order."ENDING\">Group Prefix</a></th>
<th><a href=\"".$PHP_SELF."?field=groupName&order=".$order."\" title=\"Order by group name, ".$order."ENDING\">Group Name</a></th>
<th><a href=\"".$PHP_SELF."?field=groupCount&order=".$order."\" title=\"Order by # of members, ".$order."ENDING\"># of members</a></th>
</tr>
";
/ Looping out the groups /
/ Added note for the viewers: should I drop the, (in my limited mysql knowledge terms), auto-incremented primary key of the array ($i), and use groupId as primary key instead? /
for ($i=0;$i<count($groupArray);$i++) {
echo "
<tr>
<td>".$groupArray[$i]["groupPrefix"]."</td>
<td><a href=\"groupinfo.php?groupId=".$groupArray[$i]["groupId"]."\" title=\"View more info about ".$groupArray[$i]["groupName"]."\">".$groupArray[$i]["groupName"]."</a></td>
<td title=\"".$groupArray[$i]["groupCount"]." members since \"".date("d.m.Y H:i:s",$groupArray[$i]["groupCreated"]).">".$groupArray[$i]["groupCount"]."</td>
</tr>
";
}
echo "</table>";
include("includes/template.top.php"):
?>
I hope someone is able to help me.
Best regards
Brian Schmidt
P.S. Sorry for the bad grammar 😉
