Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Oct 2013
    Location
    South Africa
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts

    mysql output not displaying NULL results

    I want to display all results in a table from the 3 tables in my database.

    table model - Fields (model_id, model)
    table colour - Fields (colour_id, colour)
    table availability - Fields(avail_id, model_id, colour_id)

    I have 3 car models(Mercedes, BMW, Ferrari),
    6 colours(white, silver, grey, black, blue, red),
    these are available as follows in my availability table:
    Mercedes = white, silver, blue
    BMW = white, grey, blue
    Ferrari = silver, black, red.

    output table:
    Model Colour Availability
    Mercedes white Yes
    BMW grey Yes
    At the moment my table is displaying "Yes" in my table for the availability which is extracted from the availability table (which is 9 values as per above).

    I need the display table to also display the other 9 values as "No" which is not in the availability table. (3 models * 6 colours), which is the:
    Mercedes is not available in grey, black, red
    BMW is not available in silver, black, red
    Ferrari is not available in white, grey, blue.

    here is my code:

    PHP Code:
    # Get Results From Database
    $query = ("SELECT model, colour, IF(avail_id IS NOT NULL, 'Yes', 'No') AS available
                FROM colour
                LEFT JOIN availability ON availability.colour_id = colour.colour_id
                INNER JOIN model ON model.model_id = availability.model_id "
    );

    $result mysql_query($query)
      or die (
    "Query failed: " mysql_error() );
      
    print 
    "<table align='center' width='40%' cellpadding='2' cellspacing='2'>\n";
    print 
    "<tr>\n";
    print 
    "<td align='center'>\n";
    print 
    "<b>Model</b>";
    print 
    "</td>\n";
    print 
    "<td align='center'>\n";
    print 
    "<b>Colour</b>";
    print 
    "</td>\n";

    print 
    "<td align='center'>\n";
    print 
    "<b>Availability</b>";
    print 
    "</td>\n";
    print 
    "</tr>\n";
        
    while (
    $fix mysql_fetch_object($result)) {
            print 
    "<tr bgcolor='#eeeeee'>\n";
            
            print 
    "<td align='center'>\n";
            print 
    $fix->model;
            print 
    "</td>\n";
            
            print 
    "<td align='center'>\n";
            print 
    $fix->colour;
            print 
    "</td>\n";    

            print 
    "<td align='center'>\n";
            print 
    $fix->available;
            print 
    "</td>\n";            
            print 
    "</tr>\n";
            
        }
    print 
    "</table>\n"

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,664
    Thanks
    80
    Thanked 4,643 Times in 4,605 Posts
    Of course that's what you get.

    You can only get colour/model combinations that exist in the availability table.

    You have to use an artificial (cartesian product) join for this.
    Code:
    SELECT model, colour, IF(avail_id IS NOT NULL, 'Yes', 'No') AS available 
                FROM colour INNER JOIN model ON 1=1
                LEFT JOIN availability 
                ON (     availability.colour_id = colour.colour_id 
                     AND model.model_id = availability.model_id )
                ORDER BY model, colour
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Nash Somaroo (10-29-2013)

  • #3
    New Coder
    Join Date
    Oct 2013
    Location
    South Africa
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Your help is GREATLY appreciated.

    I did read about the Cartesian Product from the Oracle Database 10g SQL Manual, but it looked so complicated.
    Your code is so simple to that of Oracle.

    Oh, and I was waiting patiently for your post.

    Could you kindly mark this post as [Resolved] as I cannot find that option.


  •  

    Posting Permissions

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