problem with array, please help

[brailleschool]

This code works

<?
// specify the variables in the config.php file
include("config.php");
// If the form was submitted, delete the items selected from the database 
$connection = mysql_connect("$server", "$db_user", "$db_pass");
$db = mysql_select_db("$database", $connection);
if($_POST["deleted_items"] != "") 
{ 
	$delItems = implode(",", $HTTP_POST_VARS['deleted_items']);
	for($x=0;$x<count($delItems);$x++)
	{
		$query = "DELETE FROM $table_name_usermessages WHERE id IN ($delItems)";
		$result = mysql_query($query, $connection) or die(mysql_error()); 
	}
}
?>

But when I add

		$query = "UPDATE $table_name_sentusermessages SET `new`=`deleted` WHERE id IN ($delItems)";

to make:

<?
// specify the variables in the config.php file
include("config.php");
// If the form was submitted, delete the items selected from the database 
$connection = mysql_connect("$server", "$db_user", "$db_pass");
$db = mysql_select_db("$database", $connection);
if($_POST["deleted_items"] != "") 
{ 
	$delItems = implode(",", $HTTP_POST_VARS['deleted_items']);
	for($x=0;$x<count($delItems);$x++)
	{
		$query = "DELETE FROM $table_name_usermessages WHERE id IN ($delItems)";
		$query = "UPDATE $table_name_sentusermessages SET `new`=`deleted` WHERE id IN ($delItems)";
		$result = mysql_query($query, $connection) or die(mysql_error()); 
	}
}
?>

I get this error message, why is this? How am I able to delete from one table using multiple checkboxes, and update another table at the same time? Please advise.

Error message is:
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 'SET new=deleted WHERE id IN (1391)' at line 1

[lazzerous]

field names don't need quotes.

$query = "UPDATE $table_name_sentusermessages SET new=deleted WHERE id IN ($delItems)";

[devinemke]

2 things:

1. why are you doing a for loop? you are using implode() to create an array of the records that you want to delete and then using IN() to do all the deletion in one query. you don't need to loop through the array.

2. you are setting the varibale $query twice. once for the delete query and the other for the update query. the only query that is actually going to run is the update query because you are overwriting the delete query variable.

[brailleschool]

Originally posted by devinemke
2 things:

1. why are you doing a for loop? you are using implode() to create an array of the records that you want to delete and then using IN() to do all the deletion in one query. you don't need to loop through the array.

2. you are setting the varibale $query twice. once for the delete query and the other for the update query. the only query that is actually going to run is the update query because you are overwriting the delete query variable. [/B]

The thing is, I am using multiple checkboxes to be able to delete multiple entries from a mysql db. Thats why I am using an array, but what I am looking to do is not only being able to delete the messages that are selected, but to update a second table at the same time. Does this make it a little clearer? I hope so. 🙂

[devinemke]

let's back up a bit. the user checks a bunch of checkboxes that represents records in table1 that he/she wants to delete. when the form is submitted, the checked records in table1 should be deleted and a related field called "new" in table2 is updated as "deleted".

the form should collect all of the checkboxes in an array like so:

<input type="checkbox" name="delete[]" value="1">
<input type="checkbox" name="delete[]" value="2">
<input type="checkbox" name="delete[]" value="3">
etc...

then when the form is submitted you will have an array called $POST['delete'] which contains all of the checked boxes. you can then do your deletes from table1 with a single query:

mysql_query ('DELETE FROM table1 WHERE id IN(' . implode (',', $_POST['delete']) . ')');

and then do your updates to table2 also in a single query:

mysql_query ("UPDATE table1 SET new = 'deleted' WHERE id IN(" . implode (',', $_POST['delete']) . ')');

again, there is no need to loop through the array. i think this should be the simpliest way to do this (unless i'm missing part of what you are trying to do).

[brailleschool]

I am looking to do both queries (delete and update) with the same code, as the same checkboxes that are checked need to perform the both functions.

So how would I change the below code to do both function at the same time using the same checked check boxes?

<?
// specify the variables in the config.php file
include("config.php");
// If the form was submitted, delete the items selected from the database 
$connection = mysql_connect("$server", "$db_user", "$db_pass");
$db = mysql_select_db("$database", $connection);
if($_POST["deleted_items"] != "") 
{ 
	$delItems = implode(",", $HTTP_POST_VARS['deleted_items']);
	for($x=0;$x<count($delItems);$x++)
	{
		$query = "DELETE FROM $table_name_usermessages WHERE id IN ($delItems)";
		$result = mysql_query($query, $connection) or die(mysql_error()); 
	}
}
?>

Everything I try so far gives me an error when it comes to changing the value of the row called "new".

Both the usermessages and sentusermessages table have the same structure, and are in the same database.

[devinemke]

uh, well, if you apply the example i posted earlier:

include('config.php');
$connection = mysql_connect ($server, $db_user, $db_pass);
$db = mysql_select_db ($database, $connection);

if ($_POST['deleted_items'])
{ 
	$delItems = implode (',', $_POST['deleted_items']);
	$result_delete = mysql_query ("DELETE FROM $table_name_usermessages WHERE id IN($delItems)");
	$result_update = mysql_query ("UPDATE $table_name_sentusermessages SET new = 'deleted' WHERE id IN($delItems)");
	if ($result_delete) {echo 'delete query OK';} else {echo 'delete query FAILED';}
	echo '<br>';
	if ($result_update) {echo 'update query OK';} else {echo 'update query FAILED';}
}

[brailleschool]

After doing some research, I found that the ID number in both the sent messages and inbox tables were different. However, the value of nomer was the same.

I implemeted your code, but it states that the delete query and update query failed.

The nomer value (message id) is correct within the value section of the messages checkbox. But it still refuses to execute either or both requests.

Code below:

<?
include('config.php'); 
$connection = mysql_connect ($server, $db_user, $db_pass); 
$db = mysql_select_db ($database, $connection); 

if ($_POST['deleted_items']) 
{  

    $delItems = implode (',', $_POST['deleted_items']); 
    $result_delete = mysql_query ("DELETE FROM $table_name_usermessages WHERE nomer IN ($delItems)"); 
    $result_update = mysql_query ("UPDATE $table_name_sentusermessages SET new = 'unreaddeleted' WHERE nomer IN ($delItems)"); 
    if ($result_delete) {echo 'delete query OK';} else {echo 'delete query FAILED';} 
    echo '<br>'; 
    if ($result_update) {echo 'update query OK';} else {echo 'update query FAILED';} 
}
?>