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
    94
    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. #2
    New Coder
    Join Date
    Oct 2011
    Posts
    94
    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 07:08 AM.

  3. #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,698
    Thanks
    93
    Thanked 4,947 Times in 4,908 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.
    Be yourself. No one else is as qualified.

  4. Users who have thanked Old Pedant for this post:

    hans_cellc (04-30-2013)

  5. #4
    New Coder
    Join Date
    Oct 2011
    Posts
    94
    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);

  6. #5
    New Coder
    Join Date
    Oct 2011
    Posts
    94
    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'];

  7. #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    29,698
    Thanks
    93
    Thanked 4,947 Times in 4,908 Posts
    Lol! *great*!
    Be yourself. No one else is as qualified.

  8. 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
  •