1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Foreach in Foreach Loop for MySQL

Discussion in 'PHP' started by Vistavision, Apr 13, 2015.

  1. #1
    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
     
    Vistavision, Apr 13, 2015 IP
  2. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,740
    Likes Received:
    28
    Best Answers:
    13
    Trophy Points:
    175
    #2
    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 :p
     
    EricBruggema, Apr 20, 2015 IP
  3. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,732
    Likes Received:
    1,998
    Best Answers:
    253
    Trophy Points:
    515
    #3
    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.
     
    deathshadow, Apr 21, 2015 IP