Pulling information from database, putting it in email, and then deleting it

[shakingspear]

My brain is a little fried and I'm trying to figure this out. I'm pretty sure it's possible, but I'm not sure about how to delete the information. The database is two columns: id and the info I want to send in the email. I think I can pull up the information like this:

 

mysql_query("SELECT * FROM specific_prompts ORDER BY id LIMIT 0,3") //for someone who needs 3 prompts

 

How would I go about deleting the three rows I just selected?

[redarrow]

<?php

$sql=mysql_query("SELECT * FROM specific_prompts ORDER BY id LIMIT 0,3") or die (mysql_error());

while($del=mysql_fetch_assoc($sql)){


// use sessions to do what ever with above select.

$sql2=mysql_query("delete FROM specific_prompts where id='".$del['id']."'");

$res=mysql_query($sql2)or die (mysql_error());

}
?>

[shakingspear]

Thank you for your response! Any reason why I would 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 '1' at line 1

 

I've tried to troubleshoot but I'm missing something.

[shakingspear]

I just realized that even though I'm getting the error it's deleting one row from the table. So something is working right. But I'm still getting the error and it's deleting 1 instead of 3 rows.

[9three]

LIMIT 0,3 will actually display 4 rows. 0, 1, 2, 3

 

If its only deleting one row at a time make sure that you named your row 'id'

[Philip]

You're trying to run the query twice:

$sql2=mysql_query("delete FROM specific_prompts where id='".$del['id']."'");

$res=mysql_query($sql2)or die (mysql_error());

Change it to:

$sql2="DELETE FROM specific_prompts WHERE id='".$del['id']."'";

$res=mysql_query($sql2)or die (mysql_error());

[shakingspear]

Works perfectly. Thanks all for your help!

[redarrow]

that was my fault sorry.

[shakingspear]

Sorry for making this unsolved again, but I thought it would be better then starting a new thread. I'm having trouble with the mail feature now. I'm sending out three separate emails instead of one email with three results in it. Any thoughts?

 

				$sql=mysql_query("SELECT * FROM random_prompts ORDER BY id LIMIT 0,3") or die (mysql_error());

while($del=mysql_fetch_assoc($sql)){
$id = $del['id'];
$prompt = $del['prompt'];



mail( "[email protected]" , "Test", $prompt, "From:[email protected]" );

[shakingspear]

I think I have to take the mail function out of the loop, and maybe write an array for the rows. I think that will put the information into one email. But I'm not sure how to write that. Can someone help?