14 Replies - 1130 Views - Last Post: 22 February 2014 - 06:01 PM Rate Topic: -----

#1 Desertt   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 182
  • Joined: 27-October 13

Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 10:58 AM

Hi i am using this form to delete some data
<!DOCTYPE html>
<html>
<head>
<title>Delete a Record from MySQL Database</title>
</head>
<body>

<?php
if(isset($_POST['delete']))
{
$db_host = 'host';
$db_user = 'user'; 
$db_pwd = 'pwd';
$table = 'table';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}

$id = $_POST['id'];

$sql = "DELETE {$table} ".
       "WHERE id = $id" ;

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($conn);
}
else
{
?>
<form method="post" action="<?php $_PHP_SELF ?>">
<table width="400" border="0" cellspacing="1" cellpadding="2">
<tr>
<td width="100">Employee ID</td>
<td><input name="id" type="text" id="id"></td>
</tr>
<tr>
<td width="100"> </td>
<td> </td>
</tr>
<tr>
<td width="100"> </td>
<td>
<input name="delete" type="submit" id="delete" value="Delete">
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>


but when i click on submit i get this error
Could not delete data: 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 'WHERE id = 1' at line 1
i try to change this line
$sql = "DELETE {$table} ".
       "WHERE id = $id" ;
to
$sql = "DELETE from {$table} ".
       "WHERE id = $id" ;

i got an error : Could not delete data: No database selected what is the error ??

Is This A Good Question/Topic? 0
  • +

Replies To: Could not delete data: You have an error in your SQL syntax

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2587
  • View blog
  • Posts: 10,363
  • Joined: 03-December 12

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 11:04 AM

First are you trying to delete a record or a table? The syntax is different for each.
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • So Smart! S-M-R-T!
  • member icon

Reputation: 6578
  • View blog
  • Posts: 26,719
  • Joined: 12-December 12

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 11:08 AM

The correct syntax is:
DELETE FROM table_name [WHERE Clause]

which is the last one you posted. I have to ask why you guessed at it, rather than looking it up? If you guess you could end up deleting your entire table! :whistling:

tutorials point : DELETE

So the error is now "Could not delete data: No database selected" which couldn't be much clearer. You have created the connection but haven't selected a database. mysql_select_db returns true or false, you should check this value.

mysql_connect :docs

Also notice the large warning at the top of the linked page, telling you that the mysql extension is deprecated.

This post has been edited by andrewsw: 22 February 2014 - 11:20 AM

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • So Smart! S-M-R-T!
  • member icon

Reputation: 6578
  • View blog
  • Posts: 26,719
  • Joined: 12-December 12

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 11:15 AM

I might guess that 'host' should be 'localhost' and please DO NOT name your table 'table', it is (obviously) a reserved keyword. I appreciate that you may have changed these values when posting your code here.
Was This Post Helpful? 0
  • +
  • -

#5 Desertt   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 182
  • Joined: 27-October 13

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 11:31 AM

I'm trying to delete all record with id=1 not the whole table but when i use delete from table where id=1 i have that error no database selected.
And my table isn't named table i just change it's name sorry for that
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • So Smart! S-M-R-T!
  • member icon

Reputation: 6578
  • View blog
  • Posts: 26,719
  • Joined: 12-December 12

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 11:47 AM

mysql_select_db('test_db');

This call returns a value of either true or false to confirm if the database was successfully selected. Check the value that it returns.

So make sure that this database exists, and check that you can connect to it using the credentials that you have supplied in your code.

This post has been edited by andrewsw: 22 February 2014 - 11:49 AM

Was This Post Helpful? 0
  • +
  • -

#7 Desertt   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 182
  • Joined: 27-October 13

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 03:58 PM

Ok will check it

This post has been edited by Desertt: 22 February 2014 - 04:00 PM

Was This Post Helpful? 0
  • +
  • -

#8 Desertt   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 182
  • Joined: 27-October 13

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 04:33 PM

It works perfectly now i trace some errors in data connection and other in retval but now it all works thank you

One more thing please if i enter a id that does not exist still give me Deleted data successfully how to check if id exsist then delete else show ID not found please enter another id and redirect him to the form where he put an id
Was This Post Helpful? 0
  • +
  • -

#9 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2587
  • View blog
  • Posts: 10,363
  • Joined: 03-December 12

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 04:41 PM

It would be better to use a control that uses the I'd as a value. If you are listing employees and have their identifiers known, because the information came from the database, the I'd number is stored in say a disabled textbox next to the listing, or a radio button. Or a hyperlink that will show the employee information on its own page.


Otherwise, you just need a conditional statement that checks if the id is valid.

This post has been edited by astonecipher: 22 February 2014 - 04:41 PM

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw   User is offline

  • So Smart! S-M-R-T!
  • member icon

Reputation: 6578
  • View blog
  • Posts: 26,719
  • Joined: 12-December 12

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 04:49 PM

You could either first run a SELECT query to discover if the id exists, or, after running the DELETE query, use mysql_affected_rows which will confirm whether or not the id existed.
Was This Post Helpful? 0
  • +
  • -

#11 Desertt   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 182
  • Joined: 27-October 13

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 04:59 PM

Found it
Was This Post Helpful? 0
  • +
  • -

#12 Desertt   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 182
  • Joined: 27-October 13

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 05:06 PM

i used this one
$sql = "SELECT COUNT(*) AS total FROM {$table} WHERE id = $id";
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
$total = $row['total'];

if($total==1) {
  
$sql = "DELETE from {$table} WHERE id = $id" ;

$retval = mysql_query( $sql, $con );
if(! $retval )
{
  die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\n";
mysql_close($con);
}
else
{
echo "ID not found\n";
}


When a user enter a wrong id it output ID not found how can i redirect hom to the form so he enter another id i used this one
else
{
ob_start();
echo "ID not found\n";
header("Refresh: 2; deleted.php");
ob_end_flush();
}

but i got Cannot modify header information - headers already sent by....... how can i redirect to the form page either data succefully deleted or no
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw   User is offline

  • So Smart! S-M-R-T!
  • member icon

Reputation: 6578
  • View blog
  • Posts: 26,719
  • Joined: 12-December 12

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 05:16 PM

You should put ob_start() at the very top of the php.

Quote

headers already sent by...

You cannot send any output before the header() call, not even a space. The echo before this line is one issue.

You could try switching the echo and header lines; because it delays it might still show the message. Alternatively, you could pass some value back to deleted.php to indicate that it failed and display the error in that page. 'deleted.php#fail' is an overly simple approach. Or perhaps 'deleted.php?fail=1'.

This post has been edited by andrewsw: 22 February 2014 - 05:18 PM

Was This Post Helpful? 0
  • +
  • -

#14 Desertt   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 182
  • Joined: 27-October 13

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 05:35 PM

Did not work it seems like i can't redirect to the same page that i am in it then what can i use to make the page refreshed or after saying no id found to wai about 2 seconds then dipsplay the main where user must put an id
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw   User is offline

  • So Smart! S-M-R-T!
  • member icon

Reputation: 6578
  • View blog
  • Posts: 26,719
  • Joined: 12-December 12

Re: Could not delete data: You have an error in your SQL syntax

Posted 22 February 2014 - 06:01 PM

Ah, right, you are in the same page. In which case there is really no need to redirect: why not just append an error message to the page along with the form?

Do you really need it to pause just to display a simple error message? There are ways to do this, you could even use Javascript with a setTimeout: give the form a css-style (a class-name) that hides it and then, after the setInterval, change the class-name to show it.

Seems like hard work to me, and probably not a great UX.

Please note that "did not work" is not helpful.

This post has been edited by andrewsw: 22 February 2014 - 06:02 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1