nested loop advice needed.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

nested loop advice needed.

Post by boo_lolly »

i have a mysql table with a few columns. some of the rows in this table might contain the same content in the same column. for instance. my column titles are 'uID' 'product' 'item' 'need' 'yah' 'blah' 'blah'... here's what i need i need some advice on how to design a loop (or set of loops) to output an HTML. i need to match all the rows in the table that have the same conent in the 'product' column. i do this by putting "ORDER BY product" in the sql command. the difficulty is involved in the display of the content. i want to write a loop that will output HTML tables. each HTML table will have a title that spans across the whole table. that title will be the product. then, display the rest of the information below it (still inside the HTML table). as soon as the mysql_fetch_array comes across a row in the sql table that has a new name for the 'product' column, end the HTML table, and start again. this time, display the NEXT product name as the title of the new HTML table. this loop will run until there are no more rows left in the mysql table. here's what i got so far...

Code: Select all

$row = mysql_query("SELECT * FROM ". $regID ."") or die(mysql_error());

	$num_rows = mysql_num_rows($row);

		echo "Total Number of Items in this registry: ". $num_rows ."";
		
		echo "<TABLE BORDER=1><TR><TH COLSPAN=7 ALIGN=left>Category: ". $row['category'] ."</TH></TR>";

// BEGIN SOME TYPE OF LOOP HERE

		echo "<TR><TH>Item</TH><TH>Quantity Requested</TH><TH>Still Needs</TH><TH>Price</TH><TH>View</TH><TH>Quantity</TH><TH>Buy</TH></TR>";

// EXECUTE THIS LOOP (or some loop like it)
		for($i=0; $i < $num_rows; $i++)
		{
			$row = mysql_fetch_array($row);
			echo "<TR><TD>". $row['item'] ."</TD><TD>". $row['qty_req'] ."</TD><TD>". $row['still_needs'] ."</TD><TD>VAR price</TD><TD>VAR link</TD><TD>Input Field</TD><TD>Add to Cart</TD></TR>";
		}

// UNTIL IT REACHES A ROW WITH A NEW NAME IN ITS 'product' COLUMN THEN END TABLE...
	
		echo "</TABLE><br /><br />";
	
//DO THE WHOLE THING OVER AGAIN (back up to first loop)

// WHEN THE WHOLE THING IS COMPLETELY DONE, CLOSE DATABASE CONNECTION...

		mysql_close($db);
any help?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Store the last value of 'product' in variable.
Compare the value of the current record with that variable.
If they differ, close the <table> tag and start a new one.

Code: Select all

$records = array(
		array('a', 'a0001'),
		array('a', 'a0003'),
		array('a', 'a0006'),
		array('a', 'a0010'),
		array('b', 'b0005'),
		array('b', 'b0006'),
		array('c', 'c0001')
	);
	
$last = null;
echo '<table border="1">';
foreach( $records as $row ) {
	if ( $row[0]!=$last ) {
		if ( !is_null($last) ) {
			echo '</table><table border="1">';
		}
		echo '<tr><th>', $row[0], '</th></tr>';
		$last = $row[0];
	}
	echo '<tr><td>', $row[1], '</td></tr>';
}
echo '</table>';
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post by boo_lolly »

that's nice code. but i don't think i explained my situation well enough. the amount of rows in the table will be changing all the time. i'm writing a CMS for it so the admin can add, edit, and delete each row 'on the fly'. so there's no telling how many rows there will be at any given time. i believe your code assumes the user will know how many rows there at any given time. i think all those arrays in the beginning of the code lead me to believe that is the case.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

i believe your code assumes the user will know how many rows there at any given time
Why do you believe that? Wich line(s) exactly give you that believe?


When i try to understand code that isn't clear at first sight i try to come up with one (or more) data input sets and step through the function till the end... This usually makes me understand what's going on...
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post by boo_lolly »

i did that, but that code looks so advanced! i've only been coding for 3 weeks! my code is a dead giveaway. i still didn't understand it. the arrays in the beginning are what really threw me off. i know what a nested array is, and i know how to use them. but indices in the arrays are confusing. i'm sure they are just examples, but my understanding of it is that it's supposed to do something like this for it to work for me.

Code: Select all

$indices = mysql_num_rows($result); 

	$i = range(0, $indices);

	$records = array([$i] = array($event_month, $event_day, $event_year, $ship_street, $ship_city, $ship_state, $ship_zip]));
as far as i know, that's what i believe the arrays are there to do. i'm probably wrong, but that will shed light on the misunderstanding. as for the loops afterward, i'm still working on them... upon further review i really think i didn't get the point across before. i need to write code to notice a change in similarity on every row for the column 'product'. that means that for the above code "records[$i][0]" will be the $product index. i need a for loop and then set $i = 0, then incriment $i++. begin open table tag at the beginning of this 'for' loop.


then i need another loop inside this for loop to echo that value ONCE, then echo HTML table rows of the rest of the row. continue until records[$i][0] notices that it was not exactly like the one before it, close the HTML table, and run the loop again (which starts with opening an HTML table). i figure this is done by incrimenting so you can stop the loop for good if($i < $indices). does that help?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

What does

Code: Select all

for($i=0; $i < $num_rows; $i++)
{
	$row = mysql_fetch_array($row);
	// ..
}
do? And what does

Code: Select all

foreach( $records as $row ) {

	// ..
}
do?
It's very similar and my foreach construct is only an example without a mysql query.
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Q|

