newbie question: Double Foreach

[eluser]Razedd[/eluser]
Hello

I want to get two query's in a two foreach's

This is the non CI version and that works but i cant get it in CI.

Code:

$res=mysql_query("SELECT ResDatum FROM reservering group by ResDatum order by ResDatum ASC") or die(mysql_error());
While ($row = mysql_fetch_assoc($res))
{
    echo "<h4>Date:".$row['ResDatum'].".</h4>";
    
    $res1=mysql_query("SELECT * FROM reservering where ResDatum = '".$row['ResDatum']."' order by ResTafel, ResTijd DESC") or die(mysql_error());
    While ($row1 = mysql_fetch_assoc($res1))
    {
        echo "Tafel ".$row1['ResTafel']." is gereserveerd om ".$row1['ResTijd']." voor ".$row1['ResDuur']." uur. Deze reservatie heeft reservatieID: ".$row1['ResID'].".<br> ";
    }
}

This is what i have made up until now

Code:

function reserveer()
    {
        $data['query1']=$this->db->query('SELECT Resdatum FROM `reservering` GROUP BY `ResDatum`');
        $data['query2']=$this->db->query('SELECT * FROM `reservering` WHERE `ResDatum`='.$query1->result().'');
        $this->load->view('reserveer_view', $data);
    }

//view

<?php Foreach ($query1->result() as $row): ?>
<h3>&lt;?=$row->ResDatum?&gt;</h3>
    &lt;?php foreach ($query2->result() as $row1): ?&gt;
    <p>Tafel &lt;?=$row->ResTafel?&gt; is gereserveerd om &lt;?=$row->ResTijd?&gt; voor &lt;?=$row->ResDuur?&gt; uur. ReserveringsID &lt;?=$row->ResID?&gt;.</p>
    &lt;?php endforeach;?&gt;
&lt;?php endforeach;?&gt;

Can someone explain how to do this or maybe find a complete different way to achieve the same

Thank you

greetings
Razedd

[eluser]Razedd[/eluser]
is this impossible?
or don't you understand what i want?

[eluser]Met[/eluser]

Code:

$data['query2']=$this->db->query('SELECT * FROM `reservering` WHERE `ResDatum`='.$query1->result().'');

this line is definitely wrong - the WHERE condition is a result set


this is untested, but do all the foreaching before you pass the data

so, for every result from the first query, you want to do another query using $row['ResDatum'] as a WHERE condition. and store results of THIS query in to an array.

then pass that to the view.

I see what you're trying to do but I don't think it will ever work that way - there is a huge difference between while() and foreach()

in my head its something like

Code:

$query1=$this->db->query('SELECT Resdatum FROM `reservering` GROUP BY `ResDatum`');
foreach($query1->result() as $row)
{
  //without testing not sure, you need to pass the result array in to another array    
  $query2[]=$this->db->query('SELECT * FROM `reservering` WHERE `ResDatum`='.$row->ResDatum.'');
}

//pass $query1 and $query2 to a $data array, load the view with this.

there might be a problems with performance though with this.

edit - on a second thought, could you not just use a JOIN?

[eluser]Bart v B[/eluser]
The use of GROUP BY is nog possible here as you demonstrate.
You only can use GROUP BY with agegrate functions. Like COUNT SUM.
Your mysql configuration is not ok.

Use strict mode then you see what i mean

Better is to look wih DATE_DIFF() and use an alias.
Then you only have one foreach() loop.
In short, you can use one query here.
I don't have the time now to make an example, but give it a try.

[eluser]Razedd[/eluser]
[quote author="Met" date="1284123824"]
so, for every result from the first query, you want to do another query using $row['ResDatum'] as a WHERE condition. and store results of THIS query in to an array.

then pass that to the view.
[/quote]

Thanks, that should do the trick i guess, if come this far now

Code:

$query1[]=$this->db->query('SELECT Resdatum FROM `reservering` GROUP BY `ResDatum`');
        foreach($query1->result() as $row)
        {
          //without testing not sure, you need to pass the result array in to another array    
          $query2[]=$this->db->query('SELECT * FROM `reservering` WHERE `ResDatum`='.$row->ResDatum.' ');
        }
        $data['$query1'] = $query1;
        $data['$query2'] = $query2;
        $this->load->view('reserveer_view', $data);

I made that now but there is an error

Code:

Fatal error: Call to a member function result() on a non-object in C:\wamp\www\Code_ignitor\system\application\controllers\restaurant.php on line 21

since im new to this and i dont realy know how to fix this i fugured i should post.

Thanks for the help allready, it was stupid from me not to think of this

Kind regards
Razedd

[eluser]Razedd[/eluser]
Okhe i fixed it and i figure i should post it..
After a talk with some people i made it like this

Code:

$data['query']=$this->db->query('SELECT * FROM `reservering` ORDER BY `ResDatum`,`ResTafel`, `ResTijd` ASC');
        $this->load->view('reserveer_view', $data);

//thats all for the controller

//here's the view
<?php
$datum='';
foreach ($query->result() as $row):
    if($row->ResDatum!=$datum)
    {
        ?>
        <h3>&lt;?=$row->ResDatum?&gt;</h3>
        <p>Tafel &lt;?=$row->ResTafel?&gt; is gereserveerd om &lt;?=$row->ResTijd?&gt; voor &lt;?=$row->ResDuur?&gt; uur. ReserveringsID &lt;?=$row->ResID?&gt;.</p>
        &lt;? $datum=$row->ResDatum;
    }
    else
    {
        ?&gt;
        <p>Tafel &lt;?=$row->ResTafel?&gt; is gereserveerd om &lt;?=$row->ResTijd?&gt; voor &lt;?=$row->ResDuur?&gt; uur. ReserveringsID &lt;?=$row->ResID?&gt;.</p>
        &lt;?php
    }
endforeach;?&gt;

now it works fine but its probably not optimaly using CI, if you see things that just scream to be improved please say so.

Thanks for youre help,
Razedd