update mysql table using php array?

acorc

Hi,

I was wondering is it possible to perform the below sql query with an array? - it's not working for me.

Could it be that my syntax is wrong?

$SQL2 = "UPDATE $table1 SET ".room[$i]."=' ', ".bRoom[$i]."=' ', ".room[$i]Company."=' ', ".room[$i]Initials."=' ', ".room[$i]dateAdded"=' ' WHERE id='$id'";

It works perfectly when i replace the array [$i] with 1 or 2, etc.

My code is checking what record's were added over a week ago - it is supposed to then update those records by clearing their data.

Here's my code:

=================
$length_days = 7;
for($i=1; $i < 7; $i++)
{
$id = $row[id]."\t";
${"room".$i} = $row["room".$i]."\t";
${"bRoom".$i} = $row["bRoom".$i]."\t";
${"room".$i."Company"} = $row["room".$i."Company"]."\t";
${"room".$i."Initials"} = $row["room".$i."Initials"]."\t";
${"room".$i."dateAdded"} = $row["room".$i."dateAdded"]."\t";

list( $year,$month,$day) = split ('[/.-]', ${"room".$i."dateAdded"});
echo $hold_until_date = date('Y-m-d', mktime(0, 0, 0, $month, $day + $length_days, $year));

if ($hold_until_date < date("Y-m-d"))
{

$SQL2 = "UPDATE $table1 SET ".room[$i]."='', ".bRoom[$i]."='', ".room[$i]Company."='', ".room[$i]Initials."='', ".room[$i]dateAdded"='' WHERE id='$id'";

$result2 = @($SQL2) or die("could not complete your query");

mysql_data_seek($result2,0);

}

Any help would be greatly appreciated!
Thanks in advance

bubblenut

That will not work. What is the query you are trying to get at the end of all this?

acorc

Sorry if i haven't explained this very clearly.

I have a table which is in the format of:
room1, bRoom1, room1Company, room1Initials, room1dateAdded,

room2, bRoom2, room2Company, room2Initials, room2dateAdded,.... etc... right up until room7dateAdded...

The maximum length of time a room can be HELD for is 7 days. The room{$i}dateAdded field is checked to find out if the room was held for more than a week. (<- I have that bit working).

This is where i'm having the difficulty:

I want to clear the info that is in the fields that HELD has expired on, which is the below code:

$SQL2 = "UPDATE $table1 SET room1 =' ', bRoom1 =' ', room1Company =' ', room1Initials =' ', room1dateAdded =' ' WHERE id='$id'";

This is working fine for me but i need it in an array.

Does this make any more sense?

I was thinking that my syntax wasn't just right.

That's me snookered if this doesn't work!

But thanks bubblenut for taking time to look at my problem!