Need help with MySql Query

[jecasc]

I have the following database structure:

id (auto increment) | product_id |product_category

1 | 29 | 11
2 | 29 | 14
3 | 29 | 7
4 | 30 | 11
5 | 30 | 7
6 | 32 | 11
7 | 32 | 14
8 | 32 | 26
9 | 32 | 7

...

Now I need for example all product_id where the category_id is 11, 14 and 7. So for my example above the result should be: 29, 32.

Does anybody have a hint that could point me in the right direction how I could go about this? Can I do this with one single mysql query?

[topr8]

edit ... didn't read question properly!

[tascam424]

I hope i understood correctly ..
I am a total newbie, unskilled coder, just learning. But if this is wrong then i hope that i will learn in the process .. So try this and tell me if it works and i am sure lots of people will then tell us a better way to do it . but it's all about personal development ..

<?php
$dbhost = '****';
$dbuser = '*****';
$dbpass = '******';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = '#*$!xx';
mysql_select_db($dbname);

$result = mysql_query("SELECT product_id FROM YOURTABLE
WHERE category_id='11,14,7'");

echo "<table border='1'>
<tr>
<th>Product ID</th>
</tr>";


while ($row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td>" . $row['product_id'] . "</td>";
echo "</tr>";
}

echo "</table>";

Just change YOURTABLE name .. this should be interesting.

[Frank_Rizzo]

tascam that won't work in this case for three reasons. The first is you are filtering from category_id when you must mean product_category.

Secondly your where statement is not correct. You are probably working on the lines of

category_category IN(11,14,7)

which means find all records where the category_id is any one of those numbers.

Thirdly the request is to find records where a product_category contains 14, 11 AND 7 not or. You want to find those IDs that have all 3 numbers somewhere in the table.

[jecasc]

Thanks for the effort so far. The problem is: I have a solution that works somehow. A brute force solution, so to speak. I make simple SQL queries in a foreach loop:

SELECT products_id from table WHERE products_class = 11

Then I save the results in an array like this with the product_id as array key:
array[$products_id] = 1

then I make the query for the second category

SELECT products_id from table WHERE products_class = 14

and set

array[$products_id] = array[$products_id]+1

and so on. N times (in above example n would be 3). And then I check which arrays have a value of n (=matched everytime) and this are my results. Problem is: The solution is far too slow. In my implementation and with the number of entries in my database the webpage took 8 seconds to load and produced more than 5000 queries in the database. Because sometims n can be up to 6. So I wonder if there is a more elegant solution whithout the loop. Just a single Mysql query perhaps.

[tascam424]

I don't mind being wrong when someone takes the time to explain why .. Thanks Frank_Rizzo .. now i am learning !

[Frank_Rizzo]

We all started somewhere!

jecasc. That's the kind of method I would come up with. More from a programming background, rather than an SQL background.

There is probably a select / subselect solution but the problem with this is your n is not fixed so it could get messy.

I would filter for 11,4,7 first and then loop through to find full candidates.

$result = mysql_query("SELECT product_id FROM YOURTABLE
WHERE category_id in(11,14,7) order by product_id, category_id");

this will ensure we only retrieve records that definitely have at least one of the ids thus making your select smaller and easier to manage for your array method.

Now loop through the data for each product_id and do a one shot +1 if there is a match for 14, 11, 7. At the end of the loop just find the array records that have 3 matches.

This should be easy to scale for n.

[jecasc]

Thanks for the suggestion, I'll try it out and check how it performs.

I wonder however if maybe the table design is flawed from the beginning.

The only other way to assign multiple product_id to the categories I can think of would be something like this:

product_id | product_category
29 | 11,14,7
35 | 12,8,1

Maybe I could then use some MySQL string functions to get the desired results, however I am not very familiar with those.

[Frank_Rizzo]

Yes that could be an option but you have to watch the separators.

If you set product_category to a varchar(n) field you can then do this:

SELECT product_id FROM table WHERE product_category LIKE "%11" AND product_category LIKE "%14" AND product_category LIKE "%7"

But that would be incorrect because it would find records like this:

11,14,7
211,14,7
11,14,75

You would need to ensure that all the stored product_category ids have separators around them such as a comma or any other character

~11~14~7~
~211~14~7~
~11~14~75~

Now your select can look like this:

SELECT product_id FROM table WHERE product_category LIKE "~11~," AND product_category LIKE "~14~" AND product_category LIKE "~7~"

You could just use spaces if you wish

11 14 7
211 14 7
11 14 75

but I would use a specific character as it will be easier to spot mistakes.

---

As I said earlier though there is probably a direct mysql statement, which would extract your data as in your opening post. Something on the lines of subselects and unions.

[swa66]

I'd not mess up my data structure cause a query seems difficult at first looks. Data normalization should rule.

AFAIK (I'm by far not a SQL specialist) this is typically solved with an inner join of the table with itself.

e.g.:

mysql> select * from tt;
+----+------------+------------------+
| id | product_id | product_category |
+----+------------+------------------+
|  1 |         29 |               11 |
|  2 |         29 |               14 |
|  3 |         29 |                7 |
|  4 |         30 |               11 |
|  5 |         30 |                7 |
|  6 |         32 |               11 |
|  7 |         32 |               14 |
|  8 |         32 |               26 |
|  9 |         32 |                7 |
+----+------------+------------------+
9 rows in set (0.00 sec)

mysql> select t1.id, t1.product_id 
from tt as t1 
inner join tt as t2 
on t1.product_id = t2.product_id 
inner join tt as t3 
on t1.product_id = t3.product_id 
where t1.product_category=11 
and t2.product_category=14 
and t3.product_category=7;
+----+------------+
| id | product_id |
+----+------------+
|  1 |         29 |
|  6 |         32 |
+----+------------+
2 rows in set (0.00 sec)

I'm not saying anything is optimal performance wise etc. but this at least works and probably can be enhanced from there on.

[jecasc]

Thanks swa66, that was what I was looking for. I tried it out and it performs much better than the solution with the arrays.