Check if Record Exists

Question

I found this on php.net to check if file exists.

I want to check if record exists first, then do update, otherwise do an insert.

if(record exist) {
   update query}
else 
  { insert query}

I know how to update or insert, but don't know how to check if a record exists first. How is it done?

Answer 1

If you know how to do a SQL SELECT, then do that:

$result = mysql_query("SELECT * FROM table1 WHERE something");
$num_rows = mysql_num_rows($result);

if ($num_rows > 0) {
  // do something
}
else {
  // do something else
}

Better yet, don't do this in PHP, use INSERT ... ON DUPLICATE KEY UPDATE.

Answer 2

You don't need to do this in PHP, you can do it directly in the SQL query for your database (I'm assuming you're using a database for the records, since that's what it sounds like from your question, and I'll assume MySQL since that's often used along with PHP).

INSERT INTO ... ON DUPLICATE KEY UPDATE;

This would insert a new row if it doesn't already exist, or update the current one if it does, assuming your tables have primary keys.

See the MySQL Manual for more info on this.

The other option is to just do a SELECT COUNT(1) FROM myTable WHERE ... query first, and then only do an insert if the result is 0, otherwise do an update.

Answer 3

I would recommend Dominic Rodger's solution, but with a little change to make it faster. You should select a single value and not more than one row.

$result = mysql_query("SELECT key FROM table1 WHERE something LIMIT 1");
$num_rows = mysql_num_rows($result);

if ($num_rows > 0) {
  // do something
}
else {
  // do something else
}

If your record already exists, you'll get a result, wich is more than 0 results, so it works, but potentially with less traffic from your SQL-Server.

Answer 4

you could also try this

INSERT ... ON DUPLICATE KEY UPDATE

Answer 5

$username = $_POST["user"];

$query = mysql_query("SELECT * FROM users WHERE username='$username'");

if(mysql_num_rows($query) != 0)
{
echo "Username already exists";
}
else
{
//proceed with code here
}

Answer 6

Count records matching your criteria?

select count(*) from foo where id = 5

if($count > 0) {
    // record exists
    ...
}

Answer 7

Strange no one has mentioned using REPLACE?

Answer 8

I've had the same problem and I solved using REPLACE INTO, a MySQL extension working this way:

• If the record which you want to insert does not exist, the MySQL REPLACE inserts a new record.
• If the record which you want to insert already exists, MySQL REPLACE deletes the old record first and then insert a new record.

I found this method useful when I don't need to remember the old values of the record. For example, if you want to increase a value this method isn't a great way 'cause delete the old record and the old values as well.

Remember also you need to have both INSERT and DELETE privileges to use REPLACE.

Here's and example based on my code:

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "REPLACE INTO table_name (name, age, current_date)
       VALUES ('" . $name . "', $age, '" . date('Y-m-d') . "')";

if ($conn->query($sql) === FALSE) {
   echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

Answer 9

You could do a select for that record and inspect the result, or you could try an update and see if there was an error. If there was, then there was nothing to update, so do an insert.

Answer 10

You can set the specific columns in your database as primary key and then the insert will success only if you don't have the record already. In this way, you don't event need to check if record exists.