Insert section headings - but only call 1 query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Insert section headings - but only call 1 query

Post by cdickson »

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 »

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 »

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 »

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}
Post Reply