Updating multiple records with one submit

[bunner_bob]

I'm trying to figure a way to update multiple records with a single submit. The project in question is a slideshow. The form page lists all the images in a slideshow, along with their "sort order" - the order in which they'll be displayed. Each "sort order" is a form field, named after the "imgid" of that slide, which initially echoes the order specified elsewhere. So the generated table might look like this:

<form>
<input type="hidden" name="slideshowid" value="4">
<table>
<tr><td>image 1</td><td><input type="text" name="1" value="1"></td></tr>
<tr><td>image 23</td><td><input type="text" name="23" value="2"></td></tr>
<tr><td>image 15</td><td><input type="text" name="15" value="3"></td></tr>
</table>
</form>

and after I edit it might be:

<form>
<input type="hidden" name="slideshowid" value="4">
<table>
<tr><td>image 1</td><td><input type="text" name="1" value="3"></td></tr>
<tr><td>image 23</td><td><input type="text" name="23" value="2"></td></tr>
<tr><td>image 15</td><td><input type="text" name="15" value="1"></td></tr>
</table>
</form>

of course there might be 3 images or 10 or 20

When I click submit, I want it to do this:
mysql_query("UPDATE slideshowimagetable SET sortorder=3 WHERE imgid=1");
mysql_query("UPDATE slideshowimagetable SET sortorder=2 WHERE imgid=23");
mysql_query("UPDATE slideshowimagetable SET sortorder=1 WHERE imgid=15");

so the update has to cycle for as many variables as there are that have a number for a name (all other submitted variables will have non-number names).

It seems like I need to make some kind of array after the submit - first build it from the variables that have numbers as names, then run an update for each element in the array.

I need some guidance on how to construct this.

And - is it a good idea to name variables with numbers? Or should I add a letter in there, in which case I'll have to strip it out before building the array.

Thanks to any who can help me out!

[cgraz]

First things first: you can't have a variable that starts with a number in php.

And for your other question, I think i've got a solution. What you'll need to do is change this:

<tr><td>image 1</td><td><input type="text" name="1" value="3"></td></tr> 
<tr><td>image 23</td><td><input type="text" name="23" value="2"></td></tr> 
<tr><td>image 15</td><td><input type="text" name="15" value="1"></td></tr> 

to

<tr><td>image 1</td><td><input type="text" name="image[]" value="1_3"></td></tr> 
<tr><td>image 23</td><td><input type="text" name="image[]" value="23_2"></td></tr> 
<tr><td>image 15</td><td><input type="text" name="image[]" value="15_1"></td></tr>

I know the value looks weird, but what it is is imgid_sortorder. Look at the first input. 1_3 means imageid 1, sortorder of 3.

On the script that processes this, (assuming your form method is set to POST, you would do:

<?

foreach ($_POST['image'] as $value) {

   $var = explode("_", $value);

   $imgid = $var['0'];
   $sortorder = $var['1'];
   $query = mysql_query("UPDATE slideshowimagetable SET sortorder=$sortorder WHERE imgid=$imgid"); 

}

?>

This code is tested and works like a charm. 😉

Cgraz

[sfullman]

previous post was good, my only thought (since we're really talking about PHP not mySQL is that

<tr><td>image 1</td><td><input type="text" name="image[3]" value="1"></td></tr>
<tr><td>image 23</td><td><input type="text" name="image[2]" value="23"></td></tr>
<tr><td>image 15</td><td><input type="text" name="image[1]" value="15"></td></tr>

Notice I put the indexes inside the brackets (don't use quotes in HTML). That way you don't have to worry about parsing out 32_4, you just have $arrayValue[32] = 4 -- all ready to go.

I'm just looking at the mechanics here, didn't study the post too carefully.

[bunner_bob]

cgraz - your solution works great. Only problem is the value that shows up in the form field is "1_3", "23_2", etc. when what I really need is "3", "2", etc. (so the user can just retype the sort order number).

sfullman - I think I see how your example can work, but how would I rephrase cgraz's processing script to work with your version?

Thanks - forgive my newb-ness

[cgraz]

Sorry, wasn't really thinking about what you were using it for 😉

Anyways, make your form look like this:

<tr><td>image 1</td><td><input type="text" name="image[1]" value="1"></td></tr> 
<tr><td>image 23</td><td><input type="text" name="image[23]" value="2"></td></tr> 
<tr><td>image 15</td><td><input type="text" name="image[15]" value="3"></td></tr> 

Notice you have image then between brackets is the number. Image 15 is image[15]. image 23 is image[23] The value is the sortorder, what order they want to order them in.

Then on the processing page, use a simple foreach loop

foreach ($image as $key => $value) {

   $query = mysql_query("UPDATE slideshowimagetable SET sortorder=$value WHERE imgid=$key"); 

}

That should work for you.

Cgraz

[ahundiak]

Might I suggest is that it would be a bit cleaner to just use two arrays?

<tr>
<td>image 1</td>
<td>
<input type="hidden" name="image_num[]" value="1">
<input type="text" name="image_order[]" value="1"></td></tr>
<tr>
<td>image 23</td>
<td>
<input type="hidden" name="image_num[]" value="23">
<input type="text" name="image_order[]" value="2"></td></tr>
<tr>
<td>image 15</td>
<td>
<input type="hidden" name="image_num[]" value="15">
<input type="text" name="image_order[]" value="3"></td></tr>

$image_num = $POST['image_num'];
$image_order = $POST['image_order'];

for($i = 0; $i < count($image_num); $i++)
{
echo $image_num[$i] . $image_order[$i];
}

This way, you are not trying to encode the image number into the name field. Plus, you can easily add additional columns for the user to modify.

In fact, one thing I do is to add an additional hidden field containing the original image_order value. You can then compare the original value with the new value and only update the database if a change was actually made.

[bunner_bob]

Thank you all - this is great stuff. Cgraz - I ended up going with your final version, which works perfectly. I will have to read up more on $_POST and the use of the "=>" combination - just when you think you know it all...(not that I ever did of course)

Ahundiak - I'm going to save your post for future possibilities. Thank you too.

I LOVE this board - the whole concept, the people here. It's such an effective way to learn and solve problems. People are so helpful (and kind to the selectively stupid 😉

Thanks again
- Bob

[cgraz]

No problem. Here's a link to foreach loop. The $POST is for register globals off. You can read more on predefined variables such as $POST here: http://www.php.net/manual/en/reserved.variables.php

Cgraz