[Resolved] DELETE query error syntax

kcgame

Hi,

My code has encountered the following 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 '* FROM users WHERE id = '64'' at line 1

Part of my code is as follows:
users is a table.
RealmID, id and Lastlogin is columns in users table.
$newarray[] is a row's details.

<?

$inactive_tb=mysql_query("SELECT * FROM users WHERE RealmID = '$newarray[RealmID]' AND Lastlogin < '$time_ago' ORDER BY land, NW DESC" ,$link ) or die(mysql_error());

while($realm_array=mysql_fetch_array($inactive_tb)){
	if ($_POST[$realm_array[id]]==1) {

// i believe the error lies in the following line			
$inactive_del=mysql_query("DELETE * FROM users WHERE id = '$realm_array[id]'",$link ) or die(mysql_error());					
							}					

}

Please advise, thanks.

bpat1434

I always find it helpful to really escape from the string to define a variable, then go back in finish your statement. So it would look like:

<?php
"DELETE * FROM users WHERE id = '".$realm_array['id']."'";
?>

But that isn't always necessary.

The thing that gets me about your code is this:

 if ($_POST[$realm_array[id]]==1) { 
// Continue . . .
'$realm_array[id]' // Continue . . .

What are you trying to do here?

~Brett

bubblenut

No need for the * in the DELETE statement, that is implicit. If you're deleting from a table you're deleting the whole row.

kcgame

The thing that gets me about your code is this:
 if ($_POST[$realm_array[id]]==1) { 
// Continue . . .
'$realm_array[id]' // Continue . . .
What are you trying to do here?

~Brett [/B]

I have checkboxes naming $realm_array[id]. i'm checking which checkboxes are selected by the user (=1) upon pressing the button.
After that, delete the whole row selected by the user.
Hope it clarifys the doubt.

bpat1434

Okay, then why not change your HTML to be like this:

<input type="checkbox" name="row_id[]" value="id_number">

Then, when you want to call it, you count the number of selected rows, and itterate through there deleting all the posts:

<?php
$row_id = $_REQUEST['row_id'];

for($i=1; $i<=count($row_id); $i++){
    mysql_query("DELETE FROM users WHERE id = '$realm_array[id]'",$link ) or die(mysql_error());
}
?>

That seems to better suit your needs, rather than checking each row, only delete what you need.

~Brett

bubblenut

You could reduce the number of sql queries to one and seeing as the database is usually one of the worst bottleknecks that would be no bad thing.

The principle is you get a list of all the realms you want to delete and then use the IN syntax in your query.

/*Different way of doing the same thing that bpat1434
did, just doesn't use arrays*/
$realms = array();
foreach($_POST as $key => $value) {
  if(substr($key, 0, 5)=='realm') {
    $realms[] = substr($key, 5);
  }
}
$sql = "DELETE FROM users WHERE id IN ('".implode("', '", $realms)."')";
mysql_query($sql) or die("SQL: $sql\nError:".mysql_error());

kcgame

Thanks bubblenut, the problem lies in the * in the delete syntax. (Should check the manual carefully:p)

My code works fine after taking away the *. The code provided by you is shorter and more usable. But i do not understand all of them 🙁 will spend some time going over them thou.

Thanks for all the helpful responses again🙂