Foreach in Foreach Loop for MySQL

Hi all,

I have a question. I use a SQL Join to match two tables in my database. After that I use a foreach loop to retrieve my information from the joined table. This works with this code:

   
        $data = $wpdb->get_results("
       
        SELECT *
        FROM prijzen
        INNER JOIN babyfoontest
        ON  prijzen.eancode = babyfoontest.ean
        ") or trigger_error("Fout in de query");

    $i = 1;
foreach ($data as $info) {
            echo ''.$info->shopnaam.'<br>';
            echo ''.$info->type.'<br>';
            echo ''.$info->price.'<br>';
            echo ''.$info->deeplink.'<br>';
            echo ''.$info->eancode.'<br><br>';
         $i++; } 

PHP:

I get all products with 1 price and 1 deeplink like this:

Amazon, Product 1, 99, www.test.com, 123456789
BCC, Product 2, 99, www.test.com, 23231323123
Zappos, Product 3, 99, www.test.com, 3434343434

Code (markup):

My Joined Table looks like this:

id | shopnaam | type | price | deeplink | eancode
1 | Amazon, Product 1, 99, www.test.com, 123456789
2 | BCC, Product 2, 99, www.test.com, 23231323123
3 | Zappos, Product 3, 99, www.test.com, 3434343434
4 | Shopping, Product 1, 119, www.test.com, 123456789
5 | Ebay, Product 1, 129, www.test.com, 123456789

Code (markup):

So I want to show a price comparison per product, matched by eancode. So I need to loop through the products first and after that through all ean matches like this:

Amazon, Product 1, 99, www.test.com, 123456789
>> Shopping 119
>> Ebay 129
BCC, Product 2, 99, www.test.com, 23231323123
Zappos, Product 3, 99, www.test.com, 3434343434

Code (markup):

I think I need a foreach() in a foreach() to do this but I don't know how?

Thanks for helping me out!

Kind regards,
Mark

 

You need to create another second query to fetch the prices of the same product from other sellers.

Can you show us your tables? and for as far as i can see i think its way better that you use even more tables.

Companies (company_id, name)
Products (product_id, name, numberofproductsavailable)
Prices (company_id, product_id, price)

When you need to fetch products, you'll use the products and use a link to company and prices
EX:
select products.*, companies.*, prices.*
from products
left join companies ON companies.company_id = products.company_id
left join prices ON prices.product_id = products.product_id
where /* your search options */
order by /* sort on price/date/whatever */

After fetching all results you can use the product_id to fetch other prices from other companies like this
EX:
select companies.*, prices.*
from prices
left join companies ON companies.company_id = prices.company_id
etc etc

Hope this is what you are looking for...

BTW its funny you use dutch and english words, please try to keep it OR dutch OR english!! makes coding much easier!
Oh and make every line do its thing so when joining another table, keep it on the same line, its easier to read by others.

Echt waar, zo waar als ik een Nederlander ben

 

1) Ah, turdpress' wpdb object crap -- returning an ENTIRE result set instead of iterating a PDO or MYSQLi result handler is a massive waste of memory. You REALLY should be using "while" on a result set, not making a giant copy of the entire result set in memory.

I swear every time I see $wpdb I have to fight the urge to punch a hole in the wall. PRIME example of why so many of these off the shelf CMS are halfwit code-bloat inefficient nonsense.

2) Why are you incrementing a counter you aren't even using?

3) Why are you using slower string addition and multiple echo to do the job of ONE echo and comma delimits? Much less what are those empty strings for?!?

I'm really just not sure what you seem to think you need the join for, but as @EricBruggema said it would probably help if we could see your table structures and had a better idea what your data was and what you are trying to do with it. Generally speaking the moment JOIN starts being used this way I get the feeling something simple is being overcomplicated for no good reason.