[eluser]RobbieL[/eluser]
I managed to get this done in Coldfusion, but having a bit of bother with CI. Basically, in my SQL WHERE statement, I need to reference a previous SQL statemenr to get the data I need. In this case, I want to list each school, with a list of the delegates underneath each school
These are my SQL statements at the moment:
Code:
#Get list of schools
$data['getSchools'] = $this->db->query("SELECT schoolName FROM schools GROUP BY schoolName");
#Get delegates
$data['getDelegates'] = $this->db->query("SELECT d.forename, d.surname, s.schoolName FROM delegates d LEFT JOIN schools s ON d.schoolId=s.id WHERE d.schoolId=s.id AND s.schoolName = '".$data['getSchools']."'");
And here's the PHP in my view:
Code:
<? foreach($getSchools->result() as $row)
{?>
<?=$row->schoolName;?>
<? foreach($getDelegates->result() as $row)
{?>
<?=$row->forename;?> <?=$row->surname;?>
<?
}?>
<?
}?>
The code doesn't spit out any areas, and does print the schools, just doesn't print the delegates from those schools. Not to sure were I'm going wrong.
I hope that all makes sense. Appreciate any help that can be given.
Cheers.