displaying query results

[carrilo]

I have a database that manages classes my school gives to various companies. I have a query where I can select any group and view the details of that group (level, timetable, teacher, students etc). The group details come from one table (groups) and the students from another (alumnos). With my current query I display all the info about the group on one line and then the students are listed below

 

 

$query = "SELECT groups.code AS tn, groups.groupname as gn, groups.nivel as nv, groups.libro as lib, groups.empresa as emp, groups.teacher1 as pf1,  
groups.teacher2 as pf2,groups.timetable as tbl,groups.inicio as ini, groups.fin as fin, groups.idioma as idi, alumnos.ida as id, alumnos.alumno as alm from groups, alumnos  WHERE groups.groupname='$tc' and alumnos.grupo='$tc' 
ORDER BY groupname"; 
$result = @mysql_query ($query); 

$num = mysql_num_rows($result); 
 if ($num>0) 
 { 
 echo"<p>$num alumnos.</p>\n"; 
} 
 else 
 { 
 echo'<p class="error">There are no teams like this</p>'; 
 } 
 if($result) 

 { 
 echo'<table align="center" cellspacing="0" cellpadding="5"> 
 <tr><td align="left"><b>Codigo</b></td><td align="left"><b>Empresa</b></td><td align="left"><b>Idioma</b></td><td align="left"><b>Teacher 1</b></td><td align="left"><b>Teacher 2</b></td> 
<td align="left"><b>Timetable</b></td><td align="left"><b>Inicio</b></td><td align="left"><b>Fin</b></td><td align="left"><b>Nivel</b></td><td align="left"><b>Libro</b></td></tr>'; 
 while($row = mysql_fetch_array ($result)) 
 { 
if ($row['gn'] !=$group){ 
 $group=$row['gn']; 
 $code=$row['tn']; 
  $nivel=$row['nv']; 
  $libro=$row['lib']; 
  $alumno=$row['alm'];  
  
 echo' 

<tr> 
<td align="left"> 
 '. $row['tn'] .'</td> 
<td align="left"> 
 '. $row['emp'] .'</td> 
<td align="left"> 
 '. $row['idi'] .'</td> 
<td align="left"> 
 '. $row['pf1'] .'</td> 
<td align="left"> 
 '. $row['pf2'] .'</td> 
<td align="left"> 
 '. $row['tbl'] .'</td> 
<td align="left"> 
 '. $row['ini'] .'</td> 

<td align="left"> 
 '. $row['fin'] .'</td> 


<td align="left"> 
 '. $row['nv'] .'</td> 
<td align="left"> 
 '. $row['lib'] .'</td> 
<tr> 

<td align="left"> 
 '. $row['alm'] .'</td> 

</tr>'; 
 } 
else { 
if ($row['gn'] !=$group){ 
 echo' 



<td align="left"> 
 '. $row['alm'] .'</td> 




 </tr>';  

 

 

What I would like to do is to be able to select the company and then display all the groups from that company in a similar way. Any ideas?

 

 

 

[ginerjm]

Please post your code properly.  Read the rules.

 

Now - what is the nature of your problem?  If you wrote all the above code you know how to do everything you need to do, so what are you really asking us?

[carrilo]

I had some help with the original code and am not sure how to adapt it.

 

In the original version I have a drop down menu which selects the group. It displays the query results in the following way:

 

Codigo            Empresa          Idioma            Teacher 1      Timetable        Inicio                    Fin           Nivel  

 

0091E-13        CX Ltd               Inglés             Bob Smith    Ju 14:30-16:00  2013-10-03    2014-06-12    B2.1   

 

John Smith

Dave Smith

Bill Silth

 

 

What I would like to do is display the results in the same way but have all the the groups from the same company shown in the same query.

 

I have changed the dropdown menu to select the company and adapted the query in the following way.

$query = "SELECT groups.code AS tn, groups.groupname as gn, groups.nivel as nv, groups.libro as lib, groups.empresa as emp, groups.teacher1 as pf1,
groups.teacher2 as pf2,groups.timetable as tbl,groups.inicio as ini, groups.fin as fin, groups.idioma as idi, alumnos.ida as id, alumnos.alumno as alm, alumnos.empresa as amp from groups, alumnos WHERE groups.empresa='$tc' and alumnos.empresa='$tc'
ORDER BY groupname";

This displays all the groups from the company in the same format as above but for each group it lists all of the students from the company. What I want to do is list the groups but only the students that belong to each group. Thanks

 

 

 

[ginerjm]

You need to read up on doing JOIN queries so you can join the students to a group and not simply collect all the records from both tables that match your $tc value.

 

btw - When you read up on JOINs you should read up on table aliases and try using them for your tablenames - makes it a lot easier to type your query and to read it later.

[carrilo]

Ok thanks, the structure of the database is probably improvable.  

 

But working with what I have, I have been trying various options but can't quite get it to display the results in the way I want. Currently the results show the details of each group from the selected company and then all the students from the company after the details of each group. What I need is for it to show only the students that belong to the group after the details of that group. So it needs to show students where groups.groupname gn = alumnos.grupo agp

 

Anyone have any ideas?

 

Currently the query is this

 

$query = "SELECT groups.code AS tn, groups.groupname as gn, groups.nivel as nv, groups.libro as lib, groups.empresa as emp, groups.teacher1 as pf1, 
groups.teacher2 as pf2,groups.timetable as tbl,groups.inicio as ini, groups.fin as fin, groups.idioma as idi, alumnos.ida as id, alumnos.alumno as alm, alumnos.empresa as amp 
, alumnos.grupo as agp
from groups, alumnos  WHERE groups.empresa='$tc' and alumnos.empresa='$tc'
ORDER BY groupname";

and the results are displayed using this code

{
if ($row['gn'] !=$group    ){
 $group=$row['gn'];
 $code=$row['tn'];
  $nivel=$row['nv'];
  $libro=$row['lib'];
  $alumno=$row['alm']; 
 $agroup=$row['agp'];
 echo'


<tr>
<td align="left">
 '. $row['tn'] .'</td>
<td align="left">
 '. $row['emp'] .'</td>
<td align="left">
 '. $row['idi'] .'</td>
<td align="left">
 '. $row['pf1'] .'</td>
<td align="left">
 '. $row['pf2'] .'</td>
<td align="left">
 '. $row['tbl'] .'</td>
<td align="left">
 '. $row['ini'] .'</td>


<td align="left">
 '. $row['fin'] .'</td>




<td align="left">
 '. $row['nv'] .'</td>
<td align="left">
 '. $row['lib'] .'</td>
<tr>


<td align="left">
 '. $row['alm'] .'</td>


</tr>';
 }
else {
if ($row['gn'] !=$group){
 echo'






<td align="left">
 '. $row['alm'] .'</td>








 </tr>';


 }


else {
 echo'




<tr>


<td align="left">
 '. $row['alm'] .'</td>




 </tr>'


  ;

[carrilo]

OK I've worked it out, yes the query was all wrong and I did need to use joins