Update Foreach Loop Multi Records

[madness69]

Hi there, hope have some help. I have a form where a user select time and days of the week (monday, tuesday..) that he is available. The insert script works fine, it insert and the days of week he checked in the table, the only problme is when i try to update each of them.

I used this code above, but is not working, instead of updating/inserting days that is selected is inserting multi-records with the same day of the week value.

here it is:

foreach($weekDay as $DaysofWeek){


$sql_days = "UPDATE available SET day_time = '$day_time', week_day = '$DaysofWeek' WHERE user_id = '$id_user'";


$update_availability = mysql_query($sql_days);


}

[mac_gyver]

data you store should have a unique id, typically an auto-increment column, that you can reference it by.

 

your update form would then submit the unique id/value for each row that was displayed for updating.

 

your update logic would then loop over the data, getting the unique id and the value for each submitted row of data to use in the update query. you would also still need to enforce ownership in the query, by making sure that only rows that the currently logged in user 'owns' can be updated.

[madness69]

So how should this be coded? Above i leave the strcuture of database:

days_week:

- id;

- day;

Records Inserted: Monday, Tuesday...Sunday;

 

available:

- id_available (primary, auto-incremento);

- user_id;

- day_time;

- week_day;

Inseted Records: 6, 1, 1:00, Monday;

7, 1, 1:00, Tuesday;

Edited September 20, 2014 by madness69

[mac_gyver]

there's no need for your days_week table. just use the existing relationship between the day numbers and day names that either mysql or php defines (there's four different choices, pick one, which you are likely already using in your calendar, and use it throughout your code.)

 

next, you should store the day number, not the day name, in your available table. it will take less storage and if you are searching or manipulating the data using the day number, it will result in a faster query than operating on the day name string.

 

your available table has a unique id. you would use that in your update form to identify which row the submitted data belongs with, typically as an array field name index value. since you haven't shown your update form, cannot specifically help you beyond telling you what you should be doing.

Edited September 20, 2014 by mac_gyver
edit: changes to day number/day name text