looping database results.

[teamfox201]

Hello everyone.

I am trying to get some data from 2 mysql tables and loop the results. I have a LEFT JOIN ON statement that works and when I loop table1 it works as well. I have table2 which contains subdata for table1 and can have multiple records tied to table1 .

I can't figure out how to loop table2 results with table1. Currently it creates a new html table for each table1 + table2 result.

I'm pretty new to this so I hope it makes sense.

Example (What's happening that I don't want.)

(Table1) Data
(Table2) Test

(Table1) Data
(Table2) Test2

(Table1) Hello
(Table2) Good

(Table1) Hello
(Table2) Bye

Example (What I want)

(Table1) Data
(Table2) Test
(Table2) Test2

(Table1) Hello
(Table2) Good
(Table2) Bye

[johanafm]

# keeps id from row in previous iteration to see if we have a new group of rows or
# if it's the same one
$last_id = false;

while (get_data())
{
	if ($last_id != $row['id'])
	{
		# There was a previous group of rows. End previous row
		if ($last_id)
		{
			echo '</div>';
		}
		# Start output for next group of rows
		printf('<div><h3>Row &#37;d</h3>', $row['id']); 

	# update last_id to keep current group of rows
	$last_id = $row['id']
}
# as before
}

[teamfox201]

Darn it I cant make it work.

This is the select statement I use but I cant figure out how to make it check if $last_id != $row_rs_get_goals['id']

mysql_select_db($database_mysql_dbConnect, $mysql_dbConnect);

$query_rs_get_goals = "SELECT goals.id, goals.goal_name, goals.goal_due_date, subgoals.subgoal_name, subgoals.goals_id, FROM goals LEFT JOIN subgoals ON goals.id = subgoals.goals_id";

$rs_get_goals = mysql_query($query_rs_get_goals, $mysql_dbConnect) or die(mysql_error());

$row_rs_get_goals = mysql_fetch_assoc($rs_get_goals);

$totalRows_rs_get_goals = mysql_num_rows($rs_get_goals);

[Derokorian]

You would do it in a while loop, as johanafm showed. Where is your loop to display the results? You didn't show one!

[teamfox201]

So i tried using the code below and it shows everything correctly besides the extra $row['subgoal_name'] if there is more than one record.

mysql_select_db($database_mysql_dbConnect, $mysql_dbConnect);
// Make a MySQL Connection

// Get all the data from the "example" table
$result = mysql_query("SELECT x_tbl_goals.id, x_tbl_goals.goal_name, x_tbl_subgoals.subgoal_name, x_tbl_subgoals.goals_id, x_tbl_subgoals.subgoal_description FROM x_tbl_goals LEFT JOIN x_tbl_subgoals ON x_tbl_goals.id = x_tbl_subgoals.goals_id") 
or die(mysql_error());  

$last_id = false;
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	if ($last_id != $row['id'])
	{
		if ($last_id)
		{
		}
	// Print out the contents of each row into a table
	echo "<table width=\"30%\" border=\"1\">
			<tr>
						<td>".$row['id']."</td>
						<td>".$row['goal_name']."</td>
			</tr>
			<tr>
						<td colspan=\"2\">".$row['subgoal_name']."</td>
			</tr>
</table>";
$last_id = $row['id'];
	}
} 

[Weedpacket]

The idea johanafm's code is illustrating is this:

1. Keep a track of what subgoal the previous record belonged to (for the first record this would be false)

2. If the previous record's subgoal is different from the current record's subgoal:

3. End the previous group, start a new one, and insert a heading the current subgoal

4. After all the records, end the last group.

5. Don't use tables unless you actually want to display tables.

http://www.phpbuilder.com/board/showthread.php?t=10380530
http://www.phpbuilder.com/board/showthread.php?t=10313350

[niroshan]

Hi teamfox201,

here`s the code to help you out,

$last_id = false;
$mainPrinted = false; 
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result  )) {

if ($last_id != $row['id'])
{
    if($mainPrinted)
	{
		echo '</table><br />';
		$mainPrinted = false; 
	}
	else
		$mainPrinted = true;

	echo "<table width=\"30%\" border=\"1\"><tr><td>".$row['id']."</td><td>".$row['goal_name']."</td></tr><tr><td colspan=\"2\"><b>Sub goals</b></td></tr>";
	echo "<tr><td colspan=\"2\"> - ".$row['subgoal_name']."</td></tr>";
}
else
	echo "<tr><td colspan=\"2\"> - ".$row['subgoal_name']."</td></tr>";

$last_id = $row['id'];	
} 

Ps. I just gave you the code because i felt your really stuck. Please check the code and understand it before pasting it to your file.

[teamfox201]

niroshan,

Thank you so much!