CODEWITHSUNDEEP
phpmysqlsql
Suneth Kalhara
Suneth Kalhara

Reputation: 1208

MySQL query on product attibute table

I have a mysql table which include product and attribute relations,

id  productid attributeid arributevalueid
18  521       12           36
17  521       11           43
16  521       9            16
29  522       18           168
28  522       17           138
27  522       16           115
26  522       15           71
25  522       12           36
24  522       11           48
23  522       9            19

i got a problem when i write a sql query, i'm trying to to filter product which has all attributes,

ex - if i pass attribute value 16 and 36 the product matched is 521

i test a mysql query below

$nweryt=mysql_query('SELECT * FROM 
tbl_proattrconnect 
WHERE 
tbl_proattrconnect.attroid=9 AND tbl_proattrconnect.attrvalid=16 
AND tbl_proattrconnect.attroid=12 AND tbl_proattrconnect.attrvalid=36');

echo mysql_num_rows($nweryt);

But this cant get the results and show 0, can anyone help me please, thanks a lot

Upvotes: 1

Views: 85

Answers (4)

TheProvost
TheProvost

Reputation: 1893

Maybe you meant to use OR rather than AND? Try again, this will display 521 and 522

$nweryt=mysql_query('SELECT * FROM 
tbl_proattrconnect 
WHERE 
tbl_proattrconnect.attroid=9 AND tbl_proattrconnect.attrvalid=16 
OR tbl_proattrconnect.attroid=12 AND tbl_proattrconnect.attrvalid=36
order by productid'
);

Upvotes: 0

fancyPants
fancyPants

Reputation: 51938

Please have a try with this:

SELECT
productid
FROM
tbl_proattrconnect
GROUP BY productid
HAVING SUM(attributevalueid IN (16, 36)) >= 2 /*here you specify after the >= how many attributes you have in IN()*/
AND SUM(attributevalueid BETWEEN x and y) >= 1 /*here you just always leave it at >= 1*/
AND SUM(attributevalueid BETWEEN z and w) >= 1 /*feel free to add as much checks as necessary.*/

This uses a little trick. attributevalueid IN (16, 36) returns true or false, 1 or 0.

Alternatively you can self join the table, but this may have a bit worse performance and can be quite clumsy if you have to check for more attributes. You have to self join the table once for each attribute.

Upvotes: 2

Apuig
Apuig

Reputation: 360

You have a mistake in your query because you are using an AND operator and not OR. Remember:

  • The AND operator displays a record if both the first condition AND the second condition are true.
  • The OR operator displays a record if either the first condition OR the second condition is true.

Read this info http://www.w3schools.com/sql/sql_and_or.asp

$nweryt=mysql_query('SELECT * FROM tbl_proattrconnect 
                     WHERE 
                       tbl_proattrconnect.attroid=9 AND tbl_proattrconnect.attrvalid=16 
                     OR 
                       tbl_proattrconnect.attroid=12 AND tbl_proattrconnect.attrvalid=36');

echo mysql_num_rows($nweryt);

Also can write the same query but shorter:

SELECT * FROM 
tbl_proattrconnect 
WHERE 
    tbl_proattrconnect.attroid IN(9,12) AND tbl_proattrconnect.attrvalid IN(16,36);

Upvotes: 0

Mark Madej
Mark Madej

Reputation: 1932

You need to join the table to itself to make this happen. For example:

SELECT t1.productid
FROM tbl_proattrconnect t1
JOIN tbl_proattrconnect t2 ON t1.productid = t2.productid
WHERE
  (t1.attroid = 9 AND t1.attrvalid = 16)
AND
  (t2.attroid = 12 AND t2.attrvalid = 36)

Upvotes: 1

Related Questions