Solved

Looping through Database Data with PHP

Posted on 2009-05-06
5
195 Views
Last Modified: 2012-05-06
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
Comment
Question by:vcbertini
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:Mathias
Comment Utility
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
 
LVL 34

Expert Comment

by:Beverley Portlock
Comment Utility
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
 
LVL 3

Accepted Solution

by:
Mathias earned 125 total points
Comment Utility
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
 

Author Comment

by:vcbertini
Comment Utility
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
 

Author Comment

by:vcbertini
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now