Post by boo_lolly »

i know you're foreach is just an example. but i don't care what the last row in the SQL table is. i care about ORDER BY the column 'category', then looping through each row until it gets to a new product in the 'product' column, in which case it will close the HTML table, and start a new HTML table... for example... my SQL table looks like this:

Code: Select all

+--------------+------+-------------+------------------+ 
|   CATEGORY   | ITEM |   QTY_REQ   |    STILL_NEEDS   |
+--------------+------+-------------+------------------+
|   kitchen    |  pan |      5      |        2         |
+--------------+------+-------------+------------------+
|   drapes     | sheet|     2       |         1        |
+--------------+------+-------------+------------------+
|  kitchen     |glass |      4      |          2       |
+--------------+------+-------------+------------------+
|   office     | pens |      3      |         1        |
+--------------+------+-------------+------------------+
i need to write a loop to output an HTML table that looks like this...

Code: Select all

+-------------------------------------------------------+ 
| CATEGORY: drapes                                      |
+-------------------------------------------------------+ 
|      ITEM       |      QTY_REQ     |   STILL_NEEDS    |
+-----------------+------------------+------------------+
|    sheet        |        2         |        1         |
+-----------------+------------------+------------------+


+-------------------------------------------------------+ 
| CATEGORY: kitchen                                     |
+-------------------------------------------------------+ 
|      ITEM       |       QTY_REQ    |  STILL_NEEDS     |
+-----------------+------------------+------------------+
|     glass       |         4        |       2          |
+-----------------+------------------+------------------+
|     pan         |         5        |       2          |
+-----------------+------------------+------------------+


+-------------------------------------------------------+ 
| CATEGORY: office                                      |
+-------------------------------------------------------+ 
|     ITEM        |      QTY_REQ     |   STILL_NEEDS    |
+-----------------+------------------+------------------+
|     pens        |        3         |        1         |
+-----------------+------------------+------------------+

get it now?
Last edited by boo_lolly on Fri Nov 17, 2006 9:33 am, edited 1 time in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Yes, I got it. But you do not.
Your SELECT ... ORDER BY gives you a result set that is almost like my array $records.
My foreach and your for(..$i<$num_rows) fetch similar arrays/records.
You only have to adapt the example.
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post by boo_lolly »

you're right, i don't get it. what do i adapt and how? i understand how your foreach and my for loop works very similar... but where does the code recognize a change in the value of the 'category' column? i just don't see it. your code leads me to believe that it checks for the last row in the entire SQL table. can you explain??
volka wrote:Yes, I got it. But you do not.
Your SELECT ... ORDER BY gives you a result set that is almost like my array $records.
My foreach and your for(..$i<$num_rows) fetch similar arrays/records.
You only have to adapt the example.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Ok, let's start with

Code: Select all

<?php
$query = "SELECT
		CATEGORY, ITEM, QTY_REQ, STILL_NEEDS  
	FROM
		$regID
	ORDER BY
		CATEGORY";
$result = mysql_query($query) or die(mysql_error().': '.$query);

// iterating records
while ( false!==($row=mysql_fetch_array($row)) ) {
	// print some data
	echo 'cat: ', $row['CATEGORY'], ', item:', $row['ITEM'], "<br />\n";
}
?>
does that work and print something?
If it does, compare it to

Code: Select all

<?php
$result = array(
		array('CATEGORY'=>'drapes', 'ITEM'=>'sheet'),
		array('CATEGORY'=>'kitchen', 'ITEM'=>'pan'),
		array('CATEGORY'=>'kitchen', 'ITEM'=>'glass'),
		array('CATEGORY'=>'office ', 'ITEM'=>'pens')
	);

// iterating records
foreach($result as $row) {
	// print some data
	echo 'cat: ', $row['CATEGORY'], ', item:', $row['ITEM'], "<br />\n";
}
?>
should be similar.

and the next step:

Code: Select all

<?php
$result = array(
		array('CATEGORY'=>'drapes', 'ITEM'=>'sheet'),
		array('CATEGORY'=>'kitchen', 'ITEM'=>'pan'),
		array('CATEGORY'=>'kitchen', 'ITEM'=>'glass'),
		array('CATEGORY'=>'office ', 'ITEM'=>'pens')
	);

$last = null;
echo '<table border="1">';

// iterating the records
foreach( $result as $row ) { 
	/* if the category changed
		create a new table and store the new category */
	if ( $row['CATEGORY']!=$last ) {
		if ( !is_null($last) ) {
			echo '</table><table border="1">';
		}
		echo '<tr><th>', $row['CATEGORY'], '</th></tr>';
		$last = $row['CATEGORY'];
	}
	// print some data
	echo '<tr><td>', $row['ITEM'], '</td></tr>';
}
echo '</table>';
User avatar
boo_lolly
Forum Contributor
Posts: 154
Joined: Tue Nov 14, 2006 5:04 pm

Post by boo_lolly »

thank you so much for helping me bro. i really appreciate you taking the time to explain. unfortunately i got a parse error:

Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /../../../testreg/registry.php on line 39

Code: Select all

// iterating records
	while ( false!==($row=mysql_fetch_array($row)) ) {
      
	// print some data
      echo 'cat: ', $row['category'], ', item:', $row['item'], "<br />\n";  LINE 39
}
?>
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

No error in this code snippet, the cause must be located before.
Post Reply