致命错误：未捕获的PDO异常：SQLSTATE [42000]语法错误或访问Vilation

I'm implementing a simple code to check if one email is already on my DB and if it is deleting it from the database. However, I get this error message:

我正在实现一个简单的代码来检查我的数据库中是否有一封电子邮件,以及它是否正在从数据库中删除它。但是,我收到此错误消息:

Fatal error: Uncaught PDOException: 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 
'@gmail.com' at line 1 in /home/saintpao/public_html/deleteNewsletter.php:24 Stack 
trace: #0 /home/saintpao/public_html/deleteNewsletter.php(24): 
PDOStatement->execute() #1 {main} thrown in /home/saintpao/public_html
/deleteNewsletter.php on line 24

This is the conectar.php file

这是conectar.php文件

$dbhost = "myHost";
$dbuser = "myUser";
$dbpass = "myPassword";
$dbname = "myDBName";
try {
    $db = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass,  
                  array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"));
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch(Exception $error) {
    die("Error conexión BBDD " . $error->getMessage());
}

And this is the deleteNewsletter.php file:

这是deleteNewsletter.php文件:

function sanitazeEmail($email){
   if(empty($email)){
       return "";
   }
   $sanitized = filter_var($email, FILTER_SANITIZE_EMAIL);
   if(filter_var($sanitized, FILTER_VALIDATE_EMAIL)){
       return $sanitized;
   }
   return "";
}

$success = false;
$email = sanitazeEmail($_POST['email']);

if(!empty($email)){
    require_once 'conectar.php';

    $sql = "SELECT `email` FROM `emails` WHERE `email` = " . $email;
    $statement = $db->prepare($sql);

    if($statement->execute()){
        $sql_delete = "DELETE FROM `emails` WHERE `email` =" . $email;
        $statement = $db->prepare($sql_delete);
        $success = $statement->execute();
    }
}

The conection with the database works because I'm able to make SELECT petition in other file, so it must be a problem with the deleteNewsletter.php file. Any idea what's wrong?

与数据库的连接有效,因为我能够在其他文件中进行SELECT请求,所以它必须是deleteNewsletter.php文件的问题。知道什么是错的吗?

1 个解决方案

#1

Your error is that the email is a string and should be quoted. But, you're using PDO and you could use prepared statements to avoid this kind of errors:

您的错误是电子邮件是一个字符串,应该引用。但是,您正在使用PDO并且您可以使用预准备语句来避免此类错误:

$sql_delete = "DELETE FROM `emails` WHERE `email` = :email";
$statement = $db->prepare($sql_delete);
$sucess = $statement->execute([':email' => $email]);

Note that your select query is not really useful, and you could just do the delete query. Note that $statement->execute() returns a statement, not the results.

请注意,您的选择查询不是很有用,您可以执行删除查询。请注意,$ statement-> execute()返回一个语句,而不是结果。

As @NigelRen pointed out, you could use $statement->rowCount() to check if a row was deleted.

正如@NigelRen指出的那样,您可以使用$ statement-> rowCount()来检查是否删除了一行。

#1