mysql where in array

Question

Hi I have the following mysql query which breaks when I try to add the WHERE clause using an array. I think its in the right order but not sure if I'm wrong with the notation or something here. I am able to verify that $company_type does have data and is an array, but the SQL statement returns no results when I can verify that there are.

Thanks

"SELECT *,
         ( 6371 * acos( 
     cos(radians($lat)) * cos(radians(l.location_latitude)) * 
        cos(radians(l.location_longitude) - radians($lng)) + 
     sin(radians($lat)) * sin(radians(l.location_latitude)) 
    ) ) AS distance 
            FROM
        company c
            INNER JOIN
        bcompanytocompany bc
            ON c.company_id=bc.company_id
            INNER JOIN
        lcompany_types ct
            ON bc.company_type_id=ct.company_type_id
            INNER JOIN
        bcompanytolocations bcl
            ON c.company_id = bcl.company_id
            INNER JOIN
        address a
            ON bcl.address_id = a.address_id
            INNER JOIN
        location l
            ON a.address_location_id = l.location_id
            INNER JOIN
        postal_codes po
            ON a.address_postal_id = po.postal_id
            INNER JOIN
        cities ci
            ON po.city_id = ci.city_id
            INNER JOIN
        lprovinces pr
            ON ci.province_id = pr.province_id
        WHERE company_type_name 
            IN (".implode(',',$company_type).")
        HAVING distance < $setdistance
        ORDER BY distance
        LIMIT 30"

What type are the values in $company_type? Strings? Integers? — John Conde
– John Conde, Commented Mar 20, 2014 at 20:48

John Conde · Accepted Answer · 2014-03-20 20:53:16Z

1

Since $company_type contains values that are strings you will need to wrap them in quotes if you wish to use IN():

WHERE company_type_name 
        IN ('".implode("','",$company_type)."')
-- IN('string1','string2')

You can also use FIND_IN_SET() which is one string containing values separated by commas:

WHERE FIND_IN_SET(company_type_name, '".implode(',',$company_type)."')
-- FIND_IN_SET(company_type_name, 'string1,string2')

answered Mar 20, 2014 at 20:53

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

Comments

mathielo · Accepted Answer · 2014-03-20 20:54:08Z

1

As I believe company_type_name holds strings, you are missing ' for the strings. You're getting something like this:

IN (name1, name2, [...])

Instead of

IN ('name1', 'name2', [...])

Just fix your PHP implode() to add the string characters:

WHERE company_type_name 
        IN ('".implode("','",$company_type)."')

answered Mar 20, 2014 at 20:54

mathielo

6,7757 gold badges53 silver badges64 bronze badges

Collectives™ on Stack Overflow

mysql where in array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related