Select distinct entries only

[SarahBear]

So, I've been trying to get this query working and can't quite get it to work.  I'm trying to get an "array" of distinct browsers from the database, but it's only showing one of them.  There are 3 unique browsers in the table and only "Chrome 30" gets returned.  Here is the query:

SELECT DISTINCT `browser` AS `unique_browsers`,
     COUNT(DISTINCT `ip`) AS `unique_visitors`,
     COUNT(DISTINCT `country`) AS `unique_countries`,
     COUNT(`id`) AS `total_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Chrome%') AS `chrome_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Internet Explorer%') AS `ie_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Firefox%') AS `firefox_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Safari%') AS `safari_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Opera%') AS `opera_count`,
     (SELECT COUNT(`id`) FROM `table` WHERE `browser` NOT LIKE '%Chrome%' AND `browser` NOT LIKE '%Internet Explorer%' AND `browser` NOT LIKE '%Firefox%' AND `browser` NOT LIKE '%Safari%' AND `browser` NOT LIKE '%Opera%') AS `unknown_count`
FROM `table`
GROUP BY `browser` 

Everything works properly except the line:

DISTINCT `browser` AS `unique_browsers`,

unique_browsers only returns "Chrome 30" while there are 3 different browsers in the database.  A full return looks like:

Array ( [unique_browsers] => Chrome 30 [unique_visitors] => 3 [unique_countries] => 1 [total_count] => 6 [chrome_count] => 6 [ie_count] => 1 [firefox_count] => 1 [safari_count] => 0 [opera_count] => 0 [unknown_count] => 0 )

 

 Is there something I am missing to get this to work?

 

Edit: Sorry I messed the title of the post up.  It was supposed to say "distinct" instead of "duplicate".  I didn't pay much attention to it

Edited December 7, 2014 by SarahBear

[mac_gyver]

the problem is likely in your php code that's retrieving the data or that's displaying the result.

 

have you ran this query directly against your database, using a tool like phpmyadmin, so that you know if it returns the correct result?

 

because you are grouping by the browser, there's no need for the initial DISTINCT keyword (which removes duplicate rows from the result set), since you will have only one row for each different browser value in the result set.

[Barand]

With a few test records

mysql> SELECT * FROM browsers;
+----+---------+-----------------+---------+
| id | browser | ip              | country |
+----+---------+-----------------+---------+
|  1 | Chrome  | 123.123.123.123 | UK      |
|  2 | Chrome  | 123.123.124.124 | UK      |
|  3 | Chrome  | 123.123.123.125 | USA     |
|  4 | Firefox | 12.12.12.12     | FRA     |
|  5 | Safari  | 23.23.23.23     | UK      |
|  6 | Safari  | 25.25.25.25     | USA     |
+----+---------+-----------------+---------+

Your query gave me

 

[SarahBear]

have you ran this query directly against your database, using a tool like phpmyadmin, so that you know if it returns the correct result?

I had not thought of it being a PHP issue so I did not.  When running it through phpmyadmin, it returns the correct results.  It appears that this is a PHP issue.

 

I am returning the results via:

return mysqli_fetch_assoc($sql);

Yet when using:

while($row = mysqli_fetch_assoc($sql))
    print_r($row);

It is apparent that the results exist, but in 3 different arrays with varying results being:

Array ( [unique_browsers] => Chrome 30 [unique_visitors] => 3 [unique_countries] => 1 [total_count] => 6 [chrome_count] => 6 [ie_count] => 1 [firefox_count] => 1 [safari_count] => 0 [opera_count] => 0 [unknown_count] => 0 )
Array ( [unique_browsers] => Firefox 33 [unique_visitors] => 1 [unique_countries] => 1 [total_count] => 1 [chrome_count] => 6 [ie_count] => 1 [firefox_count] => 1 [safari_count] => 0 [opera_count] => 0 [unknown_count] => 0 )
Array ( [unique_browsers] => Internet Explorer 6 [unique_visitors] => 1 [unique_countries] => 1 [total_count] => 1 [chrome_count] => 6 [ie_count] => 1 [firefox_count] => 1 [safari_count] => 0 [opera_count] => 0 [unknown_count] => 0 )

Is it possible to get all of the `unique_browser` results into a single array while using the correct data that is seen in the first array?  I have been trying to use a combination of array_merge(), _push(), etc. but cannot seem to get anything working that does not use excessive memory.  I have also tried looking at all of the other mysqli_fetch_* functions but none seem to do the trick.

[Barand]

Your query is duplicating information.

• The counts from the subqueries are the same in every row.
• The count from the browser XXX subquery is always the same as the total count in the XXX row

so you would get as much information by omitting the subqueries altogether.

[SarahBear]

I'm not sure I know what you mean.  I think you mean to omit the subqueries and just count the types that get returned by unique browsers?  i.e.: counting how many start with Firefox, Safari, IE, etc.

 

I did not think of that, and I will do that from now on.  But still there is some issue where the data is being duplicated.  I have removed the subqueries.

 

Is there a way for it to return the unique_browsers as an array?  The issue appears to be caused by the GROUP BY clause.  Also, it appears that with the GROUP BY clause, it is excluding 2 results in the `total_count` due to 3 arrays being returned.  I did not notice it before, but there are actually 8 entries in the database, and it is only returning 6 for the total in the first array.

 

The query now looks like:

SELECT
    `browser` AS `unique_browsers`,
    COUNT(DISTINCT `ip`) AS `unique_visitors`,
    COUNT(DISTINCT `country`) AS `unique_countries`,
    COUNT(`id`) AS `total_count`
FROM `table`
    GROUP BY `browser`

Edited December 7, 2014 by SarahBear

[SarahBear]

This issue has been solved.  Thank you for the help you two