Multiple tables, multiple id's

[stephenTMS]

Hi all, I am assuming this is possible but I have been searching and trying things all day with no luck.

If I have 2 or more tables both with an id column and I want to select both of those id column's with an inner join and put them into an array, how would I do this? I am getting errors saying I have undefined indexes. Here is some of the code to show what I am talking about

$result = mysqli_query($link, 'SELECT drinks.id, drinks.name, promo, visible, price.id, price FROM drinks
                       INNER JOIN price ON priceid = price.id'); 

while ($row = mysqli_fetch_array($result))
{
    $drinks[] = array('drinks.id' => $row['drinks.id'], 'name' => $row['name'], 'promo' => $row['promo'],
                      'visible' => $row['visible'], 'price.id' => $row['price.id'], 'price' => $row['price']);
}

include 'drinks.html.php'; 

How do you call these id's in an array if not using 'drinks.id' etc? Thanks

[r937]

use column aliases in your sql...

SELECT drinks.id AS drinksid
     , drinks.name
     , drinks.promo
     , drinks.visible
     , price.id AS priceid
     , price.price
  FROM drinks 
INNER 
  JOIN price 
    ON price.id = drinks.priceid

and then in your php code, reference the names highlighted in red above

[stephenTMS]

That worked a treat. Thanks!

Another question...Can I use the above method to also include categories? i.e. have the drinks and prices displayed within their categories, all on one page. I feel i'm not explaining this very well, what I want to achieve is:

Category name 1
-----------------
Drink 1 - Price 1
Drink 2 - Price 2

Category name 2
----------------
Drink 1 - Price 1
Drink 2 - Price 2

$result = mysqli_query($link, 'SELECT drinks.id, drinks.name AS drinksname, promo, visible, price,
                       category.name AS categoryname FROM drinks
                       INNER JOIN price ON priceid = price.id
                       INNER JOIN drinkscategory ON drinksid = drinks.id
                       INNER JOIN category ON categoryid = category.id'); 

while ($row = mysqli_fetch_array($result))
{
    $drinks[] = array('id' => $row['id'], 'drink.name' => $row['drinksname'], 'promo' => $row['promo'],
                      'visible' => $row['visible'], 'price' => $row['price'], 'category' => $row['categoryname']);
}

include 'drinks.html.php'; 

drinks.html.php

<?php foreach ($drinks as $drink): ?>
    
    <h1><?php htmlout($drink['category']); ?></h1>
    
    <div>
    <ul>
        
        <li><?php htmlout($drink['drink.name']); ?></li>
        <li><?php htmlout($drink['price']); ?></li>
            
            <?php if ($drink['promo'] == 'yes') {
                echo '<li><input type="checkbox" checked="checked" /></li>';
                } else {
                echo '<li><input type="checkbox" /></li>';
                }
            ?>
            
            <?php if ($drink['visible'] == 'yes') {
                echo '<li><input type="checkbox" checked="checked" /></li>';
                } else {
                echo '<li><input type="checkbox" /></li>';
                }
            ?>
        
        <li><input type="submit" value="Edit" /></li>
        
    </ul>
    </div>
        
    <?php endforeach; ?>

Do I have to use two seperate SQL queries for this or is it achievable in this way? Thanks again.

[r937]

your query should have an ORDER BY clause

SELECT category.name AS categoryname 
     , drinks.id AS drinksid
     , drinks.name
     , drinks.promo
     , drinks.visible
     , price.id AS priceid
     , price.price
  FROM category
INNER
  JOIN drinkscategory
    ON drinkscategory.categoryid = category.id
INNER
  JOIN drinks 
    ON drinks.id = drinkscategory.drinksid
INNER 
  JOIN price 
    ON price.id = drinks.priceid
ORDER
    BY category.name 
     , drinks.name

i can't help you with the php, sorry

[stephenTMS]

Thanks for the help. I worked out the PHP, I needed to use an if statement to check if the drinks were in the same category.

$lastCategory = '';

        
        if($drink['category'] != $lastCategory) {
            $lastCategory = $drink['category'];
            echo '<h1>' . $lastCategory . '</h1>';
        }