PHP - return SQL GROUP BY as array?

Question

I'm trying to return a MySQL query using GROUP BY as a array.

My table looks like this:

user_id | type
--------------------
1       | test
1       | test
2       | test
1       | hello
1       | helloworld

And my code and query:

$number_of_workouts = array();
$query = mysqli_query(connect(),"SELECT type, COUNT(*) FROM `log` WHERE `user_id` = $user_id GROUP BY type");
$number_of_workouts = mysqli_fetch_assoc($query);

return $number_of_workouts;

This code above isn't returning a array with all types listed, it will only return the number of one type(assuming that $user_id = 1.

How can I return the result of the query as an array?

Difficult to tell what you're asking. Your query returns more than one row.. but you're only fetching the first one. Do you want to loop over the results and fetch each row? Or do you want to change the query so it only returns one row? Your row has two columns, yet you fetch it into a variable that seems like it's the value of only one of those columns? Clear inputs, clear outputs. Work on the second one. — Mike B
– Mike B, Commented Dec 12, 2013 at 16:43

Daniel W. · Accepted Answer · 2013-12-12 16:55:53Z

1

mysqli_fetch_assoc($query);

fetches 1 row only from the resultset

If you want ALL rows from the resultset, you have to loop for each row and add it to a stack like:

$number_of_workouts = array();
$query = mysqli_query(connect(),
    "SELECT type, COUNT(*) AS count
     FROM `log`
     WHERE `user_id` = $user_id
     GROUP BY type"
);

$array = array();

while ($number_of_workouts = mysqli_fetch_assoc($query)) {
    $array[$number_of_workouts['type']] = $number_of_workouts['count'];
}

// Now you have all results like you want in the variable array:
print_r($array);

// print count of type test:
echo $array['test'];

Or you try out mysqli_fetch_all() (http://www.php.net/manual/en/mysqli-result.fetch-all.php)

(sorry for many updates)

edited Dec 12, 2013 at 16:55

answered Dec 12, 2013 at 16:45

Daniel W.

32.6k15 gold badges99 silver badges155 bronze badges

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

1 Comment

Abbe Over a year ago

So if I want to echo the number of the type test? from the array? print_r($array[0]); would do it but, it's not 100% that "test" is the first type in the table..: something like print_r($array['test']);?

Hearaman · Accepted Answer · 2013-12-12 16:52:53Z

0

You are fetching only first record here. Keep the fetching statement in while loop

        while($number_of_workouts = mysqli_fetch_assoc($query))
        {
            echo "<pre>";
            print_r($number_of_workouts);
        }

answered Dec 12, 2013 at 16:52

Hearaman

8,78813 gold badges45 silver badges58 bronze badges

Collectives™ on Stack Overflow

PHP - return SQL GROUP BY as array?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related