CODEWITHSUNDEEP
phpmysqlmysqli
asdasd
asdasd

Reputation: 13

Inserting record if the ID is not in the database and update it elsewise

I'm trying to insert data into my database. I'm able to insert it using INSERT INTO. However I want to check, if the database already has the ID and if it has I want it to just update the records.

$check = $db->query("SELECT Fuid FROM users WHERE Fuid='$fuid_db'");

//IF the user is new
if(empty($check))
{
  $insert = $db->query("INSERT INTO users (Fuid, Ffname, Femail) VALUES ('$fuid_db', '$ffname_db', '$femail_db'");
} 
else 
{ 
//ELSE update the information
  $update = $db->query("UPDATE users SET Ffname='$ffname_db', Femail='$femail_db' WHERE Fuid='$fuid_db'");
}

How come this is not working? I'm using ERROR_REPORTING(E_ALL), but I'm not getting any errors. It seems like the issue is the "checking" before inserting. Without the checking part it works fine but now it's not inserting anything.

Upvotes: 1

Views: 60

Answers (2)

AbcAeffchen
AbcAeffchen

Reputation: 15017

The Problem

$db->query() returns an (maybe empty) mysqli_result object. So empty($check) evaluates to false (since empty($var) returns true only if $var is zero, false, an empty string, an empty array or null) and thus an UPDATE is performed, no matter if there is a corresponding id or not.

To check if there is a record you have to use $check->num_rows to get the number of records containing the id.

Better

The ON DUPLICATE KEY UPDATE command should work for you.

INSERT INTO users (Fuid, Ffname, Femail) 
  VALUES ('$fuid_db', '$ffname_db', '$femail_db') 
ON DUPLICATE KEY UPDATE users 
  SET Ffname = '$ffname_db', Femail = '$femail_db'

This way you don't have to make a SELECT first, so you reduce the number of database calls.

Notice: This only works, if Fuid is set as a unique key (or primary key).

Security

The way you use the SQL commands is vulnerable to SQL injection. You should use prepared statements.

Using prepared statements, your code would look like this:

$stmt = $db->prepare("INSERT INTO users (Fuid, Ffname, Femail) 
  VALUES (?, ?, ?) 
ON DUPLICATE KEY UPDATE users 
  SET Ffname = ?, Femail = ?");
$stmt->bind_param('issss',$fuid_db,$ffname_db, $femail_db,$ffname_db, $femail_db)
$stmt->execute();

Upvotes: 1

Andy
Andy

Reputation: 397

First and foremost, for security reasons you should not insert value that way. But if we ignore it for now, you can instead use ON DUPLICATE KEY UPDATE as below provided that Fuid is a primary key

INSERT INTO users (Fuid, Ffname, Femail) VALUES ('$fuid_db', '$ffname_db', '$femail_db') 
ON DUPLICATE KEY UPDATE users SET Ffname = '$ffname_db', Femail = '$femail_db'

Upvotes: 0

Related Questions