Updating multiple records

[Canman2005]

Hi all

 

I have some PHP code which basically outputs the following form

 

<input name="name[]" type="text" value="apple" />
<input name="product[]" type="text" value="mp3" />
<input name="id[]" type="hidden" value="2" />    
<br /><br />
<input name="name[]" type="text" value="microsoft" />
<input name="product[]" type="text" value="software" />
<input name="id[]" type="hidden" value="10" />    
<br /><br />
<input name="name[]" type="text" value="apple" />
<input name="product[]" type="text" value="software" />
<input name="id[]" type="hidden" value="11" />

   

   

when I submit the form, I want to run an update QUERY for everything on the form, using the following QUERY

 

$sql = "UPDATE `items` SET `name` = '".$_POST['name']."', `product` = '".$_POST['product']."' WHERE `id` = '".$_POST['id']."'";
$query = mysql_query($sql);

 

So it's almost like a need to run 3 updates scripts

 

Is this possible?

 

I thought maybe with a

 

foreach()

 

but i've no idea where to start.

 

any ideas anyone?

 

thanks all

 

dave

[Psycho]

Either three queries or one complex query. If you will only ever have three items, then three queries would probably be the most straitforward approach. If you will have many items, then you should probably do some testing to see what is the most efficient.

 

Anyway, this is what the "complex" query would look like:

$query =  "UPDATE `items` SET

    `name` = CASE id
        WHEN {$_POST['id'][0]} THEN {$_POST['name'][0]}
        WHEN {$_POST['id'][1]} THEN {$_POST['name'][1]}
        WHEN {$_POST['id'][2]} THEN {$_POST['name'][2]}
    END

    product = CASE id
        WHEN {$_POST['id'][0]} THEN {$_POST['product'][0]}
        WHEN {$_POST['id'][1]} THEN {$_POST['product'][1]}
        WHEN {$_POST['id'][2]} THEN {$_POST['product'][2]}
    END

WHERE id IN ({$_POST['id'][0]}, {$_POST['id'][0]}, {$_POST['id'][0]})"

 

Of course, if the number of items is variable, you would want to script that:

$itemCount = count($_POST['id']);
$nameCases = '';
$productCases = '';
for($itemIdx=0; $itemIdx<$itemCount; $itemIdx++)
{
    $nameCases     .= "WHEN {$_POST['id'][$itemIdx]} THEN {$_POST['name'][$itemIdx]}\n";
    $productCases .= "WHEN {$_POST['id'][$itemIdx]} THEN {$_POST['product'][$itemIdx]}\n";
}

$query = "UPDATE `items` SET

    `name` = CASE id
      {$nameCases}
    END

    product = CASE id
      {$productCases}
    END

WHERE id IN (" . implode(',', $_POST['id']) . ")";

[Canman2005]

thanks man, that's a great help, ill have a try