asked on

Looping through Database Data with PHP

I have some training course data set up in a database. Each class belongs to a category - for example:

CATEGORY NAME
Blue Training Class Name #1
Red Training Class Name #2
Green Training Class Name #3
Yellow Training Class Name #4
Red Training Class Name #5
Red Training Class Name #6

I am reading the data into my page using a while loop. The expected results is that there are four sections on the page and that the classes will appear under each section as such:

BLUE
--------------------
Training Class #1

RED
--------------------
Training Class #2
Training Class #5
Training Class #6

GREEN
--------------------
Training Class #3

YELLOW
--------------------
Training Class #4

Problem is, my page is only showing the first section (BLUE) and it's class listing, then the next three headers are showing up with nothing listed under them. What I think is happening is that the loop is only going through the data once and then stopping. Help! This page is really problematic and I have to get it resolved. The page problem is actually live and can be found here: http://www.dc-ei.com/Training.php

I am VERY new to PHP, so any suggestions would be really appreciated.

Mathias

Please post your loop code here and the database structure.

For Example you nee two tables, one for the color codes and one for the classes.
CREATE TABLE `test1`.`test1` (
`id` INT NOT NULL AUTO_INCREMENT ,
`cat` VARCHAR( 10 ) NOT NULL ,
`name` VARCHAR( 100 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;

CREATE TABLE `test1`.`test2` (
`id` INT NOT NULL AUTO_INCREMENT ,
`cat` INT NOT NULL ,
`name` VARCHAR( 100 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;

The second table, field "cat", points to the first table, field "id".

Beverley Portlock

You'll need to post some example code and preferably any table definitions to get a really accurate answer, but in essence from what you have posted I would assume something like this would work

$rs = mysql_query("select category, name from myTableName order by category, name");
 
// if the query ran then process results
//
if ( $rs ) {
 
     // Read the query result set one line at a time
     //
     $lastCategory = "";
 
     while ( $rw = mysql_fetch_assoc( $rs ) ) {
 
          // When the category changes print it out
          //
          if ( $rw['category'] != $lastCategory ) {
               echo $rw['category'] . "<br/>";
               echo "---------------<br/>";
          }
          
          echo $rw['name'] . "<br/>";
 
          $lastCategory = $rw['category'];
     }
 
 
}

Open in new window

ASKER CERTIFIED SOLUTION

Mathias

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vcbertini

ASKER

bportlock, that is similar to the code I had before and it would only run through the loop once.

TDS, I reworked my code to your example and it yielded the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dcei00/public_html/Training2.php on line 93

My code is attached.

<?php	
$sql = "SELECT A.ACTIVE, A.COURSE_ID, A.COURSE_NAME, A.COURSE_LENGTH, A.COURSE_COST, C.TYPE_ID " .
		"FROM COURSES A, COURSES_TYPES B, TYPES C " .
		"WHERE A.COURSE_ID=B.COURSE_ID " .
		"AND B.TYPE_ID = C.TYPE_ID " .
		"ORDER BY C.TYPE_ID;"; 
// NEED TO FILTER FOR ACTIVE COURSES HERE		
$courseResults = mysql_query($sql)
	or die(mysql_error());
 
$sql2 = "SELECT TYPE_ID, COURSE_TYPE, TYPE_DESC FROM TYPES ORDER BY TYPE_ID;"; 
$results2 = mysql_query($sql2)
	or die(mysql_error());
 
$cats = array();
$classes = array();
		 
while ($info1 = mysql_fetch_array($catResults))
{
	$cats[] = array($info1['TYPE_ID'], $info1['COURSE_TYPE'], $info1['TYPE_DESC']);
}
		 
while ($info2 = mysql_fetch_array($courseResults))
{
	 $classes[] = array($info2['COURSE_ID'], $info2['COURSE_NAME'], $info2['COURSE_LENGTH'], $info2['COURSE_COST'], $info2['TYPE_ID'], $info2['ACTIVE']);
}
	 
for ($i=0; $i<count($cats); $i++)
{
	echo '<p class="titleLine"><a name="';
        echo $cats[$i][1];
	echo '"></a>';
	echo $cats[$i][1];
	echo '</p><p>';	
	echo $cats[$i][2];
	echo '</p>';
	echo '<table width="90%" border="1" align="center" cellpadding="6" cellspacing="0">';
	echo '<tr align="left" valign="bottom">';
	echo '<td width="60%" bgcolor="#CCCCCC" class="smalltitle">Course Name<br />';
	echo '<span class="copyright">(click on the course title for a detailed course ';
	echo 'description)</span></td>';
	echo '<td width="10%" align="center" bgcolor="#CCCCCC" class="smalltitle">Length</td>';
	echo '<td width="10%" align="center" bgcolor="#CCCCCC" class="smalltitle">Cost</td>';
	echo '<td width="20%" align="center" nowrap bgcolor="#CCCCCC" class="smalltitle">';
	echo 'Scheduled Classes</td></tr>';        
	for ($j=0; $j<count($classes); $j++)
	{
	  // cat from classes = id from categoriess
	  if ($classes[$j][4] == $cats[$i][0])
	  {
		echo '<tr><td width="60%" nowrap>';
		echo '<a href="Courses.php?ID=';
		echo $classes[$j][0];
		echo '">';
		echo $classes[$j][1];
		echo '</a></td><td width="10%">';
		echo $classes[$j][2];
		if ($classes[$j][2] == 1 ) {
			echo ' day';
		} else {
			echo ' days';
		}
		echo '</td><td width="10%" align="right">';
		echo '$ ' . number_format($classes[$j][3], 0);
		echo '</td><td width="20%" nowrap align="center">';
		if ($classes[$j][5] == 0) {
			echo '<a href="TrainingSchedule.php">View Schedule</a>';
		} else {
			echo '<a href="ContactUs.php">Contact Us</a>';
		}
		echo '</td></tr>';			
	}
}
echo '</table>';		
}
?>

Open in new window

vcbertini

ASKER

NEVERMIND! NEVERMIND!! Ugh. I named my category results set wrong. Once I changed $results2 to $catResults, all worked fine. Thank you so much for your help!