MySQL Query insert a row if it doesn't exist

Question

I have a table that's called People and each row has a name structure, and other rows like age and such...
Anyways, I want to check if the row that has a specific name is already exists to insert a new one or not to.
This is what I went with so far:

$name = $_GET["name"];
$age = $_GET["age"];
$location = $_GET["location"];

$query = $connection->query("SELECT * FROM people WHERE name='" . $name . "'");
$exist = mysqli_num_rows($query);
if ($exist > 0) {
    //exist
    $connection->query("INSERT INTO people (name,age,location) VALUES ($name, $age, $location)");
} else {
    //doesn't exist
}

But it ain't working, is it because INSERT shouldn't be executed like that? I really don't know, anyways thanks in advance.
EDIT:
After searching I just got the following not working:

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

But it gives me this error:

Fatal error: Call to a member function bind_param() on a non-object in C********* on line 19

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). What do you mean by "ain't working"? Where is your error checking? You *assume* that all of your queries will work. — Jay Blanchard, Jul 15 '15 at 17:36
if you want to insert if the name row exist then `if ($exist > 0) {` — Shehary, Jul 15 '15 at 17:38
Use INSERT IGNORE statements and create unique index on the column you want to be unique. — Abhishek Ginani, Jul 15 '15 at 17:38
@JayBlanchard I just thought of this one: https://xkcd.com/327/ — luksch, Jul 15 '15 at 17:44
sorry for being such a newbie, I'm really sorry guys your answers will help me allot — GreenCubeGames, Jul 15 '15 at 17:45

score 0 · Answer 1 · answered Jul 15 '15 at 18:23

You should use prepared statements to avoid SQL-injections (as mentioned by Jay Blanchard). You should look into Sanitize Filters to filter your$_POST variables.

If you wish to have unique names in your database, you can use the unique key in the database, or by coding:

$result = $mysqli->query("SELECT * FROM people WHERE name='$name'");
if ($result->num_rows > 0) {
    // If results, the name exists
    echo "Someone with the name $name are already in our database!";
} else {
    // Else we insert it!
    $stmt = $mysqli->prepare("INSERT INTO people (name,age,location) VALUES (?, ?, ?)");
    $stmt->bind_param('sis', $name, $age, $location);
    $stmt->execute();
}

You're mixing object-oriented syntaxes and Procedural syntaxes, I'm assuming you're using object oritented (as that's what you had the most of).

score 0 · Accepted Answer · answered Jul 16 '15 at 11:26

0

$insert is false because an error occurred in the given prepare statement. You might have a typo, so to verify that you can just check:

if (!$insert) {
    echo $connection->error;
}

answered Jul 16 '15 at 11:26

PepsiGam3r

192
2
10

it gives `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-name, age, loc) VALUES (?,?,?)' at line 1` – GreenCubeGames Jul 16 '15 at 11:31
No problem, printing errors can solve almost all the problems ;) – PepsiGam3r Jul 16 '15 at 11:43

score -1 · Answer 3 · answered Jul 15 '15 at 17:39

-1

if you try to insert a new record or update an insert record, spend some time and read / understand the syntac mysql provides .. you can read it here http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

answered Jul 15 '15 at 17:39

donald123

5,156
3
22
22

why the down-vote? can the down-voter explain? It seems like a valid answer to me. – luksch Jul 15 '15 at 17:49
It is likely because you posted a link instead of explaining how the information in the link might help the OP. – Jay Blanchard Jul 15 '15 at 19:10

MySQL Query insert a row if it doesn't exist

3 Answers3