CODEWITHSUNDEEP
phpmysqlperformanceforeach
Enrique
Enrique

Reputation: 1045

Fastest way to check array items existence in mySQL table

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:

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

Upvotes: 1

Views: 3185

Answers (5)

awgy
awgy

Reputation: 16914

$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 . ')';

Upvotes: 1

Jacob Relkin
Jacob Relkin

Reputation: 163228

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 );
?>

Upvotes: 2

thetaiko
thetaiko

Reputation: 7824

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.

Upvotes: 4

Justin Ethier
Justin Ethier

Reputation: 134167

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.

Upvotes: 0

jeroen
jeroen

Reputation: 91734

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

Upvotes: 0

Related Questions