add mysql row if userID do not exist in table

Question

i'm sending a post request to this code:

$name = (string)$_POST['name'];
$id = (int)$_POST['id'];

$query = "INSERT INTO Users (name, userID) VALUES ('$name', '$id')";


$result = mysqli_query($link,$query);

Which works fine and it adds a row to the table. How do i check wether the userID all ready exist in one of the following rows?

http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql?rq=1 — AmazingDreams, Jan 30 '14 at 09:31
You do a select first then if the row exists you dont make the insert. — Tan, Jan 30 '14 at 09:31
use unique key on 'id' column in your users table. MySQL won't let you insert duplicate rows. — popi, Jan 30 '14 at 09:36
Can you show the `CREATE TABLE` syntax of the table Users? If you have a unique index on UserID you can use `INSERT IGNORE INTO`, this will only throw a warning and skip `INSERT` if it allready exists. — Mad Dog Tannen, Jan 30 '14 at 09:39

zzlalani · Accepted Answer · 2014-01-30T09:50:15.823

1

Do it like this

$query = "SELECT COUNT(*) FROM Users WHERE userID = '$id'";

$result = mysqli_query($link,$query);

if ( mysqli_fetch_assoc($result) ) {
    $message = "Already exists";
} else {
    $query = "INSERT INTO Users (name, userID) VALUES ('$name', '$id')";
    $result = mysqli_query($link,$query);
}

edited Jan 30 '14 at 09:50

answered Jan 30 '14 at 09:33

zzlalani

19,534
16
41
72

1

Isn't the performance of `count(*)` and `fetch` higher than `mysqli_num_rows` ? – Ashraf Samhouri Jan 30 '14 at 09:36
Ashraf is right. also we can use limit 1 and find that result is exists or not it will also reduce processing time. have you seen my answer – Satish Sharma Jan 30 '14 at 09:45

score 1 · Answer 2 · answered Jan 30 '14 at 09:39

Try this

$name = (string)$_POST['name'];
$id = (int)$_POST['id'];

$res = mysqli_query($link, "SELECT * FROM Users WHERE userID = '$id' LIMIT 1 ");
if($row = mysqli_fetch_assoc($res))
{
    echo "this user id is already exists";
}
else
{
   $query = "INSERT INTO Users (name, userID) VALUES ('$name', '$id')";
   $result = mysqli_query($link,$query);
   echo "record inserted successfully ";
}

REMEMBER : always use LIMIT 1 when you trying to get exactly one result.

score 0 · Answer 3 · answered Jan 30 '14 at 09:36

IF you have properly set 'id' as primary key or unique key in your table, you can use the modifier IGNORE in your query you don't get an error when you try to isert a duplicate. Doing this will result in the row only being inserted if the value of the primary key wasn't already in the table.

$query = "INSERT IGNORE INTO Users (name, userID) VALUES ('$name', '$id')";

IF you haven't set a primary key in your table you will have to do a SELECT query to find out if a row with that id is already in your table.

score 0 · Answer 4 · answered Jan 30 '14 at 09:37

0

Make the UserID an Unique Key. If it already exists, your code will throw an error and the row will not be insterted.

alter table Users 
  add unique index Unique_user (userID (8000))

answered Jan 30 '14 at 09:37

Dieter B

1,100
11
19

It wont throw a error actually. Merely a warning! And you cannot have a unique index of that size. – Mad Dog Tannen Jan 30 '14 at 09:42

score 0 · Answer 5 · answered Jan 30 '14 at 09:40

Before inserting the values to the table check whether the following user id exists in the table or not

You can do it in this way

$name = $_POST['name'];
  $id  =   $_POST['id'];
  $sql = "SELECT * FROM Users WHERE userID = '$id'";
  $res= mysqli_query($sql);
  $num = mysqli_num_rows($res);
  if($num == 0)
  {
  $query2 = "INSERT INTO Users (name, userID) VALUES ('$name', '$id')";
  $result2 = mysqli_query($query2);
  echo "record inserted successfully ";
  }
  else
  {
  echo "Record Failed !!";
  }

add mysql row if userID do not exist in table

5 Answers5