+- 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: Nested foreach with mysql (/showthread.php?tid=24932)
Nested foreach with mysql - El Forum - 11-24-2009
[eluser]The Beginner[/eluser]
I am having trouble with a nested foreach loop. Basically I have an array list of key values that is the outer foreach. Then the first lines on code in that loop is a query pulling all records that contain the key in the outer foreach.
EXAMPLE:
Code:
$key_values = array("value1", "value2");
foreach($key_values as $key)
{
// This for each process 2 times as it should
$sql= 'SELECT * FROM wmp_order WHERE wmp_name ="MainPage" AND wmp_div ="'.$key.'" ORDER BY wmp_page_order';
$query = $this->db->query($sql);
foreach($query as $row){
//process all these records
// This foreach processes 4 times even though there 6 records returned.
}
}I would assume that the inner foreach would process 6 times since it returns six records. Then control would return to the first foreach loop and it would then process the inner loop again. Is this correct or am I missing something?
Nested foreach with mysql - El Forum - 11-24-2009
[eluser]CI Coder[/eluser]
inner foreach should be:
Code:
foreach ( $query->result() as $row ){
//process the info getting like this:
echo $row->column_name; // you get the idea
}Code:
foreach ( $query->result_array() as $row ){
//process the info getting like this:
echo $row['column_name']; // you get the idea
}$query is a result set object, not an array. You can read more here http://ellislab.com/codeigniter/user-guide/database/results.html
Good Luck!
Nested foreach with mysql - El Forum - 11-25-2009
[eluser]The Beginner[/eluser]
Thanks for the responce, sorry, I omitted part of the inner foreach($query->result() as $row).
My code that is giving me problems is correct and I am getting only one record out of six in the inner foreach.
I have placed counters in both foreach blocks and the outer one is running 2 times while the inner one is running 4 times, but I am only getting the first record the first time through the inner foreach and nothing on the second time through. As my post shows the outer one has 2 elliments in the array. The query on the inner foreach is returning 6 records.
So, given the above, 2 elliments in the outer array and 6 records in the inner array, should the inner foreach run 6 times then return to the outer foreach and it run a second time?
Hopfully I have explained this correctly.
Thanks,
Del
Nested foreach with mysql - El Forum - 11-25-2009
[eluser]CI Coder[/eluser]
I don't know what to tell you... except try the following code and see if you get the same results.
Code:
$key_values = array("value1", "value2");
foreach($key_values as $key)
{
// This for each process 2 times as it should
$sql= "SELECT * FROM wmp_order WHERE wmp_name = ? AND wmp_div = ? ORDER BY wmp_page_order";
$query = $this->db->query($sql, array('MainPage', $key));
foreach($query->result() as $row){
//process all these records
// This foreach processes 4 times even though there 6 records returned.
}
$query->free_result();
}Nested foreach with mysql - El Forum - 11-25-2009
[eluser]Jeroen Brussich[/eluser]
Could you please post your entire code, so we can see what's going on.
In your example, you don't return anything...
Also, stoopid question, but does 'value2' returns a mysql-result?
If $query->result() is empty, your foreach will not be executed...
Nested foreach with mysql - El Forum - 11-25-2009
[eluser]Federico Baña[/eluser]
try something like this.
<code>
$key_values = array('asd1', 'asd2');
$this->db->where('wmp_name', 'MainPage');
foreach($key_values as $key)
{
$this->db->or_where('wmp_div', $key);
}
$query = $this->db->get('wmp_order');
if($query->num_rows() > 0)
{
// whatever you want to do with the results
}
</code>
the point is that you shouldnt be running so many queries as it will cost you a lot of performance.
i hadnt tried that code, but otherwise you should try the query like this:
SELECT * FROM wmp_order
WHERE wmp_name = 'MainPage'
AND wmp_div = 'value1'
OR wmp_name = 'MainPage'
AND wmp_div = 'value2'
OR wmp_name = 'MainPage'
AND wmp_div = 'value3'
...