Say, I have a whole lot of book titles in the database (more than 500000) and someday I want to add another title in the database, how would I check whether the database already contains this title or not? How would I do it in php?
+2
A:
Something like SELECT 1 FROM table WHERE title = '' springs to mind, with an index on title ofcourse :)
From PHP:
$query = mysql_query("SELECT 1 FROM table WHERE title = '%s'",
mysql_real_escape_string($title));
$result = mysql_query($query);
if(mysql_num_rows($result)){
echo 'The title exists in the database';
}
WoLpH
2010-03-04 02:26:05
the problem with this is that someone else could insert that record after you have checked with "select" and before you do your "insert". The most reliable method is to "insert" and catch a "duplicate key" error - assuming you have an appropriate unique or primary key defined (which you should).
Martin
2010-03-04 05:18:26
Correct, how you would implement this would depend on the use case ofcourse. An `INSERT IGNORE` or `INSERT ON DUPLICATE KEY UPDATE` might be a better solution.
WoLpH
2010-03-04 21:18:31
A:
or you could execute the actual insert statements and wait for the results. if the row you are inserting is already in the database, assuming that you define some sort of a primary key in the table, it will post an error... duplicate entry not allowed blah... and you can "catch" it... just a thought!
ultrajohn
2010-03-04 02:35:15
+1
A:
If you really want a title to be unique, best to express that as a unique constraint on the title column.
duffymo
2010-03-04 03:01:53
A:
insert ignore into
It inserts if not exists, otherwise no op. [assuming title has unique constraint]
Fakrudeen
2010-03-04 06:10:30