Very Fast PHP-MySQL interaction

Question

Hi!, i'm lookin for a way to make some kind of function into php and mysql who give me as a result the id of a record in a simple table.

I got a id -> value table where the id is an autoincrement field.

The natural way or the simple way i know is:

$myId = Check('Cheese');

function Check($value) {



      $sql = "SELECT id FROM table WHERE value = '$value'");


      $res = mysql_query($sql);

      if ($res) 

         return mysql_result($res,0);


      // If the record doesn't exist then continue to insert the record

      $sql = "INSERT INTO table (value) values ('$value')";

      $res = mysql_query($sql);

      return mysql_insert_id();

}

Ok, I think this is the classic way .. but i think there must be a MySQL command or something who make things simpler.

I know there is a INSERT IGNORE but is there a way to make the select only if not exist and return the ID?

I want to do something like:

Select id from table where value = 'dummy' if not exist then Insert into table (value) values ('dummy')

So i'll get the ID in one step and the MySQL will solve the problem and the code will be more efficient or quick ..

(imagine i got make this 10000 times)

Answer 1

If you are assuming that there is a high likelihood that most of Check() calls will eventually result in INSERTs, then you can do INSERT IGNORE and use mysql_affected_rows to check if there was an insert. If there was not an insert, then you can run a SELECT id FROM and use mysql_insert_id(). However, if it is more likely that the row exists, then you are better off running a SELECT statement first.

http://php.net/manual/en/function.mysql-affected-rows.php

If saving resources is your main goal that is. You are probably going to have to run both queries, even if they are combined into one statement.

Answer 2

You probably want WHERE NOT EXISTS. Take a look at the following sample to get an idea:

INSERT INTO table (value) SELECT id WHERE NOT EXISTS (SELECT * FROM table WHERE value='thing to check') LIMIT 1

Answer 3

use INSERT ... ON DUPLICATE KEY UPDATE

function Check($value) {
  $sql = "INSERT INTO table (value) values ('$value') ON DUPLICATE KEY UPDATE value='$value'";
  $res = mysql_query($sql);
  return mysql_insert_id();
}

requires mysql 5.1.12 or later