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 6 of 6
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    92
    Thanks
    38
    Thanked 0 Times in 0 Posts

    Combine Tables Information

    Please assist,

    I have 3 tables:

    model
    model_id |model
    1 | BMW 320D
    2 | Puegot 208
    3 | Mercedes 190E

    colour
    colour_id |colour
    1 | Red
    2 | Green
    3 | Blue
    4 | Yellow
    5 | Silver
    6 | Gold

    availability
    avail_id |model_id |colour_id
    NULL | 1 | 1
    NULL | 1 | 3
    NULL | 1 | 6
    NULL | 2 | 3
    NULL | 2 | 2
    NULL | 2 | 4
    NULL | 2 | 1
    NULL | 3 | 5
    NULL | 3 | 6

    The logic is as follows – if there is a database row that contains a model id and a colour id, then that model (as derived from the model id) is available in that colour (as derived from the colour id).

    I now need to print onto a web page (NOT A DATABASE) from these various tables the following:

    Model Colour Availability
    BMW 320d Red Yes
    BWW 320d Green No
    ETC ETC ETC

    I am getting it to print all the available models and colours but not the unavailable ones.

    Below the code I have so far:

    Code:
    // Connect to the DB
    $conn= mysql_connect("localhost", "****", "****");
    mysql_select_db("****");
    			
    // This is the query to get all the models that are available
    $queryA = "SELECT * FROM model, colour, availability WHERE model.model_id = availability.model_id AND colour.colour_id = availability.colour_id";		
    			
    $query_run = mysql_query($queryA) or die("<br />NOTHING FOUND");
    					
    echo "<div align='center'><table width='60%' border='1'><tr align='center' bgcolor='#eeffee'><th>ModelName</th><th>Colour</th><th>Available</th></tr>";
    					
    while($query_row = mysql_fetch_array($query_run)) {
    	$model = $query_row['model'];
    	$colour = $query_row['colour'];
    	$avail = "Yes";
    	echo "<tr  align='center'><td>$model</td><td>$colour</td><td>$avail</td></tr>";
    			}
    			
    echo "</table></div>";
    mysql_close($conn);

  • #2
    New Coder
    Join Date
    Oct 2011
    Posts
    92
    Thanks
    38
    Thanked 0 Times in 0 Posts
    I found the following SELECT with inner joining code pretty helpful however even without adding WHEN it only printout availables:

    Code:
    // Connect to the DB
    $conn= mysql_connect("localhost", "****", "****");
    mysql_select_db("****");
    			
    // This is the query to get all the models that are available
    // $queryA = "SELECT * FROM model, colour, availability WHERE model.model_id = availability.model_id AND colour.colour_id = availability.colour_id";
    $query = "SELECT * FROM availability a JOIN model b ON a.model_id = b.model_id JOIN colour c ON a.colour_id = c.colour_id";
    			
    // If no matches found will stop with error message
    $query_run = mysql_query($query) or die("<br />NOTHING FOUND");
    					
    echo "<div align='center'><table width='60%' border='1'><tr align='center' bgcolor='#eeffee'><th>Model Name</th><th>Colour</th><th>Available</th></tr>";
    					
    while($query_row = mysql_fetch_array($query_run)) {
    	$model = $query_row['model'];
    	$colour = $query_row['colour'];
    	$avail = "Yes";
    	echo "<tr align='center'><td>$model</td><td>$colour</td><td>$avail</td></tr>";
    }
    			
    echo "</table></div>";
    mysql_close($conn);
    Last edited by hans_cellc; 04-30-2013 at 06:08 AM.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,126
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    You need to use both a full cartesian join and an OUTER JOIN. Thus:

    Code:
    SELECT M.model, C.colour, IF(A.modelid IS NULL,'No','Yes') AS isAvailable
    FROM models AS M 
    INNER JOIN colours AS C ON 1=1
    LEFT JOIN availability AS A ON A.modelid = M.modlid AND A.colourid = C.colourid
    ORDER BY M.model, C.colour
    The full cartesian join is forced by the dummy ON clause (ON 1=1), so that *every* model will be matched with *every* colour.

    The LEFT join will then give a NULL value for A.modelid when there is no match, and we take advantage of that to produce the "NO" or "YES" that you wanted.

    Try that in a MySQL query tool *first* before you try it in your PHP code, to make sure it gives you what you want.
    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:

    hans_cellc (04-30-2013)

  • #4
    New Coder
    Join Date
    Oct 2011
    Posts
    92
    Thanks
    38
    Thanked 0 Times in 0 Posts
    Thanks a million.
    I applied this code and it now reads all the models with the colours but how do I allocate the isAvailable (in red) to the $avail variable.

    Code:
    // Connect to the DB
    $conn= mysql_connect("localhost", "****", "****");
    mysql_select_db("****");
    			
    // This is the query to get all the models that are available
    $query = "SELECT M.model, C.colour, IF(A.model_id IS NULL,'No','Yes') AS isAvailable
    FROM model AS M 
    INNER JOIN colour AS C ON 1=1
    LEFT JOIN availability AS A ON A.model_id = M.model_id AND A.colour_id = C.colour_id
    ORDER BY M.model";
    			
    			
    // If no matches found will stop with error message
    $query_run = mysql_query($query) or die("<br />NOTHING FOUND");
    					
    echo "<div align='center'><table width='60%' border='1'><tr align='center' bgcolor='#eeffee'><th>Model Name</th><th>Colour</th><th>Available</th></tr>";
    					
    while($query_row = mysql_fetch_array($query_run)) {
    	$model = $query_row['model'];
    	$colour = $query_row['colour'];
    	$avail = "isAvailable";
    	echo "<tr align='center'><td>$model</td><td>$colour</td><td>$avail</td></tr>";
    			}
    			
    echo "</table></div>";
    mysql_close($conn);

  • #5
    New Coder
    Join Date
    Oct 2011
    Posts
    92
    Thanks
    38
    Thanked 0 Times in 0 Posts
    WoooHooo

    I got it thank you very much and the scary part is I actually understand it.

    Code:
    $avail = $query_row['isAvailable'];

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,126
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Lol! *great*!
    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:

    hans_cellc (05-01-2013)


  •  

    Posting Permissions

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