inserting & deleting in one fell swoop

[vtbruinsfan]

Hi All,

I have a table called ALERTS that I want to hold data until the 'alert' is no more and then I want to dump it into the HISTORY table and delete it from ALERTS. This works fine except that when I check off an alert to be removed, it removes all of the alerts for this customer and puts it into HISTORY (obviously due to my INSERT statement). I want to be able to select just one alert at a time and have it written to HISTORY. The problem is when I use something like

mysql_query("INSERT INTO HISTORY SELECT * FROM ALERTS where alertid='$alertid' ");

it gives me a "query was empty" error.

Any help would be much appreciated.

Denise

The alerts.php page is this:

<?

$result = mysql_query("SELECT * FROM ALERTS WHERE custid='".$_GET['custid']."'")or die(mysql_error());

while($row=mysql_fetch_array($result) )

echo"

<form name='alerts' method='post' action='alerts_remove.php'>

<B>Customer Id:</B> $row[custid] &nbsp;

<B>Alertid:</B> $row[alertid] &nbsp;

<B>Date:</b>$row[date] &nbsp;

<b>Agent:</b>$row[agent] &nbsp;

<b>Memo:</b>$row[memo] &nbsp;

<input type='checkbox' value=".$row["alertid"]." name='delete_id[]'>
<input type='submit' value='remove'><BR><BR>
";
?>

And here is the alerts_remove.php page:

mysql_query("INSERT INTO HISTORY SELECT * FROM ALERTS");

$result = mysql_query($query) or die("error: " . mysql_error());

if (isset($_POST['delete_id']) && is_array($_POST['delete_id']))
{
$deleted = 0;

foreach ($_POST['delete_id'] as $alertid)

{
if (mysql_query("DELETE FROM ALERTS WHERE alertid='$alertid'"))

{

$deleted += mysql_affected_rows();

}

else

{

die("SQL error:<br />" . mysql_error());

}

 }

}

echo"
Alert removed and copied to history.";
?>

[dalecosp]

You will have to use two SQL statements. It can be two in a row (e.g., right next to each other in the script), but you can't put them together like that AFAIK ....

[I've been wrong before, though ...]

[vtbruinsfan]

Hi dalecosp,

Thanks for responding!...but I'm a little confused. What would be the proper way to write it?

Thanks for the help,

Denise

[dalecosp]

I'm a little confused ...

I am, too. It's late, and I'm coming down with a cold or flu or something ...

Lemme see ... steps:

1. Receive vars from POST array.
2. Loop through the vars, if the value is checked, take the following actions:
   a. write the data to the "history" table in the db...
   b. delete the same data from the "alerts" table in the db ...

Also, I don't see why the form is initialized on every iteration of the loop in "alerts.php" --- one line prior to the loop and one after ("</form>") should do it....

Maybe more tomorrow ... or somebody else can help, too ... I need to get to bed....

[vtbruinsfan]

You and I had a similar night...I woke up at 2 AM and thought I was coming down with the flu...feeling ok this morning....hope you are too.

Anyhoo, I moved the form out of the loop as you suggested and I also solved my problem with the following code:

if (isset($_POST['delete_id']) && is_array($_POST['delete_id']))

foreach ($_POST['delete_id'] as $alertid)

$in_query="INSERT INTO HISTORY SELECT * FROM ALERTS where alertid='$alertid'";
$result=mysql_query($in_query) or die("error1: " . mysql_error());

$dl_query="DELETE FROM ALERTS WHERE alertid='$alertid'";
$result=mysql_query($dl_query) or die("error2: " . mysql_error());

echo"
Alert removed and copied to history.";

Thanks for your help.

Denise