mysql query array argument

Question

company                    company_to_attributes
=====================      ============================
| id_company | name        id_attributes | id_company
=====================      ============================
| 1          | Test        | 1           | 1             
| 2          | Test 2      | 2           | 1              
| 3          | Test 3      | 3           | 1         
| 4          | Test 4      | 4           | 3
| ..         | ...         | ..          | ... 
=====================      ============================

I have a simple array:

$array_attributes_IDS = array(1,2);

I want write a query for get the companies that have id_attribute from array.

Please present some examples of what you want to do and what the result should be. — Till Helge
– Till Helge, Commented Apr 9, 2013 at 9:29

Skinny Pipes · Accepted Answer · 2013-04-09 09:48:59Z

2

Try this,

$array_attributes_IDS = array(1,2);
$finalArray = implode(',', $array_attributes_IDS);
$arrCount = count($array_attributes_IDS);

$query ="    SELECT com.name
             FROM   company com
             INNER JOIN company_to_attributes att
                ON com.id_company = att.id_company
             WHERE  att.id_attributes IN ($finalArray)
             GROUP  BY com.name
             HAVING COUNT(*) = $arrCount";

this will return only one company TEST because contains both attributes.

edited Apr 9, 2013 at 9:48

answered Apr 9, 2013 at 9:30

Skinny Pipes

1,0356 silver badges14 bronze badges

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

1 Comment

Mike Over a year ago

Thk for the reply. The query works good, but my array is dynimical, i don't how many elements contains. How resolve for the query COUNT ?

NLZ · Accepted Answer · 2013-04-09 09:29:58Z

0

SELECT c.* FROM company c INNER JOIN company_to_attributes ca ON c.id_company=ca.id_company WHERE ca.id_attributes IN (1,2)

answered Apr 9, 2013 at 9:29

NLZ

9456 silver badges12 bronze badges

Comments

Kumar Saurabh Sinha · Accepted Answer · 2013-04-09 09:30:54Z

0

please try the following:

    $array_attributes_IDS = array(1,2);
    $finalArray = implode(',', $array_attributes_IDS);
    $query = "SELECT C.name FROM company_to_attributes CA INNER JOIN company C ON CA.id_company = C.id_company WHERE CA.id_attributes IN ($finalArray)";

Hope this would help.

answered Apr 9, 2013 at 9:30

Kumar Saurabh Sinha

8708 silver badges23 bronze badges

2 Comments

Mike Over a year ago

Thk for the answer. I would select all companies that have all value of array_attributes_IDS. If have all value.

Kumar Saurabh Sinha Over a year ago

@Mike: with INNER JOIN you will get all Companies which are associated with atleast 1 attribute. I hope this is what you want. Or do you want all the comapnies even if it is not associated with any attribute? Please explain

Praveen kalal · Accepted Answer · 2013-04-09 09:31:00Z

0

SELECT  c.name
FROM    company c
       JOIN company_to_attributes a
            ON c.id_company = a.id_company
WHERE   a.id_attributes IN (1,2)

answered Apr 9, 2013 at 9:31

Praveen kalal

2,0994 gold badges20 silver badges34 bronze badges

Comments

Dale · Accepted Answer · 2013-04-09 09:31:54Z

0

$sql = "
SELECT t1.name FROM company_to_attributes
LEFT JOIN company AS t1 ON t1.id_company = company_to_attributes.id_company
WHERE company_to_attributes.id_attributes IN (".implode(',', $array_attributes_IDS).")
";

answered Apr 9, 2013 at 9:31

Dale

10.5k23 silver badges34 bronze badges

Comments

chandresh_cool · Accepted Answer · 2013-04-09 09:40:00Z

0

"SELECT c.name from company c inner join  company_to_attributes cta ON cta.id_company = c.id_company WHERE   cta.id_attributes IN (1,2);"

answered Apr 9, 2013 at 9:40

chandresh_cool

11.9k3 gold badges32 silver badges46 bronze badges

Collectives™ on Stack Overflow

mysql query array argument

6 Answers 6

1 Comment

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related