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 12 of 12
  1. #1
    New Coder
    Join Date
    Oct 2013
    Location
    South Africa
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts

    SQL Table Not Displaying Correct Results

    I have 3 tables in my database:
    MODEL COLOUR AVAILABILITY
    model_id colour_id avail_id
    model colour model_id
    colour_id

    values(3): values(6): values(9):
    Mercedes white Mercedes = white, silver, blue
    BMW silver BMW = white, grey, blue
    Ferrari grey Ferrari = silver, black, red
    black
    blue
    red

    I am trying to display results in a table for all the models that are available per colour.
    My table is displaying the results BUT it is being displayed as model_id, colour_id and avail_id.
    I need : the display to display 18 rows by listing the text fields and not the _id fields.
    : if the model is available in the colour it should display Yes, else No.
    Eg.
    Model Colour Availability
    Mercedes White Yes
    Mercedes Black No
    BMW Red No
    BMW Blue Yes
    Ferrari Red Yes
    Ferrari White No

    I have tried many sql JOIN and Where statements but nothing seems to workand I get all sorts of errors.
    All my coding up to here is correct so far to check the availability, but I simply cannot display the table I need.

    PHP Code:
    echo "<table border='1'>";
    echo 
    "<tr>";
    echo 
    "<th>Model</th><th>Colour</th><th>Availability</th>";
    echo 
    "</tr>";:confused:

    while (
    $result=mysql_fetch_object($query)){
    for(
    $i=1$i<20$i++){          //Start for loop for 18 models that I know I have in model table (3 models x 6 colours)
        
    for ($i2=1$i2<7$i2++){      //Start for loop inside MODEL for loop to for each colour that there is.
     
    $i=mysql_query("SELECT model FROM model");         //query model table to get the model name where model_id=$1
     
    $i2=mysql_query("SELECT colour FROM colour");         //query colour table  to get the colour name where model_id=$i2

    $avail=mysql_query("SELECT * FROM availability WHERE model_id=$i AND colour_id=$i2"); //query availability where the 
                                                                                             //model_id=$1 and colour_id=$i2
    }
    }
    }
                            
    //get the num of rows of the query, If there is a row, then set a var (say $is_avail) to yes, else no. 
    for($avail=1$avail<20$avail++)    {
        
    $is_avail mysql_fetch_array($avail);
        if (
    $is_avail == True)
                { echo 
    "Yes" ;
                }
        elseif (
    $is_avail == False) {
            echo 
    "No"
               }
    }           
            echo 
    "<tr>";
            echo 
    "<td>"$i"</td><td>"$i2"</td><td>",$is_avail"</td>";
            echo 
    "</tr>";
        
    echo 
    "</table>";
    mysql_close($connection); 

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    If I understand this design correctly, which I'm not sure if I do since it doesn't make any sense in its current form (how you're describing it; I *think* its valid in its actual schema form if I follow you), you can use a LEFT join from the colour table.
    Code:
    SELECT model, colour, IF(avail_id NOT IS 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
    Run that against the sql to see if it gives you back what you want. I'm not 100% sure that'll work (all colours merged into only availability which is merged into matching models - it looks like it would work), but the SQL guys can give one that would if it doesn't. Then when you have that, you just run that through PHP and use a while loop.

    Never do this:
    PHP Code:
    for($i=1$i<20$i++){          //Start for loop for 18 models that I know I have in model table (3 models x 6 colours)
        
    for ($i2=1$i2<7$i2++){      //Start for loop inside MODEL for loop to for each colour that there is. 
    ESPECIALLY when you have queries in them (ie: you query 18 * 6 * 3 = 324 times to get a small recordset of data [which wouldn't work though since its nor properly fetched, but that's irrelevant since you won't be using that] instead of once which is all you need. This doesn't even count the outer query which is not posted and may be merge-able into another query). Magic numbers are never ideal in your code and would require a change in order to modify the output. Change your query parameters instead of the code in use.
    Last edited by Fou-Lu; 10-28-2013 at 03:55 PM.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #3
    New Coder
    Join Date
    Oct 2013
    Location
    South Africa
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi Fou Lu

    Thanks for the help. I have tried the sql query you gave me, but now it has a new error:
    Query failed: Query was empty Actual query:
    Here is my new code as you suggested.
    PHP Code:
    # Get Results From Database
    $result mysql_query("SELECT model, colour, IF(avail_id NOT IS 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 "
    );

    $qr1 mysql_query ($result)
      or die (
    "Query failed: " mysql_error() . " Actual query: " $result);
      
    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->avail_id;
            print 
    "</td>\n";            
            print 
    "</tr>\n";
            
        }
    print 
    "</table>\n"

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You cannot query a query result resource. You want to use fetch on the $result.

    Edit:
    Also, this won't work:
    PHP Code:
            print $fix->avail_id
    What will work is:
    PHP Code:
            print $fix->available
    Which should be either yes or no.

    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #5
    New Coder
    Join Date
    Oct 2013
    Location
    South Africa
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I still cannot get the table to work correctly. Please, please, please HELP

    PHP Code:
    //connect to the database

    $db mysql_connect("$host""$username""$password") or die ("Error connecting to database.");

    mysql_select_db("$database"$db) or die ("Couldn't select the database.");

    # 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_array($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";

    ?> 

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Run your query through a mysql tool, and see if it gives you back the resultset you anticipate. If it does, than you'll need to describe the problem; I don't know what the issue is you are currently seeing on this result, you need to clarify the issue.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #7
    New Coder
    Join Date
    Oct 2013
    Location
    South Africa
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Im sorry, but sql queries are making me

    Notice: Trying to get property of non-object in C:\Program Files\EasyPHP-5.3.3\www\table3.php on line 45
    print $fix->model;
    Notice: Trying to get property of non-object in C:\Program Files\EasyPHP-5.3.3\www\table3.php on line 49
    print $fix->colour;
    Notice: Trying to get property of non-object in C:\Program Files\EasyPHP-5.3.3\www\table3.php on line 53
    print $fix->available;

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Gotcha.
    Change this:
    PHP Code:
        while ($fix mysql_fetch_array($result)) { 
    To this:
    PHP Code:
        while ($fix mysql_fetch_object($result)) { 
    And that should clear it up.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    Nash Somaroo (10-29-2013)

  • #9
    New Coder
    Join Date
    Oct 2013
    Location
    South Africa
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    You are a star

    just one more thing, sorry, the table is only displaying the Yes values and not the NO values.

    You can check out the website as per the link provided.
    http://45954712-advancedphp.atwebpages.com/

    This project (final year php for exam purposes) consists of 14 pages of different coding styles. It was just two pages that gave me hard time. Apart from the table I only had a problem with displaying the "Result" in the adv calc page.

    The page in question is OOP5_select.php.

    Once again, Thank You.

  • #10
    New Coder
    Join Date
    Oct 2013
    Location
    South Africa
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I want to display all results in a table from the 3 tables in my database.
    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.

    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:
    $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 "
    ); 

  • #11
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You'll need a new query, the one I wrote is clearly deficient in intersecting.
    Check with the SQL guys.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    Nash Somaroo (10-29-2013)

  • #12
    New Coder
    Join Date
    Oct 2013
    Location
    South Africa
    Posts
    12
    Thanks
    4
    Thanked 0 Times in 0 Posts
    @Fou-Lu

    I was provided the code by Old Pedant
    He used a Cartesian Product.

    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
    Thanks for your first half of the code.
    Could you mark this thread as [Resolved] as I cannot find the option for it.


  •  

    Posting Permissions

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