Multiple tables, multiple id's

PHP
1

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

2

use column aliases in your sql…

SELECT drinks.id [B]AS [/B][COLOR="#FF0000"]drinksid[/COLOR]
     , drinks.[COLOR="#FF0000"]name[/COLOR]
     , drinks.[COLOR="#FF0000"]promo[/COLOR]
     , drinks.[COLOR="#FF0000"]visible[/COLOR]
     , price.id [B]AS [/B][COLOR="#FF0000"]priceid[/COLOR]
     , 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

3

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.

4

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

5

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>';
        }