MYSQL compare array against db table

Question

I have have a multi-select feature which allows the user to tag a company with services. And in my database I have a table with just id and names of those services. What I am trying to do is create a statement that searches to see if the tag already exists, if it does not exist, create it. Could someone help me with the logic of setting this up?

I am just not sure how to go about searching through a table column with an array.

Thanks

$company_type = implode(',', $company_type);
 $lookup_tags = "SELECT * FROM table WHERE company_type_id = ?";
  $q_lookup_tags = $conn->prepare($lookup_tags);
   $q_lookup_tags -> execute(array($company_type));

How do I perform the select and sort the results into existing and non existing ids?

Thanks

andrew · Accepted Answer · 2014-03-13 23:10:53Z

1

mysql in operator is your friend here:

$lookup_tags = "SELECT * FROM table WHERE company_type_id in ($company_type)";

but if you're using pdo prepared statements you'll need to filter manually

Edit

$placeHolders=implode(',',array_fill(0,count($company_type),'?'));
$lookup_tags = "SELECT * FROM table WHERE company_type_id in ($placeHolders)";
$qry= $conn->prepare($lookup_tags);
$qry->execute(array_values($company_type));

edited Mar 13, 2014 at 23:10

answered Mar 13, 2014 at 22:59

andrew

9,6039 gold badges36 silver badges62 bronze badges

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

1 Comment

andrew Over a year ago

which I did point out in the original answer. Edited with a safer version

Loïc · Accepted Answer · 2014-03-13 23:27:00Z

0

 $query = "";
 $i = 0;
 foreach($company_type as $key=>$type){
    $query.="SELECT $type as type, count(*) as total,* FROM table WHERE company_type_id = ".intval($type);
    if($i < count($company_type)){
        $query.=" UNION ";
    } 
    $i++;
}

$q_lookup_tags = $conn->prepare($query);
$q_lookup_tags -> execute();

edited Mar 13, 2014 at 23:27

answered Mar 13, 2014 at 22:58

Loïc

12k2 gold badges35 silver badges52 bronze badges

2 Comments

Owen Over a year ago

just about the second part of the question how would I go about sorting the existing and non existing tags? Usually I perform a row count after a select statement to see if it exists but I have never tried to pass an array through before

Loïc Over a year ago

you could use mysql union statement and craft a query. I'll edit my post, give me a sec.

Collectives™ on Stack Overflow

MYSQL compare array against db table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related