SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advanced looping techniques with MySQL array

    So,

    I'm just beginning to learn PHP and know how to do a simple foreach loop to echo out data from a MySQL query set array. Nothing weird there, but I have no idea on how to do it a bit more advanced

    My MySQL set contains data from three tables: Name, city and date. It's sorted with ORDER BY date, city.

    What I would like to output is different "blocks" of data depending on the city and the date. Let's say we have the following data:

    Mark, Chicago, 2012-11-25
    John, New York, 2012-11-25
    Jane, New York, 2012-11-25
    Lisa, Dallas, 2012-11-26
    Jenny, Miami, 2012-11-26
    Nina, Miami, 2012-11-27

    What I would like the result to look like when the loop is done is something like this:

    2012-11-25

    Chicago


    Mark

    New York

    John
    Jane

    2012-11-26

    Dallas


    Lisa

    Miami

    Jenny

    2012-11-27

    Miami


    Nina

    _______

    If anyone can point me in the right direction here or maybe just give me a link to something similar, it would be great. But don't just tell me exactly what to do, then I won't learn anything. :)

  2. #2
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    A good practice when you're dealing with situations like this is to break the process down into a two step.

    It might be tempting to do it all within the output loop, but this creates a lot of headache as well as make future maintenance (changes) difficult.

    What you first want to do is break this down into an array that is easier to digest. A good way to do this is to take advantage of PHP's array index and it's flexibility.

    You can do something like this (you'd want to actually check for data and all that but this will give you an idea):

    PHP Code:

    # organizes the database result set into a digestible multi dimensional array
    while ($row fetch($rs))
    {
        
    $list$row['date'] ][ $row['city'] ][] = $row['name'];
    }

    # now output is easy

    foreach ($list as $date=>$row)
    {
        echo 
    $date.'<br/>';

        foreach (
    $row as $city => $users)
        {
            echo 
    $city.'<br/>';
            
            foreach (
    $users as $user)
            {
                    echo 
    $user.'<br/>';
             }
        }


  3. #3
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wonshikee View Post
    A good practice when you're dealing with situations like this is to break the process down into a two step.

    It might be tempting to do it all within the output loop, but this creates a lot of headache as well as make future maintenance (changes) difficult.

    What you first want to do is break this down into an array that is easier to digest. A good way to do this is to take advantage of PHP's array index and it's flexibility.

    You can do something like this (you'd want to actually check for data and all that but this will give you an idea):

    PHP Code:

    # organizes the database result set into a digestible multi dimensional array
    while ($row fetch($rs))
    {
        
    $list$row['date'] ][ $row['city'] ][] = $row['name'];
    }

    # now output is easy

    foreach ($list as $date=>$row)
    {
        echo 
    $date.'<br/>';

        foreach (
    $row as $city => $users)
        {
            echo 
    $city.'<br/>';
            
            foreach (
    $users as $user)
            {
                    echo 
    $user.'<br/>';
             }
        }

    Thanks for helping wonshikee

    If you have time could you please explain this? I mean, it works (there are some things which I need to figure out as how to put an extra couple of line breaks after the last name before a new date...), but what is going on haha?

    You write that we first create a multi dimensional array of the result set... Maybe I need to read exactly what happens if I just do a "regular" array? Like this:

    foreach ($result as $row) {
    $list[] = array('name' => $row['name'], 'city' => $row['city'], 'date' => $row['date'])
    }

    To me it looks like we're doing something total different here? And I don't really understand the loops either. Or is it so that it is first echoing a date then a loop within the date to echo out the cities under that date, and finally another loop to echo out the names under that one city? Maybe I understood that right now?

    What I do not understand is what's going on with the =>, i've only used foreach ($result as $row) earlier and not the =>. And how do we get the names at all? I don't see it in the loop. So many questions.

  4. #4
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your data comes from 3 different tables, I would rewrite the sql query with a JOIN and let the Database do the work.
    The Path of excess leeds to the tower of wisdom (W. Blake)

  5. #5
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pompopom View Post
    If your data comes from 3 different tables, I would rewrite the sql query with a JOIN and let the Database do the work.
    No, it's a single table for now.

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Here I am spoofing your result set from your table(s) as a PHP multi array called $rows.

    PHP Code:
    $rows = array(
    => array(
    'name'=>'Mark'
    'city'=>'Chicago'
    'date'=>'2012-11-25'
    ),
    => array(
    'name'=>'John'
    'city'=>'New York'
    'date'=>'2012-11-25'
    ),
    => array(
    'name'=>'Jane'
    'city'=>'New York'
    'date'=>'2012-11-25'
    ),
    => array(
    'name'=>'Lisa',
    'city'=> 'Dallas'
    'date'=>'2012-11-26'
    ),
    ); 
    Here's how you'd loop through that result set outputting a bold line every time it got to a new date, or city, with comments you can remove if you like.

    PHP Code:
    // set 2 temp vars
    $last_date "";
    $last_city "";

    // go through the results
    foreach ($rows as $row){

    // only show date if its a new one
    if( $row['date'] !== $last_date)
        echo 
    '<b>' $row['date'] . '</b><br />';
    $last_date $row['date'];   // update the var

    // only show city  if its a new one
    if( $row['city'] !== $last_city)
        echo 
    '<b>' $row['city'] . '</b><br />';
    $last_city $row['city'];   // update the var


    // always echo the name
    echo $row['name'] . '<br />';

    Gives:

    2012-11-25
    Chicago
    Mark
    New York
    John
    Jane
    2012-11-26
    Dallas
    Lisa

  7. #7
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    Here I am spoofing your result set from your table(s) as a PHP multi array called $rows.

    PHP Code:
    $rows = array(
    => array(
    'name'=>'Mark'
    'city'=>'Chicago'
    'date'=>'2012-11-25'
    ),
    => array(
    'name'=>'John'
    'city'=>'New York'
    'date'=>'2012-11-25'
    ),
    => array(
    'name'=>'Jane'
    'city'=>'New York'
    'date'=>'2012-11-25'
    ),
    => array(
    'name'=>'Lisa',
    'city'=> 'Dallas'
    'date'=>'2012-11-26'
    ),
    ); 
    Here's how you'd loop through that result set outputting a bold line every time it got to a new date, or city, with comments you can remove if you like.

    PHP Code:
    // set 2 temp vars
    $last_date "";
    $last_city "";

    // go through the results
    foreach ($rows as $row){

    // only show date if its a new one
    if( $row['date'] !== $last_date)
        echo 
    '<b>' $row['date'] . '</b><br />';
    $last_date $row['date'];   // update the var

    // only show city  if its a new one
    if( $row['city'] !== $last_city)
        echo 
    '<b>' $row['city'] . '</b><br />';
    $last_city $row['city'];   // update the var


    // always echo the name
    echo $row['name'] . '<br />';

    Gives:

    2012-11-25
    Chicago
    Mark
    New York
    John
    Jane
    2012-11-26
    Dallas
    Lisa
    That's an interesting solution and i can actually understand what's going om here. Thanks for that.

    The array in this examle, is that how a mysql query set "looks" like?

    Another question that comes to mind is which of these two is the best one? Lets say that i have 300 rows in my table result. Or is that so little that it really doesnt matter?
    Last edited by madmats; Nov 14, 2012 at 06:12. Reason: Forgot one question

  8. #8
    SitePoint Evangelist pompopom's Avatar
    Join Date
    Feb 2004
    Location
    Huldenberg (Belgium)
    Posts
    426
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by madmats View Post
    My MySQL set contains data from three tables: Name, city and date. It's sorted with ORDER BY date, city.
    Quote Originally Posted by madmats View Post
    No, it's a single table for now.
    ?

    Besides that: You mention "for now". It's important you define a good datamodel for your db prior to start coding. If you develop code for a certain data model (one table with all the data) and later during the process you have to/need to change your datamodel. You 're gonna have to rewrite lot's of things => waste of time. A good, well considered database structure saves lot's of programming/processing time on the application side.
    The Path of excess leeds to the tower of wisdom (W. Blake)

  9. #9
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pompopom View Post
    ?

    Besides that: You mention "for now". It's important you define a good datamodel for your db prior to start coding. If you develop code for a certain data model (one table with all the data) and later during the process you have to/need to change your datamodel. You 're gonna have to rewrite lot's of things => waste of time. A good, well considered database structure saves lot's of programming/processing time on the application side.
    Sorry for that. I just mixed up the terms for it. It's one table and data from three columns.

    And yeah, you're right. But this is a quite simple thing I want to do, and I could always just change the MySQL query to fit the loop? I mean, If I move something to another table, like I should with an address column - can't I just use AS and things like that in the query to match what I'm already using? Thing is I need this done as soon as possible, but I do understand that it's a stupid choice to do something half-assed from the beginning.

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    The array in this examle, is that how a mysql query set "looks" like?
    Yep.

    It handling the equivalent of
    Code:
    SELECT `date`, `city`, `name` from MYTABLE
    WHERE something = something
    ORDER BY `date`, `city`
    LIMIT 4
    PHP Code:
    $rows mysql_fetch_array($qry); 
    You'll notice I had to backtick `quote` the column names because of potential clashes with Mysql reserved words, ideally you'd avoid this by judicious choice of column names.

  11. #11
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    Yep.

    It handling the equivalent of
    Code:
    SELECT `date`, `city`, `name` from MYTABLE
    WHERE something = something
    ORDER BY `date`, `city`
    LIMIT 4
    PHP Code:
    $rows mysql_fetch_array($qry); 
    You'll notice I had to backtick `quote` the column names because of potential clashes with Mysql reserved words, ideally you'd avoid this by judicious choice of column names.
    Great. Feels good when you get something even though it's simple. Just gotta figure out wonshikee's method of doing this.

    My column names are in Swedish so it should be fine. Just translated so it would make more sense. Thanks for explaining!

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Feels good when you get something even though it's simple.
    Nice one, very true. The thing is that "simple" is a moving target. A thing you find simple today would had left you stumped a year ago. Sometimes the way something is explained on a certain day just cuts the *rap, and lands in the right receptors into your brain.

    I wish it was happening to me more often


Bookmarks

Posting Permissions

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