+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Referencing SQL Statement Within an SQL Statement (/showthread.php?tid=3644)
Referencing SQL Statement Within an SQL Statement - El Forum - 10-15-2007
[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.
Referencing SQL Statement Within an SQL Statement - El Forum - 10-15-2007
[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 ...
Referencing SQL Statement Within an SQL Statement - El Forum - 10-15-2007
[eluser]RobbieL[/eluser]
Excellent. That worked a treat. Will I be able to tweak it so that all the SQL is in a controller?
Referencing SQL Statement Within an SQL Statement - El Forum - 10-15-2007
[eluser]smith[/eluser]
yes, you can, you just need to organize your views slightly different...