Error in my query when I try to delete data

Question

I am trying to delete some data from a table in Workbench, but when I press the delete button I get this error;

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='Stephenie Meyer' and Year_of_birth='1985'' at line 1.

This is the code:

$author=$_POST['AuthorID'];
$name=$_POST['Name'];
$year=$_POST['Year_of_birth'];

$author=htmlspecialchars($author);
$name=htmlspecialchars($name);
$year=htmlspecialchars($year);

if($db_found)
{
      $SQL="SELECT*from books WHERE AuthorID='$author' Name='$name' and Year_of_birth='$year'";
      $result=mysql_query($SQL) or die(mysql_error());

        while($row=mysql_fetch_assoc($result))
         {
             echo "<tr><td>".$row["AuthorID"]."</td><td>".$row["Name"]."</td><td>".$row["Year_of_birth"]."</td></tr>" ;

         }
         echo "</table><br>";
         $DQL="DELETE from books WHERE AuthorID='$author' , Name='$name' and Year_of_birth='$year'";
         if ($db_handle->query($DQL) === TRUE) 
           {
            echo "Record(s) deleted successfully";
           }
            else 
            {
            echo "Error deleting record: " . $db_handle->error;
            }
       }
       else
       {
          echo"Record not found";
       }

Can you please help?

And required between name and author instead of comma for starters — Rooster, Jan 08 '16 at 23:49

Chris Bier · Answer 1 · 2016-01-08T23:53:50.810

You have a couple of syntax errors.

You left out an AND
Add some spaces

Change:

 $SQL="SELECT*from books WHERE AuthorID='$author' Name='$name' and Year_of_birth='$year'";

to

 $SQL="SELECT * FROM books WHERE AuthorID='$author' AND Name='$name' AND Year_of_birth='$year'";

Also please look into more modern ways of doing a MySQL query in PHP instead of mysql_query. It was deprecated. Check out the documentation: http://php.net/manual/en/function.mysql-query.php

score 2 · Accepted Answer · edited May 23 '17 at 12:15

2

You need to put a logic operator between all your WHERE conditions.

SELECT*from books WHERE AuthorID='$author' Name='$name' ...

should be

SELECT * FROM books WHERE AuthorID='$author' AND Name='$name' ...

and also

DELETE from books WHERE AuthorID='$author' , Name='$name' ...

should be

DELETE FROM books WHERE AuthorID='$author' AND Name='$name' ...

Incidently, mysql_ functions are deprecated. You should use MySQLi or PDO instead. Additonally, your query is open to SQL injection; read How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:15

Community

1
1

answered Jan 08 '16 at 23:49

worldofjr

3,868
8
37
49

@SofiaV Happy to help; but please do take notice of the second part. `mysql_` functions have been removed in the latest version of PHP, so you do need to change all your database methods. See [this answer](http://stackoverflow.com/a/26476208/3899908) for info of how to migrate to MySQLi. – worldofjr Jan 09 '16 at 01:06
I changed that as well. Thank you again. – SofiaV Jan 09 '16 at 01:23

Error in my query when I try to delete data

2 Answers2