need help changing an array

[dazzathedrummer]

Hi,

 

I've got the follow array:

 

Query:

SELECT 
concat(monthname(gl_date),", ",year(gl_date)) as "monthYear",
concat(DATE_FORMAT(gl_date,"%d-%m-%Y"),", ",gl_venue) as "dateVenue",
DATE_FORMAT(gl_date,"%d-%m-%Y")as "date",
gl_venue as "venue",
gl_city as "city",
gl_postcode as "postcode",
gl_text as "description",
concat(DAYOFMONTH(gl_date), MONTHNAME(gl_date),".png") AS "imageName"
FROM tg_gig_list 
where
gl_date >= curdate()
and gl_publish = 1

order by gl_date

 

Array:

$array = array();
$array['gigs'] = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{ 
  $array['gigs'][] =$row;
}
$output = json_encode($array);

 

This returns JSON like this: -

{
    "gigs": [
        {
            "monthYear": "May, 2011",
            "dateVenue": "14-05-2011, Queen Victoria hall",
            "date": "14-05-2011",
            "venue": "Queen Victoria hall",
            "city": "Oundle",
            "postcode": "PE8 4EJ",
            "description": "",
            "imageName": "14May.png"
        },
        {
            "monthYear": "May, 2011",
            "dateVenue": "19-05-2011, O'Neill's",
            "date": "19-05-2011",
            "venue": "O'Neill's",
            "city": "Peterborough",
            "postcode": "PE1 1SQ",
            "description": "This one is another outing for Jason and Darren's acoustic duo, the pair will be playing a selection of their favourite songs as well as some tunes from the guards set.<br \/>\r\n<br \/>\r\n19:00 to 21:00",
            "imageName": "19May.png"
        },
        {.......next record

 

I'd like the data to be arranged by the 'monthYear' field....something like this...

 

{
    "gigs": [

"monthYear": "May, 2011"
        {
            "monthYear": "May, 2011",
            "dateVenue": "14-05-2011, Queen Victoria hall",
            "date": "14-05-2011",
            "venue": "Queen Victoria hall",
            "city": "Oundle",
            "postcode": "PE8 4EJ",
            "description": "",
            "imageName": "14May.png"
        },
        {......all gigs for 'May, 2011'

         }
"monthYear": "June, 2011"
        {....records for 'June, 2011'

 

 

so 'gigs'=>'monthYear'=>'other records'

 

 

I've tried all sorts of combinations of putting the '$row['monthYear']' field in different places in the array, but I seem to loose data by doing so for some reason.

 

any input greatly appreciated.

 

Darren

 

[dazzathedrummer]

This is as close as I've got.

 

If I do this....

$array = array();
$array['gigs'] = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{ 
  $array[$row['monthYear']] =$row;
}
$output = json_encode($array);

 

I get the right sort of result, but there's an empty set of square brackets at the start.....

 

{
    "gigs": [
        
    ],
    "May, 2011": [
        {
            "monthYear": "May, 2011",
            "dateVenue": "14-05-2011, Queen Victoria hall",
            "date": "14-05-2011",
            "venue": "Queen Victoria hall",
            "city": "Oundle",
            "postcode": "PE8 4EJ",
            "description": "",
            "imageName": "14May.png"
        },
        {
            "monthYear": "May, 2011",
            "dateVenue": "19-05-2011, O'Neill's",
            "date": "19-05-2011",
            "venue": "O'Neill's",
            "city": "Peterborough",
            "postcode": "PE1 1SQ",
            "description": "This one is another outing for Jason and Darren's acoustic duo, the pair will be playing a selection of their favourite songs as well as some tunes from the guards set.<br \/>\r\n<br \/>\r\n19:00 to 21:00",
            "imageName": "19May.png"
        },
        {
            "monthYear": "May, 2011",
            "dateVenue": "21-05-2011, The Old Bridge Hotel",
            "date": "21-05-2011",
            "venue": "The Old Bridge Hotel",
            "city": "Huntingdon",
            "postcode": "PE29 3TQ",
            "description": "",
            "imageName": "21May.png"
        },
        {
            "monthYear": "May, 2011",
            "dateVenue": "29-05-2011, The Bell",
            "date": "29-05-2011",
            "venue": "The Bell",
            "city": "Sawtry",
            "postcode": "PE28 5UY ",
            "description": "We'll be joining in the fun at the Bell as part of their charity day in aid of Help the heroes and CARESCO.<br \/>\r\nThis is an all day fun day event that starts at 1pm.<br \/>\r\n<br \/>\r\nWe'll be on stage at 9pm.",
            "imageName": "29May.png"
        }
    ],
    "June, 2011": [
        {
            "monthYear": "June, 2011",
            "dateVenue": "04-06-2011, ",
            "date": "04-06-2011",
            "venue": "",
            "city": "Cambridge",
            "postcode": "",
            "description": "",
            "imageName": "4June.png"
        },............
        {

 

 

How do I get rid of those brackets??

[dazzathedrummer]

Solved!

$array = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
{ 
  $array['gigs'][$row['monthYear']][] =$row;
}
$output = json_encode($array);

}

 

gives me what I want.