help with mysql update

[tmurphy]

i can't get mysql to update multiple records. Can someone help out ?

I run a while ($row=mysql_fetch_array($mysql_result)) to get the form i need to do the update. That works fine but when i try to create an array to write the updated information back to the database it doesn't work.

[[deleted]]

You can't write an array back to the database. You must update the database with queries, such as:

UPDATE table SET somefield = 'thisvalue' WHERE somefield = 'currentvalue

[tmurphy]

i have that

$sql = "UPDATE customers
SET
b_hbday = \"$b_hbday\"
WHERE id = \"$id\"
";

$result = @($sql,$connection) or die("Couldn't execute query.")

if i have mutilple records like

TD><input type="hidden" value="3" name="id[]"></TD>
<TD> somename</TD>
<TD>somespouse</TD>
<TD>somelastname</TD>
<TD>somephone</TD>
<TD><input type="checkbox" name="b_hbday[]" value="Y"></TD>
</TR>
<TR ALIGN="CENTER">
<TD><input type="hidden" value="4" name="id[]"></TD>
<TD>somenextperson</TD>
<TD>nextpersonspouse</TD>
<TD>nextpersonlastname</TD>
<TD>nextpersonphone</TD>
<TD><input type="checkbox" name="b_hbday[]" value="Y"></TD>
</TR>

how do i make mysql update each record?

[[deleted]]

foreach($id as $key=>$value) {
$sql = "UPDATE customers
SET
b_hbday = '{$b_hbday['$key']}'
WHERE id = '$value'
";

$result = @($sql,$connection) or die("Couldn't execute query.");
}

give that a try....

[tmurphy]

got no errors but it is not writing the value of Y to the the database

[[deleted]]

Just before the query line, put:

echo "$sql<BR>\n";

so that you can see what the queries look like...

[tmurphy]

this is what i did

foreach($id as $key=>$value) {
echo "$sql<BR>\n";
$sql = "UPDATE customers
SET
b_hbday = '{$b_hbday['$key']}'
WHERE id = '$value'
";

$result = @($sql,$connection) or die("Couldn't execute query.");
}

and this is what i got

UPDATE customers SET b_hbday = '' WHERE id = '3'

[[deleted]]

Try changing the $sql = line to:

$sql = "UPDATE customers
SET
b_hbday = '{$b_hbday[$key]}'
WHERE id = '$value'
";

The change is that I removed the quotes from around $key....

[tmurphy]

ok that worked but there is one more little detail if i check the first one it updates fine but if i only check the second one it update the first one. This is what i get when i check the second check the second box which is 4 not 3

UPDATE customers SET b_hbday = 'Y' WHERE id = '3'

[[deleted]]

If the value of b_hbday always going to be Y if it is checked? if so, dump the hidden fields and put the id as the value rather than Y....

then do this:

foreach($b_hbday as $value) {
echo "$sql<BR>\n";
$sql = "UPDATE customers
SET
b_hbday = 'Y'
WHERE id = '$value'
";

[tmurphy]

this is what i get when i try that

Warning: Invalid argument supplied for foreach()

[tmurphy]

ok I put everything back the way it was and added

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

for ($i=0; $i<$value; $i++) {

echo "$sql<BR>\n";

$sql = "UPDATE customers
SET
b_hbday = '{$b_hbday[$key]}'
WHERE id = '$value'
";

and I added another record so we have 3,4,&5 now. when i check 3 it updates fine but when I update 5 only I get this

UPDATE customers SET b_hbday = 'Y' WHERE id = '3'
UPDATE customers SET b_hbday = 'Y' WHERE id = '3'
UPDATE customers SET b_hbday = 'Y' WHERE id = '3'
UPDATE customers SET b_hbday = '' WHERE id = '4'
UPDATE customers SET b_hbday = '' WHERE id = '4'
UPDATE customers SET b_hbday = '' WHERE id = '4'
UPDATE customers SET b_hbday = '' WHERE id = '4'
UPDATE customers SET b_hbday = '' WHERE id = '5'
UPDATE customers SET b_hbday = '' WHERE id = '5'
UPDATE customers SET b_hbday = '' WHERE id = '5'
UPDATE customers SET b_hbday = '' WHERE id = '5'

hope i didn't make things worse.