Updating Multiple Database Rows Simultaneously

Hi,

I'm having some trouble with something that should be relatively easy. I
want to update multiple rows in one of my database tables simultaneously.
In my table I have these values:

imageID
image_order

I want to be able to modify the image_order column where imageID = imageID.

imageID | image_order
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5

I want to be able to pull these values into a form and allow the user to
edit the image_order value. So my updated table might look something like
this:

imageID | image_order
1 | 2
2 | 5
3 | 1
4 | 3
5 | 4

The problem lies in the form handling script. How can I pass multiple sets
of imageID / image_order values to an sql update query?

I can pass one set without problem. But I want the user to be able to edit
all of the image_order values from one page.

I've tried passing multiple image_order values by initialising an array and
using a foreach() loop to extract the values but I can't think of a way to
pass the corresponding imageID value. I can grab all the imageID values
again in my form handler script using an ORDER clause to ensure they are
pulled in the same order as the form script but again, I can think of no way
to combine this with the img_order array sent from my form.

How can I combine all of this data in one sql query that will update my
table?

Thanks in advance for any help.

re: Updating Multiple Database Rows Simultaneously

I noticed that Message-ID: <bmbk8p$nnb$1$8302bc10@news.demon.co.uk> from
scott contained the following:
[color=blue]
>The problem lies in the form handling script. How can I pass multiple sets
>of imageID / image_order values to an sql update query?[/color]

You can't. You'll have to write a loop and do it with multiple queries[color=blue]
>
>I can pass one set without problem. But I want the user to be able to edit
>all of the image_order values from one page.[/color]

You'll either have to write a loop to update all the values whether
changed or not or put a sequentially numbered checkbox beside the ones
which you want to change. Use a bit of javascript to check the box
automatically onChange for a bit of added functionality (but make it
clear the box must be checked for those without JS) Incidentally, this
simple bit of javascript had me stumped for ages until I found out that
the checkboxes would have to have a letter prefix

I use the same method to delete entries except on the first pass the
script simply writes hidden input for the records to be deleted. The
user then has to press a confirm button to complete the operation.



--
Geoff Berrow
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

re: Updating Multiple Database Rows Simultaneously

"scott" <no@spamplease.com> wrote in message
news:bmbk8p$nnb$1$8302bc10@news.demon.co.uk...[color=blue]
> Hi,
>
> I'm having some trouble with something that should be relatively easy. I
> want to update multiple rows in one of my database tables simultaneously.
> In my table I have these values:
>
> imageID
> image_order
>
> I want to be able to modify the image_order column where imageID =[/color]
imageID.[color=blue]
>
> imageID | image_order
> 1 | 1
> 2 | 2
> 3 | 3
> 4 | 4
> 5 | 5
>
> I want to be able to pull these values into a form and allow the user to
> edit the image_order value. So my updated table might look something like
> this:
>
> imageID | image_order
> 1 | 2
> 2 | 5
> 3 | 1
> 4 | 3
> 5 | 4
>
> The problem lies in the form handling script. How can I pass multiple[/color]
sets[color=blue]
> of imageID / image_order values to an sql update query?
>
> I can pass one set without problem. But I want the user to be able to[/color]
edit[color=blue]
> all of the image_order values from one page.
>
> I've tried passing multiple image_order values by initialising an array[/color]
and[color=blue]
> using a foreach() loop to extract the values but I can't think of a way to
> pass the corresponding imageID value. I can grab all the imageID values
> again in my form handler script using an ORDER clause to ensure they are
> pulled in the same order as the form script but again, I can think of no[/color]
way[color=blue]
> to combine this with the img_order array sent from my form.
>
> How can I combine all of this data in one sql query that will update my
> table?
>
> Thanks in advance for any help.
>[/color]
Fixed it:

foreach ($imageID as $key => $value)
{
mysql("$db_name","UPDATE images SET img_order = '$value' WHERE imageID =
'$key'");
}

Just needed to create an array to pass the imageID value as the $key and the
img_order value as the $value.