Create multidimensional arrays using single table

Question

I have a table like this;

**Year**   **Month**
2012        Jan
2012        Mar
2012        Apr
2013        Dec
2013        Nov

And I am trying to create an array from this table similar to below;

Array
(
    [2012] => Array(
              Jan, Mar, Apr),
    [2013] => Array(
              Dec, Nov)
);

so far I tried this code, but didn't get what I want,

    while ($row = mysql_fetch_array($result)){

          $new_array[] = array(
            'year' => $row['year'],
            'month' => $row['month'],
          );
}

[Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — krishna, Jun 02 '14 at 09:44
@krishna, you are right, mysql_* are very outdated and unsecure. In fact, they are deprecated. These functions are good for learning, but once you understand the basics, you should pick something more secure, like PDO or ORM from some framework. In worst case, you still can use mysqli_* functions, but I still don't recommend to do that. — Serge Kuharev, Jun 02 '14 at 09:50
@Megakuh I think you thought it as a question.It a link to learn why you should not use mysql_* function. — krishna, Jun 02 '14 at 09:52
@krishna, yeah, I noticed that later on and correct myself a little bit :-) — Serge Kuharev, Jun 02 '14 at 09:54

score 1 · Answer 1 · answered Jun 02 '14 at 09:43

1

Try this

while ($row = mysql_fetch_array($result)){
          $new_array[$row['year']][] = $row['month'];
}

answered Jun 02 '14 at 09:43

Sadikhasan

18,365
21
80
122

1

+1 I thinks its very simplest solution that can be ever achieved for this question – krishna Jun 02 '14 at 09:48
I didn't downvoted, but this is uncorrect solution. If problem of getting data can be solved on DATAbase layer, they it should be solved like that. – Serge Kuharev Jun 02 '14 at 09:52
@Megakuh it is not problem of getting data, its problem of storing the retrived data. – krishna Jun 02 '14 at 09:53
@Megakuh How can you say incorrect solution can you explain? – Sadikhasan Jun 02 '14 at 09:57
@Sadikhasan, I did already. From terms of syntax, etc. - your proposal is correct. From terms of software design - using GROUP_CONCAT function in DB query is much more preferable. If I did code review for such task, I would allow both solutions, but if I would have to pick better one, DB layer usage is much more right here. – Serge Kuharev Jun 02 '14 at 10:07

score 1 · Answer 2 · answered Jun 02 '14 at 09:47

1

A better solution would be using MySQL's GROUP_CONCAT and GROUP to create a result of a row per year, then use PHP's explode to split the GROUP_CONCATenated value to an array:

$query = mysql_query("SELECT `year`, GROUP_CONCAT(`month`) AS `months` FROM table GROUP BY `year`");
$new_array = array();
while ($row = mysql_fetch_array($result)) {
    $new_array[$row['year']] = explode(',', $row['months']);
}

answered Jun 02 '14 at 09:47

Diamondo25

769
1
8
21

`mysql_*` is depricated. You should not advice OP to use it – krishna Jun 02 '14 at 09:51
@RahilWazir i am not forcing OP to use it. It is specifying a good advice to him.And here i am mentioning to the one who answers this.Generally when you answer using deprecated function(without saying it is deprecated) it is a bad solution – krishna Jun 02 '14 at 10:02

score 1 · Answer 3 · answered Jun 02 '14 at 09:52

1

$new_array=array();
while ($row = mysql_fetch_array($result)){

      if(!isset($new_array[$row['year']])
        $new_array[$row['year']]=array();

       $new_array[$row['year']] = array_push($new_array[$row['year']],$row['month']);
}

answered Jun 02 '14 at 09:52

shabeer

1,064
9
17

GreyRoofPigeon · Answer 4 · 2014-06-02T10:00:00.390

0

Change your while loop to this:

while ($row = mysql_fetch_array($result)){
      $new_array[$row['year']][] = $row['month'];
}

edited Jun 02 '14 at 10:00

answered Jun 02 '14 at 09:43

GreyRoofPigeon

17,833
4
36
59

1

I really don't mind downvotes, but an explanation would be helpful. – GreyRoofPigeon Jun 02 '14 at 09:45
1

I downvoted when you first posted and it was wrong(you missed the `[]` before the equals sign), now it's too late for me to remove unless you edit again. – scragar Jun 02 '14 at 09:48
Yes, I saw that the minute I hit enter, and changed it right away. I guess you were quicker than my edit :). Made a small edit, hope you can undo it now. – GreyRoofPigeon Jun 02 '14 at 10:01
1

My vote is undone, but there's still someone else having downvoted you. – scragar Jun 02 '14 at 10:40

score 0 · Answer 5 · answered Jun 02 '14 at 09:59

0

Try the below line create the array in outside the loop.

 $newArray=array();
    while($row=mysql_fetch_array($(result))
    $newArray[$row['year']] = $row['month'];

answered Jun 02 '14 at 09:59

Selva

1,620
3
33
63

check my answer by print the array. foreach($newArray as $year => $month){ echo $year."=>".$month; } – Selva Jun 02 '14 at 10:07
see it will result like `2012=> jan` , `2012=> feb` , `2013=> Dec`. but op asked to create an array for all months of 2012 and 2013 seperately – krishna Jun 02 '14 at 10:10

Create multidimensional arrays using single table

5 Answers5