Advanced looping techniques with MySQL array

[madmats]

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. :)

[wonshikee]

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):

# 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/>';
         }
    }
} 

[madmats]

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):

# 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.

[pompopom]

If your data comes from 3 different tables, I would rewrite the sql query with a JOIN and let the Database do the work.

[madmats]

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.

[Cups]

Here I am spoofing your result set from your table(s) as a PHP multi array called $rows.

$rows = array(
0 => array(
'name'=>'Mark', 
'city'=>'Chicago', 
'date'=>'2012-11-25'
),
1 => array(
'name'=>'John', 
'city'=>'New York', 
'date'=>'2012-11-25'
),
2 => array(
'name'=>'Jane', 
'city'=>'New York', 
'date'=>'2012-11-25'
),
3 => 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.

// 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

[madmats]

Here I am spoofing your result set from your table(s) as a PHP multi array called $rows.

$rows = array(
0 => array(
'name'=>'Mark', 
'city'=>'Chicago', 
'date'=>'2012-11-25'
),
1 => array(
'name'=>'John', 
'city'=>'New York', 
'date'=>'2012-11-25'
),
2 => array(
'name'=>'Jane', 
'city'=>'New York', 
'date'=>'2012-11-25'
),
3 => 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.

// 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?

[pompopom]

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

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.

[madmats]

?

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.

[Cups]

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

Yep.

It handling the equivalent of

SELECT `date`, `city`, `name` from MYTABLE
WHERE something = something
ORDER BY `date`, `city`
LIMIT 4

$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.

[madmats]

Yep.

It handling the equivalent of

SELECT `date`, `city`, `name` from MYTABLE
WHERE something = something
ORDER BY `date`, `city`
LIMIT 4

$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!

[Cups]

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