Updating multiple records on submit

TangoGirl

I've looked through a few explanations here around this subject but none are really applicable to what I want to do so I was hoping someone could help?

I have a page of results generated by a query and next to each displayed record is a checkbox. On selecting the checkbox(es) and submitting the form, a column in the table will be updated.

E.g.
Name = "blah"
Address = "blah"
Status = "added" [checkbox]

If checkbox is selected, the column 'Status' will be updated to a new value.

Can anyone explain the concept of what I need to do so I can have a go at working it out (if my explanation makes any sense)?

Cheers...

sarah

One way to do it would be to name the checkboxes something like:
nameCheckbox
addressCheckbox

Then create your query based on whether there is something in that variable...

<?
//If the name checkbox is checked, add it to the query.
if(!empty($nameCheckbox)){
	$fieldToUpdate.="nameField='$nameTextField'";
}
//If the address checkbox is checked, add it to the query.
if(!empty($addressCheckbox)){
	//If there is already a field to update, add "and".
	if(!empty($fieldToUpdate)){
		$fieldToUpdate.=" and ";
	}
	$fieldToUpdate.="addressField='$addressTextField'";
}
//If there are fields to update, create the query
if(!empty($fieldToUpdate)){
	$sqlQuery="update $tableName set $fieldToUpdate";
}
?>

I'm sure there are more efficient ways to perform this, but this should get you started.

TangoGirl

Thanks for the reply sarah.

I think this is more complex than my needs - I only need to update one column (Status). It won't be empty, it will already have a vaue, I just need to change it.

I have an ID for each record already, could I somehow use this with the update record behaviour?

sarah

Unless I don't understand, just check to see if the status checkbox is checked, update that one field and forget the other junk.

<?
//If the status checkbox is checked, perform the update.
if(!empty($statusCheckbox)){
    $result = mysql_db_query($DB,"update $tableName set status='$newStatus'");
}
?>

TangoGirl

Don't worry it's me who's not understanding I think!

Trying to get my head around what is actually happening in the PHP below. How does the script identify which records to update from this? I thought I would need to put the ID in their somewhere otherwise only one record would be updated?

And where do I declare the newStatus variable in the first place?

Help!

planetsim

Tango the var that Sarah showed is just something she made up to show you what to do. You could make it anything but not starting with a numerial.

Now that must be defined.

So $newStatus cannot just be made like that and automatically given a value

If your using a form. Seems you are.

Id use $_POST or in older versions $HTTP_POST_VARS

its a lot safer

Now about it being defined. Sarah is just making the form feild which is required to update the record
the var

so that feild is has the name newStatus.

Now i hope you understand a little more.

TangoGirl

Thanks planetsim

I think I'm being a bit ambitious trying to work this all out myself seeing as I've only been to one PHP workshop!

I understood the variable '$statusCheckbox' was my form field. I've renamed everything - basically the checkbox & the db column are both named 'Status'.

I'm stuck on where I should actually put this code. I've tried checking for the variable $submit in order to run this code once the form is submitted but I've abandoned that because it broke everything!

If anyone has the patience, would you mind taking it back a step?!

planetsim

Well are you trying to do this in the one file
If so id put the if(isset($_POST['submit'])) up the top then else show the form.

TangoGirl

Not necessarily, what I need to understand is how the update record behaviour can work on each record selected from this - how is the record identified?

E.g.
Name1 = "blah"
ID = "1"
Status = "blah" [checkbox]

Name2 = "blah"
ID = "2"
Status = "blah" [checkbox]

Shouldn't I be somehow bringing the ID into the code to update each record for which the checkbox is selected..?

Or can I do it another way, because the Status of each checked checkbox should be changed to one (the same) value anyway?

xblue

Hi TG,

okay, you've got your form with a number of checkboxes, did I get this right?

Each of them has a name and a value.
I'd use the id's as values:
<input type="checkbox" name="idlist[]" value="234">
<input type="checkbox" name="idlist[]" value="235">
and so on

With the square brackets in the name, you will get all selected values as an array.

After the form is submitted, you can use this array to create a comma-separated list of id's:

$idlist=explode(', ', $HTTP_POST_VARS['idlist']);

The query would be something like:
mysql_query("UPDATE table_name SET status='something' WHERE id IN ($idlist)");

What to replace that "something" with depends on what kind of data the status column takes, e.g. if the status was numeric and 0 before and you want to make it 1, insert 1 there (in that case no singlequotes needed).

Hope I didn't contribute to any confusion...

TangoGirl

Thanks xblue - this is starting to make sense to me, in theory I think this is what I need, just need to get the code right!

I'll go away and try it... may be back!

Cheers for all the input guys

TangoGirl

I might be getting there - each of the 'Status' checkboxes now has a unique ID assigned to it instead, but I'm getting the error "Unknown column 'Array' in where clause" which I guess means the explode function isn't working properly..?

$IDlist=explode(', ', $HTTP_POST_VARS['IDlist']); 
mysql_query = ("UPDATE employees SET Status='Live' WHERE ID IN ($IDlist)");

xblue

oh sorry my fault.

explode creates an array from a string.
implode does the opposite which is what we need here.

TangoGirl

Works like a dream! Many thanks...

Now to fix all the other issues!!

Shamrox

Can this be applied to a dropdown as well?