Changing the way SQL results are nested in a PHP Array?

Question

The PHP & SQL

            list( $since, $start_index, $count ) = func_get_args();
            $query =    "SELECT 
                            size_groups.id, size_groups.name, GROUP_CONCAT( size_values.names SEPARATOR ';' ) AS `display_name`, GROUP_CONCAT( size_values.vals SEPARATOR ';' ) AS `values`, GROUP_CONCAT( size_count.count SEPARATOR ';' ) AS `count`
                        FROM size_groups
                        LEFT JOIN
                            (SELECT CONCAT_WS( ', ', sizes.size ) AS names, CONCAT_WS( ', ', sizes.id ) AS vals, sizes.id AS ids, sizes.group_id FROM sizes)
                            AS size_values ON size_values.group_id = size_groups.id
                        LEFT JOIN
                            (SELECT CONCAT_WS( ', ', COUNT( product_to_sizes.size_id ) ) AS count, product_to_sizes.size_id FROM product_to_sizes)
                            AS size_count ON size_count.size_id = size_values.ids
                        WHERE size_groups.timestamp >= '%s' AND id = %s
                        GROUP BY size_groups.id
                        ORDER BY size_groups.timestamp ASC LIMIT %s, %s";

            return $this->result( $this->connection->query( $query, $since, $category_id, $start_index, $count ) );

And

   public function result( $data ) {

        $result = array();
        while ($row = $data->fetch_assoc())
            array_push( $result, $row ); 

        return $result;

    }

outputs an array like this

 Array
 (
    [0] => Array
        (
            [id] => 21
            [name] => Sleeve length
            [display_name] => 33;34;35;36;37;38
            [values] => 94;95;96;97;98;99
            [count] => 
        )

    [1] => Array
        (
            [id] => 22
            [name] => Waist
            [display_name] => 40;30;32;34;36;38
            [values] => 105;100;101;102;103;104
            [count] => 
        )
 )

but I want it to be like this

Array
(
    [0] => Array
        (
            [id] => 21
            [name] => Sleeve length
            [values] => Array(
                  Array(
                          [display_name] => 33
                          [value] => 94
                          [count] => 5
                       ),
                  Array(
                          [display_name] => 34
                          [value] => 95
                          [count] => 31
                       )
               )
        ),
        [1] => Array
             (
               etc
             )
)

Table structure for sizes

+-----+--------+----------+----------+---------------------+
| id  | size   | alt_size | group_id | timestamp           |
+-----+--------+----------+----------+---------------------+
|  81 | Small  |          |       19 | 2011-07-15 17:26:01 |
|  82 | Medium |          |       19 | 2011-07-15 17:26:10 |
|  83 | Large  |          |       19 | 2011-07-15 17:26:14 |
|  84 | 14.5   |          |       20 | 2011-07-15 17:28:01 |
|  85 | 15     |          |       20 | 2011-07-15 17:28:05 |
|  86 | 15.5   |          |       20 | 2011-07-15 17:28:08 |
|  87 | 16     |          |       20 | 2011-07-15 17:28:13 |
|  88 | 16.5   |          |       20 | 2011-07-15 17:28:18 |
|  89 | 17     |          |       20 | 2011-07-15 17:28:21 |
|  90 | 17.5   |          |       20 | 2011-07-15 17:28:24 |
|  91 | 18     |          |       20 | 2011-07-15 17:28:27 |
|  92 | 19     |          |       20 | 2011-07-15 17:28:32 |
|  93 | 20     |          |       20 | 2011-07-15 17:28:36 |
|  94 | 33     |          |       21 | 2011-07-15 17:28:49 |
|  95 | 34     |          |       21 | 2011-07-15 17:28:54 |
|  96 | 35     |          |       21 | 2011-07-15 17:28:56 |
|  97 | 36     |          |       21 | 2011-07-15 17:29:00 |
|  98 | 37     |          |       21 | 2011-07-15 17:29:05 |
|  99 | 38     |          |       21 | 2011-07-15 17:29:15 |
| 100 | 30     |          |       22 | 2011-07-15 17:29:42 |
| 101 | 32     |          |       22 | 2011-07-15 17:29:46 |
| 102 | 34     |          |       22 | 2011-07-15 17:29:51 |
| 103 | 36     |          |       22 | 2011-07-15 17:30:03 |
| 104 | 38     |          |       22 | 2011-07-15 17:30:06 |
| 105 | 40     |          |       22 | 2011-07-15 17:30:10 |
| 106 | 32     |          |       23 | 2011-07-15 17:30:52 |
| 107 | 34     |          |       23 | 2011-07-15 17:30:56 |
| 108 | 36     |          |       23 | 2011-07-15 17:31:00 |
| 109 | 38     |          |       23 | 2011-07-15 17:31:04 |
+-----+--------+----------+----------+---------------------+

