Page 1 of 1

Extract and display from MySql

Posted: Tue Jul 27, 2010 6:14 pm
by facarroll
Hi.

I'm new to PHP MySql, and I would appreciate some help.

I have a database which holds information about students, and one piece of information is that each student is categorised into a group from 1-6.

I want to be able to display each student in one of six columns depending on group. The code I've written will only display one line of data, though there are several students in each group. I'd appreciate any advice as to where I'm wrong.

Thanks.

chanel

Code: Select all

<?php
// Query member data from the database
$query1 = mysql_query("SELECT userId FROM users WHERE managerId='".$userid."' AND userGroup='".$group1."' ORDER BY userId ASC"); 
$query2 = mysql_query("SELECT userId FROM users WHERE managerId='".$userid."' AND userGroup='".$group2."' ORDER BY userId ASC"); 
$query3 = mysql_query("SELECT userId FROM users WHERE managerId='".$userid."' AND userGroup='".$group3."' ORDER BY userId ASC"); 
$query4 = mysql_query("SELECT userId FROM users WHERE managerId='".$userid."' AND userGroup='".$group4."' ORDER BY userId ASC"); 
$query5 = mysql_query("SELECT userId FROM users WHERE managerId='".$userid."' AND userGroup='".$group5."' ORDER BY userId ASC"); 
$query6 = mysql_query("SELECT userId FROM users WHERE managerId='".$userid."' AND userGroup='".$group6."' ORDER BY userId ASC"); 
while($row1 = mysql_fetch_array($query1))
{
$firstGroup = $row1['userId'];
}
while($row2 = mysql_fetch_array($query2))
{
$secondGroup = $row2['userId'];
}
while($row3 = mysql_fetch_array($query3))
{
$thirdGroup = $row3['userId'];
}
while($row4 = mysql_fetch_array($query4))
{
$fourthGroup = $row4['userId'];
}
while($row5 = mysql_fetch_array($query5))
{
$fifthGroup = $row5['userId'];
}
while($row6 = mysql_fetch_array($query6))
{
$sixthGroup = $row6['userId'];
}
?>              
                   <table>
                    <tr style="text-align:center;">
                      <td width="26">&nbsp;</td>
                      <td width="133"><?php echo $firstGroup;?></td>
                      <td width="133"><?php echo $secondGroup;?></td>
                      <td width="133"><?php echo $thirdGroup;?></td>
                      <td width="133"><?php echo $fourthGroup;?></td>
                      <td width="133"><?php echo $fifthGroup;?></td>
                      <td width="133"><?php echo $sixthGroup;?></td>
                      <td Width="26">&nbsp;</td>
                    </tr>
                  </table><hr />

Re: Extract and display from MySql

Posted: Tue Jul 27, 2010 6:51 pm
by buckit
you are over writing the variable each time the while loops.

to append the variable you need to do .=

example:
instead of

Code: Select all

$firstGroup = $row1['userId'];
do

Code: Select all

$firstGroup .= $row1['userId'];
you will probably want to tack on a BR after each one as well so they display ontop of each other in a column. otherwise it will turn into one long continuous string with no separation between userid's
in that case you would do:

Code: Select all

$firstGroup .= $row1['userId']."<br />";

Re: Extract and display from MySql

Posted: Tue Jul 27, 2010 7:29 pm
by facarroll
Many thanks.

That looks good, but I'm at work now and cannot test it till later. I'll let you know. Appreciate it.

Re: Extract and display from MySql

Posted: Wed Jul 28, 2010 6:46 am
by facarroll
Your solution worked perfectly. Thanks a million. Also needed the tip about the BR. I never would have guessed. Thanks.

Re: Extract and display from MySql

Posted: Wed Jul 28, 2010 7:03 am
by buckit
No problem! Glad to help!