Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    98
    Rep Power
    9

    Query and Display from Multiple Tables


    Is this possible? If so how?

    Table - shingles: s_id, s_name, s_year
    Table - colors: c_id, s_id, c_name

    In table shingles:
    1, Owens Corning
    2, Elk

    In table colors:
    1, 1, Autumn Brown
    2, 1, Estate Gray
    3, 1, Driftwood
    4, 2, Weathered Wood
    5, 2, Charcoal Gray
    6, 2, Forest Green

    I'd like to run a query that will display the results as so:

    Owens Corning
    • Autumn Brown
    • Estate Gray
    • Driftwood


    Elk
    • Weathered Wood
    • Charcoal Gray
    • Forest Green


    I have tried joins but can only get one result (Owens Corning) to display. Perhaps what I'd like to do isn't possible, if so I'd like to know so I can move on.

    Thanks
    David
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,502
    Rep Power
    594
    What does this have to do with PHP? Shouldn't this be in one of the database forums?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,122
    Rep Power
    9398
    Originally Posted by gw1500se
    What does this have to do with PHP? Shouldn't this be in one of the database forums?
    Probably.
  6. #4
  7. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,122
    Rep Power
    9398
    Actually it's a two-parter and the PHP side is the harder one.

    OP, what's your query and your code? The query should be a very simple JOIN with an ORDER BY to get the results in the order you want. The code will need a bit of logic to be able to break the big resultset it gets back into the different "sections" by shingle name.
    Code:
    previous shingle = empty
    for each result {
    	if current shingle != previous shingle {
    		if previous shingle isn't empty {
    			end the previous section of shingles
    		}
    		start a new section of shingles
    		previous shingle = current shingle
    	}
    	print the color
    }
    end the previous section of shingles
    Last edited by requinix; October 14th, 2012 at 07:32 PM. Reason: s/single/shingle/g
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by requinix
    The query should be a very simple JOIN with an ORDER BY to get the results in the order you want.
    this is the correct approach

    use the database to retrieve the data

    use php to format it nicely with H3 tags for the shingles and UL/LI tags for the colours
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    98
    Rep Power
    9
    Putting information into database tables is a rather simple procedure. Getting the information back out and displaying it as you would like, isn't always as simple.

    I tried this:

    PHP Code:
    $query "SELECT s_id, s_name FROM shingles";
    $result mysql_query ($query);
    while(
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    {
        
    $s_id stripslashes($row['s_id']);
        
    $s_name stripslashes($row['s_name']);

        echo 
    "<p>$s_name</p>";

        
    $query "SELECT c.c_name
        FROM colors AS c
        LEFT JOIN shingles AS s
        ON (c.s_id = s.s_id)
        WHERE c.s_id = '
    $s_id'
        ORDER BY c_id"
    ;

        
    $result mysql_query ($query);
        if (
    mysql_num_rows($result) == 0)
        {
            echo 
    "";
        }
        else
        {
            echo 
    "<ul>";

            while(
    $row mysql_fetch_array($resultMYSQL_ASSOC))
            {
                
    $c_name stripslashes($row['c_name']);

                echo 
    "<li>$c_name</li>";
            }

            echo 
    "</ul>";
        }

    This returned the first result set and that's it. A regular query of the color table would have given the same result as this left join.

    I can display the results the way I'd like by eliminating the colors table and adding the shingle colors to the shingle table, but there would either be too many color fields or not enough. The form that feeds the color table has only one color field and upon successful entry the the user is presented with the option of displaying the form again to add another color.

    requinix I'll give your suggestion some thought to see how I can work it. Thanks for the help.
    Last edited by DavidPr; October 14th, 2012 at 07:44 PM.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    98
    Rep Power
    9
    I'm finding that this is a very common question that people have, but I haven't seen an answer to it yet.

    If table-1 only had one entry then it would be a simple thing to display that one entry and all the entries from table-2 that was related to that one entry from table-1 by way of a common id.

    However, if there is more than one entry in table-1 then it seems you'll have to write a new query for each entry specifically naming the id, and using that id to query table-2 for any related information associated with that id. For instance:

    First we query the first table to list the id's to use in the other queries.
    PHP Code:
    $query "SELECT * FROM first_table;
    $result = mysql_query($query);
    while (
    $row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        
    $whatever_id = $row['whatever_id'];
        
    $whatever_name = stripslashes($row['whatever_name']);
        echo "
    $whatever_name $whatever_id<br />";

    Results:
    Howdy Doody - 1
    Hopalong Cassidy - 2
    The Brady Bunch - 5
    ...and so on


    Then once we're able to physically see the id numbers (because some may have been deleted) we can then do our queries to display the information we want from both tables.

    Queries for id - 1
    PHP Code:
    $query "SELECT whatever_name FROM first_table WHERE whatever_id=1;
    $result = mysql_query($query);
    while (
    $row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        
    $whatever_name = stripslashes($row['whatever_name']);
    }
        echo "
    <p><strong>$whatever_name</strong></p><ul>";

    $query = "SELECT FROM second_table WHERE whatever_id=1;
    $result mysql_query($query);
    while (
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    {
        
    $whatever_info stripslashes($row['whatever_info']);
        echo 
    "<li>$whatever_info</li>";
    }
    echo 
    "
    </ul>

    <br /><br />
    "

    Results:
    Howdy Doody
    • An American children's television program.
    • Western theme.
    • Red headed doll.


    Queries for id - 2
    PHP Code:
    $query "SELECT whatever_name FROM first_table WHERE whatever_id=2;
    $result = mysql_query($query);
    while (
    $row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        
    $whatever_name = stripslashes($row['whatever_name']);
    }
        echo "
    <p><strong>$whatever_name</strong></p><ul>";

    $query = "SELECT FROM second_table WHERE whatever_id=2;
    $result mysql_query($query);
    while (
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    {
        
    $whatever_info stripslashes($row['whatever_info']);
        echo 
    "<li>$whatever_info</li>";
    }
    echo 
    "
    </ul>

    <br /><br />
    "

    Results:
    Hopalong Cassidy
    • A fictional cowboy hero.
    • Total of sixty-six immensely popular films.


    Queries for id - 5 (id's 3 and 4 deleted)
    PHP Code:
    $query "SELECT whatever_name FROM first_table WHERE whatever_id=5;
    $result = mysql_query($query);
    while (
    $row = mysql_fetch_array($result, MYSQL_ASSOC))
    {
        
    $whatever_name = stripslashes($row['whatever_name']);
    }
        echo "
    <p><strong>$whatever_name</strong></p><ul>";

    $query = "SELECT FROM second_table WHERE whatever_id=5;
    $result mysql_query($query);
    while (
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    {
        
    $whatever_info stripslashes($row['whatever_info']);
        echo 
    "<li>$whatever_info</li>";
    }
    echo 
    "
    </ul>

    <br /><br />
    "

    Results:
    The Brady Bunch
    • A popular 1970's TV show.
    • Three boys, three girls, step mom, step dad, a maid and a dog.
    • Drove a station wagon to Grand Canyon.
    • Kids were easily disciplined by reasoning with them.


    And continue on down the list until all 100, 1000 or 10,000 entries have been listed. So you could end up with either 200, 2000 or 20,000 individual queries depending on how many entries you have.

    This doesn't seem like the most efficient use of PHP and MySQL, but it's the only way I know to display information from two tables in the way I would like for them to be displayed. If this can be done simply by writing a single join query I've been unsuccessful in discovering that formula.
  14. #8
  15. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1316
    short answer, you're doing it wrong.

    Longer answer, you would join the necessary tables.
    you would follow the pseudo code requinix outlined in their answer.

    In short you store a variable and compare the value of that variable to see if it has changed, if it has you update the variable and print it out along with the subsequent info below. If it has not, you don't print it out and just the info below.

    so in your case you get the shingles and colours for shingles using a join on the two tables. you store the value of shingle in a variable and print the first one out, you then print out the subsequent colours. Before each colour is printed you check to see if the variable for shingles has changed (to the next shingle company) and if not you print the next colour. if it has you update it, print out the new company and begin the process again.

    you only need a single database query to get all the information. you format it with your front end application.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    98
    Rep Power
    9
    I tested this in phpmyadmin and it seemed to be close to what I need:

    PHP Code:
    SELECT shingles.*, colors.c_name FROM shinglescolors WHERE shingles.s_id=colors.s_id ORDER BY shingle.s_id 
    When I tried it in on my page it returned the first shingle Nearly all the colors although they were mixed up.

    Of course, this was not using the formula that was suggested, as I'm having difficulty figuring out how to write it using which variables. But first things first... does that join look right?
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by DavidPr
    But first things first... does that join look right?
    it'll work fine, but you'd be better off learning to use explicit JOIN syntax rather than the deprecated FROM clause comma list style
    Code:
    SELECT shingles.*
         , colors.c_name 
      FROM shingles
    INNER
      JOIN colors 
        ON colors.s_id = shingles.s_id
    ORDER 
        BY shingle.s_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    98
    Rep Power
    9
    PHP Code:
    $query "SELECT shingles.s_name, colors.c_name FROM shingles, colors WHERE shingles.s_id=colors.s_id ORDER BY shingle.s_id "
    $result mysql_query($query);
    while(
    $row mysql_fetch_array($resultMYSQL_ASSOC))
    {
        
    $s_name $row['s_name'];
        echo 
    "<p><strong>$s_name</strong></p>";

        while(
    $row mysql_fetch_array($resultMYSQL_ASSOC))
        {
            
    $c_name $row['c_name'];

            echo 
    "<ul>";

                if (
    $c_name == '');
                {
                    echo 
    "";
                }
                else
                {
                    echo 
    "<li>$c_name</li>";
                }

            echo 
    "</ul>";
        }

    I know this isn't right. For one, the <ul></ul>'s are open but there won't be any <li></li>'s if there aren't any colors in the table for that s_id.

    I don't know how to write it to get it to layout the way I want it. Perhaps I should just do away with trying to make a bullet list since this seems to be a big part of my problem. I need possible multiple <li></li>'s but only one set of <ul></ul> - if there are colors listed for that shingle, and no <ul></ul> or <li></li>'s if there aren't any colors listed.

    I hate it because I'm using bullet lists elsewhere on the page and if I have to just use a * for a bullet it'll look a bunch of junk on a page. So I'll have to use * on all the page to match it in this section. Sucks not being able to get what you want.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    98
    Rep Power
    9
    What I'm getting using the script and styling/layout I posted above is:

    Owens Corning
    * Autumn Brown

    Owens Corning
    * Estate Gray

    Owens Corning
    * Driftwood

    Elk
    * Weathered Wood

    Elk
    * Charcoal Gray

    Elk
    * Forest Green

    I also changed the query to this as you suggested:

    PHP Code:
    $query "
    SELECT shingles.s_name, colors.c_name
    FROM  shingles
    INNER JOIN colors
    ON colors.s_id = shingles.s_id
    ORDER BY shingles.s_id
    "


    With this formula:
    PHP Code:
    previous shingle = empty
    for 
    each result {
        if 
    current shingle != previous shingle {
            if 
    previous shingle isn't empty {
                end the previous section of shingles
            }
            start a new section of shingles
            previous shingle = current shingle
        }
        print the color
    }
    end the previous section of shingles 
    How do I set the previous shingle variable at the top of this script if I haven't even set the first shingle variable yet? To tell you the truth the only thing I see in that script that I know what to do with is the part that says "print the color" - echo "$c_name";

    PHP Code:
    $previous_shingle "";
    // for each result 

        
    if ($current_shingle != $previous_shingle) {
            if (!empty(
    $previous shingle)) {
                echo 
    "";
            }
            
    // start a new section of shingles
            
    $previous_shingle $current_shingle
        
    }
        echo 
    "$c_name";
    }
    // end the previous section of shingles 
    Not sure how to assign shingles (which shingle) to which $current_shingle or $previous_shingle variable. I'm just not able to see what I'm supposed to put where and I'm guessing that I'm supposed to add more code to this code somewhere. Is that correct?

    I'm sorry, but I've never used a foreach clause in my life, mainly because I can't understand them. I just tried this and I get an error saying:

    syntax error, unexpected ')' on line... which is this - foreach($c_name)

    here's the code:
    PHP Code:
    if(!$c_name) {
    echo 
    "";
    } else {
    echo 
    "<ul>";

    foreach(
    $c_name) {
    echo 
    "<li>$c_name</li>";
    }

    echo 
    "</ul>";

    I tried foreach($c_name as $c_name)... but that threw an error also.

    From what I gather you can only use foreach with an array, so I tried to list the colors as an array and loop through them:

    PHP Code:
    if(!$c_name) {
    echo 
    "";
    } else {
    echo 
    "<ul>";

    $c_name = array($c_name)

    foreach(
    $c_name as $c) {
    echo 
    "<li>$c</li>";
    }

    echo 
    "</ul>";

    Once again this gave me:

    Owens Corning
    * Autumn Brown

    Owens Corning
    * Estate Gray

    Owens Corning
    * Driftwood

    Elk
    * Weathered Wood

    Elk
    * Charcoal Gray

    Elk
    * Forest Green

    I seem to be chasing my tail.
    Last edited by DavidPr; October 16th, 2012 at 12:52 AM.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    moving your thread to the php forum, because that's where you're having the trouble

    Comments on this post

    • requinix agrees : yeah...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    98
    Rep Power
    9
    I've been working with various foreach's and still nothing. I just tried running another query inside the while loop of the main join query hoping to get the colors for the current shingle to list under that shingle's name, but it listed only one shingle name and below that the same color four times and then threw an error about an unidentified s_id on the line that displays the s_id variable from the while loop of the join query.

    PHP Code:
    $query "SELECT c_name FROM colors WHERE s_id='$s_id'";
    $result mysql_query($query);
    $num_rows mysql_num_rows($result);

    if(
    $num_rows == '') {
        echo 
    "";
    } else {
        echo 
    "<ul>";

        for (
    $i=0$i<=$num_rows$i++) {
                echo 
    "<li>$c_name</li>";
            }

        echo 
    "</ul>";

    Result:
    Owens Corning
    • Autumn Brown
    • Autumn Brown
    • Autumn Brown
    • Autumn Brown

    At least I was able to get a bullet list under the shingle name that was listed only once, so that's some progress.

    I changed the $i=0 to $i=1 and it reduced the number of Autumn Browns listed in the above "Result:" to 3 which is how many colors there are in the colors table under that shingle id. However, I'm not sure that that is correct as most examples I've seen has it as "$i=0".
    Last edited by DavidPr; October 16th, 2012 at 10:59 AM.
  28. #15
  29. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,112
    Rep Power
    487
    DavidPr,
    To save on headache tablets, try getting a list of just the shingles first and then foreach shingle, do another query to return the colours.

    PHP Code:
    $sql "SELECT * FROM `shingles`";
    $result mysql_query($sql);
    while (
    $row mysql_fetch_assoc($result)) {
      
    // This WHILE loop will be performed for each shingle
      
    echo $row['s_name'] . "<br />";
      
    $csql "SELECT * FROM `colours` WHERE `s_id` = {$row['s_id']};";
      
    $cresult mysql_query($csql);
      while (
    $crow mysql_fetch_assoc($cresult)) {
        
    // This WHILE loop will be performed for each colour
        
    echo "<li>{$crow['c_name']}</li>";
      }
      echo 
    "<br />";

    Simples /squeek.

    ps. Please forgive my LI / UL / OL HTML bullet cr*p, I'm sure you can work out how to display it properly from that code above!
    Regards

    pps. The code is UNTESTED and USES DEPRECATED MYSQL library, consider using PDO etc etc etc
    "For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
    - George Orwell, 1984
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo