sql + php- checking duplicate before insert

Question

im trying to check duplicates before I insert a statement, and after looking at some posts i still couldn't get what went wrong in my php, below is my code and please let me know if i either have something in my php or in my sql.. thanks!!

date_default_timezone_set("America/Los_Angeles");
$time = date("y-m-d H:i:s");

//$name = $_POST["name"];
$name = "bulbasaur";
$lcname = strtolower($name);
if(empty($_POST["nickname"])) {
    $nickname = strtoupper($name);
} else {
    $nickname = $_POST["nickname"];
}



//Connect SQL & PHP
$host = getenv('IP');
$user = getenv('C9_USER');
$password = "";
$dbname = "hw7";

// Create connection
$ds = "mysql:host={$host};dbname={$dbname};charset=utf8";

// Make connection
try {
    $db = new PDO($ds, $user, $password);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   
}
catch(PDOException $ex) {
    header("Content-Type: text/plain");
    echo "Can not connect to the database. Please try again later. \n";
    echo "Error details: $ex \n";
    die();
}

//check duplicates
$subsql = "SELECT name FROM Pokedex WHERE name = {$name})";
try {
$dupe = $db->prepare($subsql);
$param = array("name" => $name);
$dupe->execute($param);
}
catch(PDOException $ex) {
    die($ex->getMessage()."\n");
}
if($dupe-> rowCount() > 0) {
    echo "fail";
} else {
    $sql = "INSERT INTO Pokedex(name, nickname, datefound) 
        VALUES (:name, :nickname, :datefound);";
    try{
        $stmt = $db->prepare($sql);
        $params = array(":name" => $lcname, 
                ":nickname" => $nickname, 
                ":datefound" => $time);
        $stmt->execute($params);
    }
    catch(PDOException $ex) {
        die($ex->getMessage());
    }
}

i received this error message- please help me ...

SQLSTATE[42000]: Syntax error or access violation: 1064 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 ')' at line 1

Since the message mention something about my sql, here is my code for my sql-

DROP TABLE IF EXISTS Pokedex;
CREATE TABLE Pokedex(
  name          VARCHAR(30)     PRIMARY KEY,
  nickname      VARCHAR(30),
  datefound     DATETIME      
);

INSERT INTO Pokedex(name,nickname,datefound) VALUES 
("Pikachu","Pika","2018-01-03 12:23:44");

Since you're using a prepared statement with parameters, it should {$name} should be :name. — Barmar
– Barmar, Commented Jun 1, 2018 at 5:18
Dump the whole code and use unique index to prevent duplicates from being inserted. — Shadow
– Shadow, Commented Jun 1, 2018 at 5:19
@Shadow It's the primary key, so it is a unique index. He just wants to give a better error message than the SQL error. — Barmar
– Barmar, Commented Jun 1, 2018 at 5:20
@Barmar he can check the error and if it is about violation of pk, then he can provide the same user friendly error message as above — Shadow
– Shadow, Commented Jun 1, 2018 at 5:25

Barmar · Accepted Answer · 2018-06-01 05:27:59Z

0

You have two problems.

First, you have a typo, an extra ) at the end of the query. That's what's causing the syntax error.

Second, you're using a prepared statement with $param = array("name" => $name);. So you don't need to put {$name}, in the query, you have to put a placeholder.

$subsql = "SELECT name FROM Pokedex WHERE name = :name";

Alternatively, instead of checking for the duplicate yourself, you could just attempt the INSERT. If it has a duplicate name, you'll get an error, and you can check for this in the catch block.

catch(PDOException $e) {
    if ($e->code == 2627) { // Violation of primary key constraint
        echo "fail";
    } else {
        die($ex->getMessage());
    }
}

edited Jun 1, 2018 at 5:27

answered Jun 1, 2018 at 5:22

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kaitlyn He Over a year ago

daaang!! thank you for checking these out.. i guess i just needed a fresh pair of eyes..

Collectives™ on Stack Overflow

sql + php- checking duplicate before insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related