foreaching through Objects?

[eluser]Stilly[/eluser]
Hi

I am stuck in something which can be simply my ignorance of PHP and OOP, or of CI - But it's such an standard task, so I hope someone can point me in the right direction:

I have two tables - COMPANY and PRODUCT. Now I wanna make a search over Product or Company. The SQL would look like this:

Code:

SELECT *
FROM (`company`)
JOIN `product` ON `company`.`id` = `product`.`company_id`
WHERE  UCASE(name)  LIKE '%A%'
OR  UCASE(brand)  LIKE '%A%

I do this in my CI model with this code:

Code:

$this->db->select('*');
$this->db->join('product', 'company.id = product.company_id');
$this->db->like('UCASE(name)',strtoupper($search_string));
$this->db->or_like('UCASE(brand)',strtoupper($search_string));
$query = $this->db->get('company');

So far so good. Now I want to create an output in which I list every company and all of their products. So I process the search in the controller and call the view.

Code:

$search_string = $this->input->post('search_string');
$data['query']=$this->suche_model->getSearchResults($search_string);

In the view I have to foreach through the results - but how?

a simple (not nestled) call "<?php foreach ($query->result() as $row): ?>" gives me:

Code:

Company A, Product1
Company A, Product2
Company A, Product3
Company B, Product1
Company B, Product2

What I want to achive is:

Code:

Company A
* Product1
* Product2
* Product3
Company B
* Product1
* Product2

But I simply seem not to understand how to construct a nestled foreach here. Anyone a hint for me? (Or ist the whole approach crap and I should get the data in a multi-dimenional array?)

confused

Sven

[eluser]LifeSteala[/eluser]
Hi,
Generally most will do this with two queries. However, it is possible with some if statements.

I'm assuming these field names inside your database tables:

COMPANY - company_id, company_name
PRODUCT - product_id, name

If the above is not what you have, change the variables in the code.

Code:

$temp_company = '';
foreach ($query->result() as $row) {
    $name = $row->company_name;
    $product = $row->name;
    
    if ($name != $temp_company) {
        $temp_company = $name;
        echo "<h2>$name</h2>";
    }
    
    echo "<p>$product</p>";    
}

This code is untested, so give it a try.

[eluser]Stilly[/eluser]
Hi

thanks for the hint of if-statements. I could have thought of it myself

But you also said:
[quote author="LifeSteala" date="1260174903"]Generally most will do this with two queries. However, it is possible with some if statements.[/quote]

Not sure what the "best practice" is in such a case. How would the approach with "two queries" work?

best

Sven

[eluser]LifeSteala[/eluser]
Well, you would get a list of distinct company id's where your search fields match. Then you setup another query to loop through the company id's and pull out the products. Hmmm, now that I think about it, this is probably not a great idea.

I would stick to the if statements with searches.

Good luck!

[eluser]Stilly[/eluser]
[quote author="LifeSteala" date="1260176025"]Hmmm, now that I think about it, this is probably not a great idea. I would stick to the if statements with searches.[/quote]

Well big thanks -> you pointed me in the right direction...

In the view the if-statements felt wrong and messed up the HTML code, so I used them inside the controller to construct a multidimensional array. Works just fine.

Code:

// construct a multidimensional array to have nice output
    $company = array();
    $company["name"] = array();
    $temp = "";
    foreach ($data['query']->result() as $row){
        if($temp != $row->name) {
          //we have a new company
          $temp=$row->name;
          $company[$row->name]=array();
          array_push($company[$row->name],$row->brand);
        }
        else{
          // we have another product
          array_push($company[$row->name],$row->brand);
          }
    }

Now I am asking myself if I should have done that in the model instead the controller? but maybe this is just a matter of taste...

Thanks again für pushing me forward on this one

Sven

[eluser]LifeSteala[/eluser]
Your very welcome.

The model should only be used to communicate with the database only. Manipulations to data occur in controllers and displaying data occurs in views.

So yes, you have done it correctly by performing such task in the controller.

Hope that makes sense.
LS

[eluser]jedd[/eluser]
I don't see why you need to mung the data in the controller at all - this can be done at the view level, without having to re-model your returned data.

Have a read of [url="http://ellislab.com/forums/viewreply/622401/"]this thread[/url] which does something similar.