Solved

Looping through Database Data with PHP

Posted on 2009-05-06
5
199 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to count occurrences of each item in an array.
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 …

710 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