MySQL: add row if it doesn't exist otherwise don't

[Mahmood-Saleh]

This is my first post on this forums, I hope I get what I've expected, anyways xD

I have a MySQL table called People, each row must have 4 structures which are Name, Age, Location and Gender.

This is what I've got so far:

$prepare = $connection->prepare("SELECT * FROM people WHERE name=?");
$prepare->bind_param("s", $name);
$prepare->execute();
$result = $prepare->get_result();
if ($result) {
    $insert = $connection->prepare("INSERT INTO people (name, age, location, gender) VALUES (?,?,?,?)");
    $insert->bind_param("siss", $name, $age, $location, $gender);
    $insert->execute();
    echo "true,".$name;
} else {
    echo "false,".$name;
}

I don't even know if it's the right way to do it or not, I just tried what I've got after a long search, so correct me if I was wrong.

 

~Thanks for your time!

[fastsol]

Well right now the code is going to insert a new row if there already IS a row in the db with a name of the posted $name.  So you have that part backwards. What you want is this instead

if (!$result) { // Notice the !

Then I'm not sure if you are using a custom pdo wrapper, but get_result() is not a pdo object function, so that isn't going to return anything.  What you want instead is this.

$result = $prepare->fetch(PDO::FETCH_ASSOC);

[requinix]

1. It looks like you're checking to see if the person exists, and if so, doing the insert. Got that backwards.

2. If this script executes twice at once, with the same person name, then one may both think the person doesn't exist and both try to insert the person.

 

The name is the unique value, right? Make sure your table has a UNIQUE constraint on the name, then do an INSERT IGNORE without bothering to check if the person exists yet. If they do then the insert won't do anything, and if they don't then you've checked and inserted at the same time (which address problem #2).