Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
cdickson
Forum Contributor
Posts: 120 Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA
Post
by cdickson » Mon May 23, 2005 11:23 am
For the sake of efficiency I am trying to use 1 query, but would like to then organize my data by section. There are 8 categories of employees, and I would like to print a heading at the beginning of each section.
Thus far I can only get the heading to appear before
each record instead of before
each category .
Code: Select all
//define query
$query="SELECT * FROM staff ORDER BY Category, List_no ASC";
$result = mysql_query($query) or die("Error: " . mysql_error());
while($row = mysql_fetch_array($result)){
if ($row['Category'] == 1){
//print category 1 heading
echo("<table width=\"450\" cellspacing=\"3\" cellpadding=\"4\">
<tr bgcolor=\"#EEEEEE\" class=\"tablecelltitle\">
<td width=\"175\" height=\"25\">Owners</td>
<td width=\"200\" height=\"25\">Title</td>
<td width=\"75\" height=\"25\">Extension</td>
</tr>");
//print category 1 records
echo("<tr><td width=\"85\">");
if (empty($row["StaffPhoto"])){
echo ("Photo</td>");
} else {
echo("<img src=\"staff/{$row['StaffPhoto']}\" width=\"75\" height=\"110\"></td>");
}
echo("<td valign=\"top\" align=\"left\">");
echo($row['StaffName'] . "<br>" . $row['StaffTitle']);
echo("<td valign=\"top\" align=\"center\">");
echo($row['Extension']);
echo("</td></tr>");
}
elseif ($row['Category'] == 2){
//print category 2 heading
echo("<table width=\"450\" cellspacing=\"3\" cellpadding=\"4\">
<tr bgcolor=\"#EEEEEE\" class=\"tablecelltitle\">
<td width=\"175\" height=\"25\">Office</td>
<td width=\"200\" height=\"25\">Title</td>
<td width=\"75\" height=\"25\">Extension</td>
</tr>");
//print category 2 records
echo("<tr><td width=\"85\">");
if (empty($row["StaffPhoto"])){
echo ("Photo</td>");
} else {
echo("<img src=\"staff/{$row['StaffPhoto']}\" width=\"75\" height=\"110\"></td>");
}
echo("<td valign=\"top\" align=\"left\">");
echo($row['StaffName'] . "<br>" . $row['StaffTitle']);
echo("<td valign=\"top\" align=\"center\">");
echo($row['Extension']);
echo("</td></tr>");
}
}
echo("</table>");
mysql_free_result($result);
?>
I've tried numerous variations to get the desired results but thus far have been unsuccessful.
timvw
DevNet Master
Posts: 4897 Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium
Post
by timvw » Mon May 23, 2005 11:50 am
Code: Select all
$current_category = null;
..
while ($row = mysql_fech_assoc($result))
{
if ($current_category != $row['Category'])
{
echo "<p>New category.....</p>";
}
// do stuff with record
$current_category = $row['Category'];
}
cdickson
Forum Contributor
Posts: 120 Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA
Post
by cdickson » Tue May 24, 2005 2:55 pm
Thanks timvw - I will try this!
cdickson
Forum Contributor
Posts: 120 Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA
Post
by cdickson » Thu May 26, 2005 2:19 pm
I tried timvw's suggestion, but I'm just clueless enough where I haven't been able to apply the technique properly.
Here is code to show 3 of my 8 categories, but the sections aren't showing up properly. I'm getting Category 1 at the heading of all 3, and then categories 2 & 3 show up after that.
Code: Select all
//define query
$query="SELECT * FROM staff ORDER BY Category, List_no ASC";
$result = mysql_query($query) or die("Error: " . mysql_error());
$current_category = null;
while($row = mysql_fetch_array($result))
{
if ($current_category != $row['Category'])
{
//print category 1 heading
echo("<table width=\"450\" cellspacing=\"3\" cellpadding=\"4\">
<tr bgcolor=\"#EEEEEE\" class=\"tablecelltitle\">
<td width=\"175\" height=\"25\">Owners</td>
<td width=\"200\" height=\"25\">Title</td>
<td width=\"75\" height=\"25\">Extension</td>
</tr>");
}
//print category 1 records
echo("<tr><td width=\"85\">");
if (empty($row["StaffPhoto"])){
echo ("Photo</td>");
} else {
echo("<img src=\"http://tubbs.server303.com/staff/{$row['StaffPhoto']}\" width=\"75\" height=\"110\"></td>");
}
echo("<td valign=\"top\" align=\"left\">");
echo($row['StaffName'] . "<br>" . $row['StaffTitle']);
echo("<td valign=\"top\" align=\"center\">");
echo($row['Extension']);
echo("</td></tr>");
$current_category = $row['Category'];
}
if ($current_category != $row['Category'])
{
//print category 2 heading
echo("<table width=\"450\" cellspacing=\"3\" cellpadding=\"4\">
<tr bgcolor=\"#EEEEEE\" class=\"tablecelltitle\">
<td width=\"175\" height=\"25\">Office</td>
<td width=\"200\" height=\"25\">Title</td>
<td width=\"75\" height=\"25\">Extension</td>
</tr>");
}
//print category 2 records
echo("<tr><td width=\"85\">");
if (empty($row["StaffPhoto"])){
echo ("Photo</td>");
} else {
echo("<img src=\"http://tubbs.server303.com/staff/{$row['StaffPhoto']}\" width=\"75\" height=\"110\"></td>");
}
echo("<td valign=\"top\" align=\"left\">");
echo($row['StaffName'] . "<br>" . $row['StaffTitle']);
echo("<td valign=\"top\" align=\"center\">");
echo($row['Extension']);
echo("</td></tr>");
$current_category = $row['Category'];
if ($current_category != $row['Category'])
{
//print category 3 heading
echo("<table width=\"450\" cellspacing=\"3\" cellpadding=\"4\">
<tr bgcolor=\"#EEEEEE\" class=\"tablecelltitle\">
<td width=\"175\" height=\"25\">New Vehicles</td>
<td width=\"200\" height=\"25\">Title</td>
<td width=\"75\" height=\"25\">Extension</td>
</tr>");
}
//print category 3 records
echo("<tr><td width=\"85\">");
if (empty($row["StaffPhoto"])){
echo ("Photo</td>");
} else {
echo("<img src=\"http://tubbs.server303.com/staff/{$row['StaffPhoto']}\" width=\"75\" height=\"110\"></td>");
}
echo("<td valign=\"top\" align=\"left\">");
echo($row['StaffName'] . "<br>" . $row['StaffTitle']);
echo("<td valign=\"top\" align=\"center\">");
echo($row['Extension']);
echo("</td></tr>");
$current_category = $row['Category'];
echo("</table>");
mysql_free_result($result);
Output is:
Category 1 Heading
Cat1 - Record 1
Cat1 - Record 2
Category 1 Heading
Cat2 - Record 1
Cat2 - Record 2
Category 1 Heading
Cat3 - Record 1
Cat3 - Record 2
Category 2 Heading
{no records}
Category 3 Heading
{no records}