MySQL searching for all elements in an array

Question

I'm struggling extracting all the information I need from a MySQL query. I am almost there but need help just getting seperate values.

My aim is to gather any company in the table which has all the products provided by an array. The information is provided by the end user through the use of AJAX.

The query also needs to be slightly fluid as the array in which the product_ids are provided in, can be infinite.

My query is as follows:

SELECT `company_id`, `product_id`, COUNT(*) as c
FROM `products`
WHERE `product_id` IN (2495, 2403) 
GROUP BY `company_id`
HAVING c = 2

The output provided by this query is (A):

| company_id | product_id | c |
-------------------------------
| 1234       |  2495      | 2 |

But what I am after is (B):

| company_id | product_id | c |
-------------------------------
| 1234       |  2495      | 2 |
| 1234       |  2403      | 2 |

or even better (C)

| company_id | product_id A | product_id B | c |
------------------------------------------------
| 1234       |  2495        | 2403         | 2 |

Any help on this would be appreciated, so thanks in advance!

Answer 1

Result A is a result of grouping your query on the company_id.

You'll get result B by taking the GROUP BY away. I'm no expert in MySQL, I can't tell you how to count it otherwise (in the same query). But since you tagged PHP too, you might consider counting the results with PHP.

For result C you can look in to GROUP_CONCAT, check this answer.

Answer 2

Thanks goes to LinkinTED for the GROUP_CONCAT suggestion, my final query was:

SELECT `company_id`, GROUP_CONCAT(`product_id`) as _product_id, COUNT(*) as c
FROM `products`
WHERE `product_id` IN (2495,2403)
GROUP BY `company_id`
HAVING c=2

The output was like C apart from there werent individual columns for product A and B