Mysql problem with table printing (a bit complicated)

[jtakkinen_82]

Hi guys

 

I have a bit of a problem... I'm trying to gather data from 2 different tables and output them via a html table. I'm using PHP and MySQL. The problem I have is that I want to get the "team name" field only once but there can be lots of records under the same team. I mean, I want to get the team name only once so I can use it as a header for each table to separate different teams' results from each other, and then print the records for that team underneath it. This is the query I'm using now, it works fine but it gathers the team_name on every row... I originally used multiple while loops inside each other but that seemed like a sloppy way to do it.

 

SELECT teams.id, teams.name, vika_tk.ID, vika_tk.name, vika_tk.description, vika_tk.state, vika_tk.time, vika_tk.vmodel, vika_tk.teamID, vika_tk.projectiID

FROM teams, vika_tk

WHERE vika_tk.projectiID = '$projectiID' AND teams.id = vika_tk.teamID AND vika_tk.vmodel LIKE \"%$phase%\"

ORDER BY teams.id LIMIT $offset, $rowsPerPage";

 

I'm getting this kinda results:

 

team.ID | team.name | vika_tk.ID | vika_tk.name | vika_tk.desc | vika_tk.state | vika_tk.time | etc

 

But like I tried to explain earlier, I need the team.ID and team.name listed only once unless it changes, if you know what I mean... is it even possible to detect the change or do I have to do the detection in the php code somehow?

 

Now the resulting html table looks like this (simplified):

 

1 | Team name 1 | 2 | programming failure | descr. of programming failure | open | 3rd March 20:00 | etc

1 | Team name 1 | 4 | testing failure | descr. of testing failure | closed | 5rd March 20:00 | etc

3 | Team name 3 | 7 | assembling failure | descr. of assembling failure | open | 1rd March 20:00 | etc

 

And I want it like this:

 

-------------------------------------------------------------------------------------

                              Team name 1:                                                                    |

-------------------------------------------------------------------------------------

All team 1's records here...                                                                                  |

-------------------------------------------------------------------------------------

 

-------------------------------------------------------------------------------------

                              Team name 3 (or whatever number:                                        |

-------------------------------------------------------------------------------------

All team 3's records here...                                                                                  |

-------------------------------------------------------------------------------------

 

etc... Can this be done?

 

[jtakkinen_82]

Anyone? Or is the multiple while loop best way to solve this problem? I mean, first loop the through the teams table, then loop for teams records every round and so on... the problem with that was that I couldn't get the paging to work with so many different queries.

[mwasif]

Anyone? Or is the multiple while loop best way to solve this problem? I mean, first loop the through the teams table, then loop for teams records every round and so on... the problem with that was that I couldn't get the paging to work with so many different queries.

Use a single query and detect the change within the loop e.g.

 

$current_team = "";
while($rs = mysql_fetch_array($result))
{
   if($current_team!=$rs["name"])
   {
     $current_team=$rs["name"];
     //print team name header
   }
   // do other stuff here
}

[jtakkinen_82]

Thanks I will try this and let you know if it worked.

[aschk]

Make sure to issue an ORDER BY clause with your SQL statement to get them out in team order, then you can do the conditinal during the PHP loop.

It's more of a formatting issue than anything else, hence the requirement issue being more related to PHP.

[mwasif]

Make sure to issue an ORDER BY clause with your SQL statement to get them out in team order, then you can do the conditinal during the PHP loop.

He is right :-)

[jtakkinen_82]

Make sure to issue an ORDER BY clause with your SQL statement to get them out in team order, then you can do the conditinal during the PHP loop.

He is right :-)

 

Yeah I knew that, I already have it figured out in the SQL statement in my 1st post  .

[jtakkinen_82]

