Fastest way to check array items existence in mySQL table

Question

User writes a series of tags (, separated) and posts the form.
I build an array containing the tags and delete dupes with array_unique() php function.

I'm thinking of doing:

• go through the array with foreach($newarray as $item) { ... }
• check each $item for existence in the tags mySQL table
• if item does not exists, insert into tags table

Is there a FASTER or MORE OPTIMUM way for doing this?

Answer 1

You can call implode( ',' $array ) then use the IN SQL construct to check if there are existing rows in the db.

Example:

<?php
   $arr = ...
   $sql = 'SELECT COUNT(*) FROM table WHERE field IN ( ' . implode( ',', $arr ) . ' );';
   $result = $db->query( $sql );
?>

Answer 2

$tags = array('foo', 'bar');

// Escape each tag string
$tags = array_map('mysql_real_escape_string', $tags, array($sqlCon));

$tagList = '"' . implode('", "', $tags) . '"';

$qs = 'SELECT id FROM tag_list WHERE tag_name IN (' . $tagList . ')';

Answer 3

I don´t know if it's faster, but you can use mysql's IN. I guess you'd have to try.

Answer 4

You could build up a big query and do it in one shot, instead of many little queries:

SELECT DISTINCT tag
  FROM my_tags
 WHERE tag in ( INPUT_TAGS )

Just build up INPUT_TAGS as a comma-separated list, and make sure you properly escape each element to prevent SQL injection attacks.

Answer 5

I'm hoping that people can comment on this method. Intuition tells me its probably not the best way to go about things, but what do you all think?

Instead of making an extra call to the DB to find duplicates, just add a unique constraint in the DB so that tags cannot be inserted twice. Then use INSERT IGNORE... when you add new tags. This method will ignore the errors caused by duplications while at the same time inserting the new items.