How can I check which values in an array are in MySQL?

Question

I have an array of values that I want to check to see if it is in the database already. This array can grow upto 100 elements. What's the best way to check it? Should I pull all the table data and then check against it? Should/Can I do this in 1 SQL statement? If so, how? I'm using PHP.

Edit:

I should add that I am checking to see which ones are in the database so then I can add the ones that aren't in there. Maybe there's a better solution than what I was thinking.

What server side language are you using?

John Conde
– John Conde

2012-05-16 00:11:22 +00:00
Commented May 16, 2012 at 0:11 — John Conde
– John Conde, Commented May 16, 2012 at 0:11
How silly of me to forget to mention! PHP

Strawberry
– Strawberry

2012-05-16 00:14:58 +00:00
Commented May 16, 2012 at 0:14 — Strawberry
– Strawberry, Commented May 16, 2012 at 0:14

John Conde · Accepted Answer · 2012-05-16 00:31:36Z

4

I am assuming PHP

$sql = "SELECT name FROM tablename WHERE name IN ('".implode("','", $array_of_names)."')";

edited May 16, 2012 at 0:31

answered May 16, 2012 at 0:10

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

5 Comments

Strawberry Over a year ago

This returns the ones that are in the database. How can I have it tell me which ones aren't? Because I'm trying to insert only the new ones.

John Conde Over a year ago

Use array_diff() to compare the two arrays. The ones remaining aren't in your database.

Strawberry Over a year ago

I think putting NOT in front of IN would just select all the rows where any of the ('...') values aren't in not in a row right? I wanted to know which ones in my array are not in the database already? or Am I misunderstanding something?

Strawberry Over a year ago

Thanks John Conde! Pronounced as Con-day?

John Conde Over a year ago

Con-dee. Surprisingly some people think it's sounds like candy. I think they're brain dead. :)

wallyk · Accepted Answer · 2012-05-16 00:11:20Z

1

Create a statement dynamically like this:

select *
from table
where value in (3, 5, 8, 12, 17, (etc))

produce the list of values from the array in whatever language you are using.

answered May 16, 2012 at 0:11

wallyk

58.1k17 gold badges92 silver badges155 bronze badges

Collectives™ on Stack Overflow

How can I check which values in an array are in MySQL?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related