Ok, so here's my code... sorry the variable names are in Finnish (I changed them in the previous post so it would be easier for you to understand but I don't think it really matters). For some reason the system doesn't work, it goes to the "if" every time and I can't understand why.

 

		$tiimi_nyt = "";
	while($kuvaus = mysql_fetch_array($result))
	{ //vikakuvaukset hakeva while alkaa
		if($tiimi_nyt != $kuvaus['tiiminimi']);
		{
			$tiimi_nyt = $kuvaus['tiiminimi'];
			echo "<H3>$kuvaus[tiiminimi] vikatietokanta</H3>";
			echo "<TABLE WIDTH='90%' BORDER='0' ALIGN='CENTER' CELLPADDING='5' CELLSPACING='1' BGCOLOR='#B30000'>";
			echo "<TR ALIGN='CENTER' BGCOLOR='#CCCCCC'>";
			echo "<TD WIDTH='5%'><B>Tiimi ID</B></TD>";
			echo "<TD WIDTH='5%'><B>Tiiminimi</B></TD>";
			echo "<TD WIDTH='10%'><B>Vaihe</B></TD>";
			echo "<TD WIDTH='20%'><B>Nimi</B></TD>";
			echo "<TD WIDTH='30%'><B>Vikakuvaus</B></TD>";
			echo "<TD WIDTH='10%'><B>Tila</B></TD>";
			echo "<TD WIDTH='10%'><B>Muokattu</B></TD>";
			echo "<TD WIDTH='10%'><B>Toiminto</B></TD>";
			echo "</TR>";
		}
                echo "<TR BGCOLOR='#FFFFFF'>";
	        echo "<TD>";
	        echo $kuvaus[tiimi_id];
	        echo "</TD><TD>";
	        echo $kuvaus[tiiminimi];
                        etc...

 

And here's what it looks like in the browser, a total mess:

 

 

 

 

 

 

[jtakkinen_82]

I got it working, guys... thanks. I had some typos and stupid shit like that in the previous bit of code I sent + </table> was missing in the beginning, thus the table didn't "end" at the right time resulting in a multiple line at the beginning. Here's the final code:

 

	//Jos tuloksia löytyi, listataan ne
else if($num_rows > 0)
{
	$tiimi_nyt = "";
	while($kuvaus = mysql_fetch_array($result))
	{ //vikakuvaukset hakeva while alkaa

		if($tiimi_nyt != $kuvaus['tiiminimi'])
		{
			echo "</TABLE>";
			$tiimi_nyt = $kuvaus['tiiminimi'];
			echo "<H3>$kuvaus[tiiminimi] vikatietokanta</H3>";
			echo "<TABLE WIDTH='90%' BORDER='0' ALIGN='CENTER' CELLPADDING='5' CELLSPACING='1' BGCOLOR='#B30000'>";
			echo "<TR ALIGN='CENTER' BGCOLOR='#CCCCCC'>";
			echo "<TD WIDTH='5%'><B>Tiimi ID</B></TD>";
			echo "<TD WIDTH='5%'><B>Tiiminimi</B></TD>";
			echo "<TD WIDTH='10%'><B>Vaihe</B></TD>";
			echo "<TD WIDTH='20%'><B>Nimi</B></TD>";
			echo "<TD WIDTH='30%'><B>Vikakuvaus</B></TD>";
			echo "<TD WIDTH='10%'><B>Tila</B></TD>";
			echo "<TD WIDTH='10%'><B>Muokattu</B></TD>";
			echo "<TD WIDTH='10%'><B>Toiminto</B></TD>";
			echo "</TR>";
		}
	echo "<TR BGCOLOR='#FFFFFF'>";
	echo "<TD>";
	echo $kuvaus['tiimi_id'];
	echo "</TD><TD>";
	echo $kuvaus['tiiminimi'];
	echo "</TD><TD>";
	echo $kuvaus['vmalli'];
	echo "</TD><TD>";
	echo $kuvaus['nimi'];
	echo "</TD><TD>";
	echo $kuvaus['vikakuvaus'];
	echo "</TD><TD>";
	echo $kuvaus['tila'];
	echo "</TD><TD>";
	echo $kuvaus['aika'];
	echo "</TD><TD ALIGN='CENTER'>";
	echo "<A HREF='view.php?id=$kuvaus[iD]'><IMG SRC='b_browse.png' ALT='Näytä' BORDER='0'></IMG></A>";
	echo "<A HREF='edit.php?id=$kuvaus[iD]'><IMG SRC='b_edit.png' ALT='Muokkaa' BORDER='0'></IMG></A>";?>
	<A HREF="javascript:delRecord('<?php echo $kuvaus[iD];?>','<?php echo $kuvaus[nimi];?>');"><IMG SRC="b_drop.png" ALT="poista" BORDER="0"></IMG></A></TD>
	<?php
	echo "</TR>";

	} //vikakuvaukset hakeva while loppuu
	echo "</TABLE>";

 

Thanks for your help guys, it was much appreciated!

 

 

[mwasif]

You are welcome.