problem with Update/delete from database

lentildal

hi people

this might be a bit of a long post :-/

i have a table in my database called bbmaillist. its basically a list of email addresses. there are two columns bbml_id (auto increment int key field) and bbml_email (text).

i have this script called mail.php.

<?php
// database variables ***************************************
*blah*

// connect to the database **********************************
*blah*

// make da query
$SQL="SELECT * FROM bbmaillist ORDER BY bbml_id";
$query=mysql_db_query($database_name,$SQL);
$num_rows=mysql_num_rows($query);

// build a table
print("<table cellspacing='0' cellpadding='0'><form method='POST' action='delete.php'>");
for ($a=0; $a < $num_rows; $a++)
{
$array=mysql_fetch_array($query);
print("<tr><td bgcolor='#FF99FF'>");
print("<input name=eadd size=30 maxlength=70 value='".$array['bbml_email']."'>");
print("</td><td bgcolor='#FF99FF'>");
$bbml_id=$array['bbml_id'];
print('<input type="checkbox" name="echck['.$bbml_id.']" value="none">');
print("</td></tr>");
}
print("<tr><td><br><input type='submit' value='amend' name='amend'><input type='submit' value='delete' name='delete'></td></tr></form></table>");
//print('<input type="checkbox" name="echck['.$bbml_id.']" value="none">');
?>

it takes the entries from the database and puts them in an html table. each entry appears in an input text field with a checkbox next to it.

there are two submit buttons, one to delete and one to amend. both buttons go to this script:

<?php
// database variables ***************************************
*blah*

// connect to the database **********************************
*blah*

//AMEND ***********************************************
if(isset($HTTP_POST_VARS['amend'])){

//as long as we have elements in our array
while(list($key)=each($HTTP_POST_VARS['echck']))
{
    $sql.=' or bbml_id="'.$key.'"';
}

//lets cut the first ' or' part
$sql=substr($sql, 4);

//lets complete the query
$sql='UPDATE LOW_PRIORITY bbmaillist set bbml_email='.$HTTP_POST_VARS['eadd'] .' where '. $sql;
$query=mysql_db_query($database_name,$SQL);

echo $sql;
}

//DELETE ***********************************************
elseif(isset($HTTP_POST_VARS['delete'])){

//as long as we have elements in our array
while(list($key)=each($HTTP_POST_VARS['echck']))
{
    $sql.=' or bbml_id="'.$key.'"';
}

//lets cut the first ' or' part
$sql=substr($sql, 4);

//lets complete the query
$sql='delete from bbmaillist where '.$sql;

$query=mysql_db_query($database_name,$SQL);

echo $sql;
}

?>

there is a problem somewhere, but im not sure where. i am not too hot at php - -i made this code with help from others.

basically it doesnt throw up any errors, but neither button changes the database.

when i echo the $sql for the amend part i get

UPDATE LOW_PRIORITY bbmaillist set bbml_email=text from text box where bbml_id="4"

...but it only gets the text from the last input field in the list, how can i make sure it selects the right one?

the code for deleting entries is fine (except it doesnt actually delete!) but when it comes to updating and i try to update more than one thing from the list at once it says:

UPDATE LOW_PRIORITY bbmaillist set bbml_email= text from text box where bbml_id="6" or bbml_id="11"

so i need to get it to include the new text for every entry in the list that was checked.

thanks in advance, sorry for the lengthy post. (does vbcode work on here? guess i'll find out...)

amcgrath

why not somehting like this instead:

Output the email addresses within a loop:

<?
while(list($k,$v) = @each(emails)) {

// $emails is an array of id/email addresses

<input type="radio" name="action[<?= $k ?>" value="delete"> <input type=text name="email[<?= $k ?>" value="<?= $v ?>">

then, loop through the action array, and update/delete based on the key where the id = the key.

lentildal

hey allen, thanks a lot for your reply.

im afraid your code kind of goes over my head though. im pretty much a newcomer to php.

was there something wrong with the way i was doing things?

i thought that radio buttons could only be selected one at a time thats why i chose to use checkboxes.

if you have the time to explain your code in a bit more detail i would be very grateful, if not thanks anyway - i'll try my best to understand it :-/

amcgrath

Len,

Yea, radios can only check one within the group, meaning 1 selected where 10 have the same name. In my example, each update/delete radio is named action<?= $k ?> where $k is the key of the $emails array, which should be a unique id (say the email id). So, to that end:

<?
// begin looping through the $emails array.

// It is assumed here that the $emails array
// was created earlier in the script by
// selecting the email id and email address
// and assigning the id as the key.

while(list($k,$v) = @each($emails)) {

// output the html. notice the name
// of the radio button is the same
// only for that specific record.
// this will create an array called
// $action which, when read, will
// have the email id as its key
// and the action the user chose

<input type="radio" name="action[<?= $k ?>" value="delete"> <input type=text name="email[<?= $k ?>" value="<?= $v ?>">

// when you read this in the script (it
// basically removes the need for the "if
// submit = edit stuff) you can loop through
// the $action array, and have two sql
// statements to choose from, based on the
// value:

while(list($k,$v) = @each($action)) {
if ($v == "delete") {
$sql = "delete from email_table " .
"where email_id = $k";
} elseif ($v == "update") {
$sql = "update email_table " .
"set email_add = '$email[$k]' " .
"where email_id = $k";
}
// execute query depending on db
}

and there ya go.

amcgrath

Im missing a } (and prolly other misstypes) but you sohuld be able to fix those easily... sorry, i type fast... 🙂

lentildal

hey allen

thanks for taking the time to explain your code. i havent tested it yet but it makes much more sense now.

the only thing is i wanted to create a page which could update an unlimited number of emails, not just one at a time. if you have any advice on how to adapt the script for this purpose i would be most grateful, otherwise thanks for all your help so far - appreciate it.

amcgrath

Believe me, what I have there will update as many emails as you have, its not limited in anyway.

You can email your script, if you want, and I'll modify it with in-depth comments so you can understand it.

lentildal

hi allen

if you have the time to comment this script that would be absolutely great, im just starting out learning php and i need all the help i can get. thanks very much, i really appreciate that.