Page 2 of 2 First 12
  • Jump to page:
    #16
  1. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2008
    Posts
    577
    Rep Power
    181
    Originally Posted by DavidPr
    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".
    When you count the number of rows you are getting 3. When you do your iteration you are counting 0,1,2,3... which is 4 numbers. You can start $i=1 or $i=0,$i<$num_rows instead of <=.

    And then $c_name must be being created somewhere before the query, because you aren't getting any query results using a regular variable like that. You need to loop through the results.

    PHP Code:

    while($row mysql_fetch_array($result)) 
    {    
       
    //use $row['c_name']

  2. #17
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by badger_fruit
    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.
    no

    no, no, no

    if you do this, your apps will be slow as cold glue

    disclaimer: i do coldfusion, not php, but any competent php developer knows how to loop over a query result set and do previous/next logic for the control breaks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #18
  5. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2008
    Posts
    577
    Rep Power
    181
    Just taking an untested stab at this... you can try...

    PHP Code:
    $prev_result '';

    $query "SELECT s_id,s_name,c_name FROM colors ORDER BY s_id";
    $result mysql_query($query);

    if(
    mysql_num_rows($result)>0)
    {
       while(
    $row mysql_fetch_array($result)) 
       {    
          if(
    $prev_result != $row['s_id'])
          {
              echo 
    $row['s_name'];
              echo 
    '<ul>';
          }
          
          echo 
    "<li>{$row['c_name']}</li>";

          if(
    $prev_result != $row['s_id'])
          {
              echo 
    '</ul>';
              
    $prev_result $row['s_id'];
          }
       }

  6. #19
  7. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,112
    Rep Power
    487
    Originally Posted by r937
    no

    no, no, no

    if you do this, your apps will be slow as cold glue

    disclaimer: i do coldfusion, not php, but any competent php developer knows how to loop over a query result set and do previous/next logic for the control breaks
    Although true, it was just a quick-fix suggestion.
    Had it not been 10 minutes before I finished work I'd have posted something much better, honest!!
    "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
  8. #20
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0
    Originally Posted by badger_fruit
    Although true, it was just a quick-fix suggestion.Had it not been 10 minutes before I finished work I'd have posted something much better, honest!!
    It's a workable solution. Sometimes multiple queries is preferred because it's easier to understand, implement and maintain than complex joins.

    Anyway...

    Here's my shot at it (Using Joins):
    PHP Code:
    $result mysql_query(
              
    "SELECT sh.s_id, sh.s_name, cl.c_name
              FROM  shingles as sh
              INNER JOIN colors as cl
              ON cl.s_id = sh.s_id
              ORDER BY sh.s_name"
    );

    $arr = array();

    while(
    $row mysql_fetch_assoc($result)) {
      if(isset(
    $arr[$row['s_id']])) {
        
    $arr[$row['s_name']] = $row['c_name'];
      }
      else {
        
    $arr[$row['s_name']][] = $row['c_name'];
      }
    }

    echo 
    "<ul>";

    foreach ( 
    $arr as $key => $val ) {
      echo 
    "<li><strong>{$key}</strong></li>\n";

        foreach ( 
    $val as $v ) {
          echo 
    "<ul><li>{$v}</li></ul>";
        }
    }

    echo 
    "</ul>"
  10. #21
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by MeroD
    Sometimes multiple queries is preferred because it's easier to understand, implement and maintain than complex joins.
    the query you posted is a "complex" join like a pimple on your nose is a life-threatening catastrophe
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #22
  13. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,112
    Rep Power
    487
    Originally Posted by r937
    the query you posted is a "complex" join like a pimple on your nose is a life-threatening catastrophe
    LOL ... what if you were allergic to said pimple?
    "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
  14. #23
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by badger_fruit
    LOL ... what if you were allergic to said pimple?
    then you shouldn't be writing code -- take up gardening instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #24
  17. No Profile Picture
    Permanently Banned
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    6
    Rep Power
    0
    PHP Code:

    $myFile = "testFile.txt";
    $fh = fopen($myFile, 'a');

    If we were to write to the file it would begin writing data at the end of the file.

    Comments on this post

    • Jacques3 disagrees : Can someone throw out this stupid SEO bot?
  18. #25
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    98
    Rep Power
    9
    I used what badger_fruit suggested and it works fine:
    PHP Code:
    $query "SELECT s_id, s_name FROM shingles ORDER BY s_name";
    $result mysql_query ($query);
    if (
    mysql_num_rows($result) == 0)
    {
        echo 
    "<p><a href='add_shingle.php>Add a Shingle Name</a></p>";
    }
    else
    {
        while(
    $row mysql_fetch_array($resultMYSQL_ASSOC))
        {
            
    $s_id stripslashes($row['s_id']);
            
    $s_name stripslashes($row['s_name']);

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


            
    $dsql "SELECT c_name FROM colors WHERE s_id='$s_id'";
            
    $dresult mysql_query($dsql);
            if (
    mysql_num_rows($dresult) == 0)
            {
                echo 
    "<a href='add_color.php'>Add Shingle Color</a>";
            }
            else
            {

                echo 
    "<ul>";

                while(
    $drow mysql_fetch_assoc($dresult))
                {
                    
    $c_name stripslashes($drow['c_name']);

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

                echo 
    "</ul>";
            }
        }

    Thank you all, I appreciate your help.

    DavidPr
  20. #26
  21. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2008
    Posts
    577
    Rep Power
    181
    Originally Posted by DavidPr
    I used what badger_fruit suggested and it works fine:

    Thank you all, I appreciate your help.

    DavidPr
    I'm glad it worked out, but just letting you know that is terrible practice.

    Comments on this post

    • badger_fruit agrees : Oddly enough, I agree; but I did say it was only a quick-fix stop-gap solution
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo