Page 1 of 1

Insert section headings - but only call 1 query

Posted: Mon May 23, 2005 11:23 am
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.

Posted: Mon May 23, 2005 11:50 am
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'];
}

Posted: Tue May 24, 2005 2:55 pm
by cdickson
Thanks timvw - I will try this!

Posted: Thu May 26, 2005 2:19 pm
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}