PHP MySQL Query group by items

Question

I'm getting this JSON result from mySQL with the following php query:

/* grab the posts from the db */
 $query = "SELECT * FROM ko_timetable";
 $result = mysql_query($query,$link) or die('Errant query:  '.$query);

 /* create one master array of the records */
 $posts = array();

if(mysql_num_rows($result)) {

while($post = mysql_fetch_assoc($result)) {
      $posts[] = array('post'=>$post);
    } 
}

JSON Result:

post =         {
"CLASS_LEVEL" = "Intro/General";
"CLASS_TYPE" = "Muay Thai";
"DAY_OF_WEEK" = Sunday;
ID = 19;
"ORDER_BY" = 5;
TIME = "1:00pm - 2:30pm";
};
}
{
post =         {
"CLASS_LEVEL" = "General/Intermediate/Advanced";
"CLASS_TYPE" = "Muay Thai Spar - Competitive";
"DAY_OF_WEEK" = Sunday;
ID = 27;
"ORDER_BY" = 5;
TIME = "6:00pm - 9:00pm";
};
},
{
post =         {
"CLASS_LEVEL" = "Fighters/Advanced/Intermediate";
"CLASS_TYPE" = "Fighters Training";
"DAY_OF_WEEK" = Monday;
ID = 1;
"ORDER_BY" = 1;
TIME = "9:30am - 11:00pm";
};

But how can I change this query to get this result group by "DAY_OF_WEEK". See example below, thanks for your help:

{
Sunday =         {
"CLASS_LEVEL" = "Intro/General";
"CLASS_TYPE" = "Muay Thai";
"DAY_OF_WEEK" = Sunday;
ID = 19;
"ORDER_BY" = 5;
TIME = "1:00pm - 2:30pm";
};
{
"CLASS_LEVEL" = "General/Intermediate/Advanced";
"CLASS_TYPE" = "Muay Thai Spar - Competitive";
"DAY_OF_WEEK" = Sunday;
ID = 27;
"ORDER_BY" = 5;
TIME = "6:00pm - 9:00pm";
};
},
{
Monday =         {
"CLASS_LEVEL" = "Fighters/Advanced/Intermediate";
"CLASS_TYPE" = "Fighters Training";
"DAY_OF_WEEK" = Monday;
ID = 1;
"ORDER_BY" = 1;
TIME = "9:30am - 11:00pm";
};

Thanks

Please stop writing new code with the ancient mysql_* functions. They are no longer maintained and community has begun the deprecation process. Instead you should learn about prepared statements and use either PDO or MySQLi. If you care to learn, here is a quite good PDO-related tutorial. — Madara's Ghost
– Madara's Ghost, Commented May 4, 2012 at 8:42
@Truth, the question is How can I change this query to get this result group by "DAY_OF_WEEK"?... — Jeremy Harris
– Jeremy Harris, Commented May 4, 2012 at 8:44
@HernandoZ: This is not JSON response. Please show us the genuine JSON output. — Madara's Ghost
– Madara's Ghost, Commented May 4, 2012 at 9:33

Robert Henry · Accepted Answer · 2012-05-04 09:56:39Z

2

You can use DAY_OF_WEEK as array index like this,

while($post = mysql_fetch_assoc($result)) {
      $posts[$posts["DAY_OF_WEEK"]] = array('post'=>$post);
    }

But remember that in the example you have shown the indexes of array are names of the days then the result of previous Sunday will be replaced with this weeks Sunday and so on so forth. You could also use a count variable to avoid replacing of keys with duplicate names, like this.

$count = 1;
while($post = mysql_fetch_assoc($result)) {
      $posts[$posts["DAY_OF_WEEK"].$count] = array('post'=>$post);
      $count++;
    }

answered May 4, 2012 at 9:56

Robert Henry

364 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HernandoZ Over a year ago

Thanks , is perfect , how can I tag "DAY_OF_WEEK" so it shows DAY = Monday ({ post = ..... Thanks

Robert Henry Over a year ago

Can you show an example how you want Json to be displayed because I am not getting your point.

Jimmy Collazos · Accepted Answer · 2012-05-04 08:42:39Z

1

change this:

while($post = mysql_fetch_assoc($result)) {
    $posts[ $post['DAY_OF_WEEK'] ] = $post;
}

answered May 4, 2012 at 8:42

Jimmy Collazos

1,9242 gold badges13 silver badges7 bronze badges

2 Comments

Jeremy Harris Over a year ago

That would overwrite duplicate DAY_OF_WEEK array keys. For example, if it adds $post to $posts['sunday'] and then tries to add a different post which also uses sunday, the first one is over-written. Instead try: $posts[$post['DAY_OF_WEEK']][] = $post;

HernandoZ Over a year ago

@acido69 Cillosis is right , cillosis is you summit your answer i'll accepted , thanks

Collectives™ on Stack Overflow

PHP MySQL Query group by items

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related