For size_groups

+----+---------------+---------------------+
| id | name          | timestamp           |
+----+---------------+---------------------+
| 19 | Size          | 2011-07-15 17:25:45 |
| 20 | Collar size   | 2011-07-15 17:26:50 |
| 21 | Sleeve length | 2011-07-15 17:27:00 |
| 22 | Waist         | 2011-07-15 17:27:28 |
| 23 | Leg           | 2011-07-15 17:27:38 |
+----+---------------+---------------------+

And products_to_sizes

+------+------------+---------+-------------+----------+---------------------+
| id   | product_id | size_id | stock_level | theorder | timestamp           |
+------+------------+---------+-------------+----------+---------------------+
| 8050 |        683 |     109 |           0 |        0 | 2011-07-16 10:45:30 |
| 8049 |        683 |     108 |           0 |        0 | 2011-07-16 10:45:30 |
| 8048 |        683 |     107 |           0 |        0 | 2011-07-16 10:45:30 |
| 8099 |        679 |     109 |           0 |        0 | 2011-07-16 10:48:30 |
| 8098 |        679 |     108 |           0 |        0 | 2011-07-16 10:48:30 |
| 8097 |        679 |     107 |           0 |        0 | 2011-07-16 10:48:30 |
| 8096 |        679 |     106 |           0 |        0 | 2011-07-16 10:48:30 |
| 8095 |        679 |     105 |           0 |        0 | 2011-07-16 10:48:30 |
| 8094 |        679 |     104 |           0 |        0 | 2011-07-16 10:48:30 |
| 8093 |        679 |     103 |           0 |        0 | 2011-07-16 10:48:30 |
+------+------------+---------+-------------+----------+---------------------+

Any ideas?

You do know that without custom looping and constructing of the array you won't be able to have the output the way you described it? — N.B.
– N.B., Commented Oct 20, 2011 at 11:02

designosis · Accepted Answer · 2011-10-20 11:28:15Z

1

Easy enough to fix via PHP. Assuming $items contains your records array ...

foreach ($items as $itemkey=>$item) {
    if ($item['display_name']) {

        // prep vars
        $displaynames = explode(';',$item['display_name']);
        $values = explode(';',$item['values']);
        $count = explode(';',$item['count']);
        unset($items[$itemkey]['values']);

        // iterate
        $i=0;
        foreach ($displaynames as $null) {
            $items[$itemkey]['values'][$i] = array(
                'display_name' => $displaynames[$i],
                'value'        => $values[$i],
                'count'        => $count[$i]
            );
            $i++;
        }

    } else {
        $items[$itemkey]['values'] = null;
    }
}

edited Oct 20, 2011 at 11:28

answered Oct 20, 2011 at 11:09

designosis

5,2631 gold badge42 silver badges58 bronze badges

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

2 Comments

designosis Over a year ago

Or better yet, show me the table syntax and sample data for the tables sizes, size_groups, size_values and size_count. Perhaps there is a MySQL-only way.

designosis Over a year ago

Cool, I gave it a go. Thought for sure the current MySQL query would be a bear, but it's surprisingly efficient. So I'd leave that as is, and "fix it in post" with the above PHP, doing a FOREACH on the $result, which will iterate the array and rebuild it to your requirements. Usually it's best to nail your MySQL queries, but sometimes PHP is the right tool for the job :)

Collectives™ on Stack Overflow

Changing the way SQL results are nested in a PHP Array?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related