SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast stephenTMS's Avatar
    Join Date
    Jun 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple tables, multiple id's

    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

    PHP Code:
    $result mysqli_query($link'SELECT drinks.id, drinks.name, promo, visible, price.id, price FROM drinks
                           INNER JOIN price ON priceid = price.id'
    ); 
    PHP Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    use column aliases in your sql...
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast stephenTMS's Avatar
    Join Date
    Jun 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    PHP Code:
    $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'
    ); 
    PHP Code:
    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 Code:
    <?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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    your query should have an ORDER BY clause
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast stephenTMS's Avatar
    Join Date
    Jun 2006
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    $lastCategory '';

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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •