Solved

Looping through Database Data with PHP

Posted on 2009-05-06
5
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:Mathias
ID: 24316157
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
ID: 24316236
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
ID: 24316237
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
ID: 24326258
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
ID: 24326468
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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

630 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