CodeIgniter Forums

Full Version: Nested foreach with mysql
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

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

El Forum

[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
}
or
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-gui...sults.html

Good Luck!

El Forum

[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

El Forum

[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();
}
the only thing I can think of is that you used double quotes instead of single quotes in the SQL query.

El Forum

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

El Forum

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