Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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.
0
vcbertini
Asked:
vcbertini
  • 2
  • 2
1 Solution
 
MathiasCommented:
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".
0
 
Beverley PortlockCommented:
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

0
 
MathiasCommented:
Here is an example code how to do it.
<?php
$db_res1 = categories;  // select from test1
$db_res2 = courses/class name;  // select from test2
 
$cats = array();
$classes = array();
 
while ($info1 = mysql_fetch_array($db_res1))
{
	$cats[] = array($info1['id'], $info1['name']);
}
 
while ($info2 = mysql_fetch_array($db_res2))
{
	$classes[] = array($info2['id'], $info2['cat'], $info2['name']);
}
 
for ($i=0; $i<count($cats); $i++)
{
	echo $cats[$i][1];
	
	for ($j=0; $j<count($classes); $j++)
	{
	  // cat from classes = id from categoriess
	  if ($classes[$j][1] == $cats[$i][0])
	  {
	    echo $classes[$j][2];
		}
	}
}
?>

Open in new window

0
 
vcbertiniAuthor Commented:
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

0
 
vcbertiniAuthor Commented:
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!
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now