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:
[B]2012-11-25
Chicago[/B]
Mark
New York
John
Jane
[B]2012-11-26
Dallas[/B]
Lisa
Miami
Jenny
[B]2012-11-27
Miami[/B]
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.
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/>';
}
}
}
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:
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.
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?
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.
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.
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.
