Referencing SQL Statement Within an SQL Statement

[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.

[eluser]smith[/eluser]
You are using $row in nested loop, after first iteration $row will be wrong.
in inside loop use something like $row2 or anything you like, just not $row, because you are using $row in outside loop...

Code:

$sql = "SELECT schoolName FROM schools GROUP BY schoolName";
$query = $this->db->query($sql);

<?
foreach($query->result() as $row)
{
    $sql2 = "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 = '".$row->schoolName ."'";
    $query2 = $this->db->query($sql2);

    print $row->schoolName;
    foreach($query2->result() as $delegate)
    {
        print $delegate->forename." ".$delegate->surname;
    }
}
?>

try something like this in your view ...

[eluser]RobbieL[/eluser]
Excellent. That worked a treat. Will I be able to tweak it so that all the SQL is in a controller?

[eluser]smith[/eluser]
yes, you can, you just need to organize your views slightly different...