I am learning about serialization and although I understand the concept I am not familiar with its limitations.
The following is a fictitious scenario, say I have a table called week and inside the table week there are 3 columns. The third column holding serialized data as follows:
INSERT INTO `week` (`week_id`, `meal_code`, `meal_id`) VALUES
(1, 'week12016', 'a:7:{i:0;s:1:"1";i:1;s:1:"2";i:2;s:1:"3";i:3;s:1:"4";i:4;s:1:"5";i:5;s:1:"6";i:6;s:1:"7";}'),
And then I have I have another table called meal and inside meal is as follows:
INSERT INTO `meal` (`meal_id`, `meal_name`) VALUES
(1, 'Chorizo tapas'),
(2, 'Mediterranean halloumi burger'),
(3, 'Lamb Moussaka'),
(4, 'Snappy Asian Fish En Papillote'),
(5, 'Puttanesca Risotto'),
(6, 'Mexican Pork Chilli With Apple Salsa'),
(7, 'Warm Greek Salad');
Now say I have some HTML with some check-boxes as follows:
Monday []
Tuesday []
Wednesday []
Thursday []
Wednesday []
Thursday []
Friday []
Saturday []
Sunday []
And then I decide to add another meal with an meal_id of 8 and tick the Monday box, is it possible to update the serialized array to reflect the tick box so it shows as follows:
'a:7:{i:0;s:1:"8";i:1;s:1:"2";i:2;s:1:"3";i:3;s:1:"4";i:4;s:1:"5";i:5;s:1:"6";i:6;s:1:"7";}'),
see how "8" has now gone in place of the "1". The meal table is a little irrelevant since its a serialized array but I have added it for context.
I want to know if its possible to do what I am describing?
Hypothetically I am guessing something like this:
$monday=$_POST['monday'];
$result = mysqli_query($conn,"SELECT meal_id from WEEK");
$array = unserialize( $result );
$array[0] = $meal_id;
$sfood=serialize($array);
if ($_POST['weekday'] == 'monday')
{
mysqli_query($conn, "UPDATE week SET meal_id ('$sfood') ");
}
I know the above isn't correct but it's what I've tried and have no idea what to go with next.
The only time you should use serialized data in a database is when it is completely irrelevant to the data relationship... Think storing CSS rules or something, they could be used when you select a row to help style it, but have no bearing on conditions or relationships in the actual data.
That said, your $result is not actually what you think it is, it's a sql result set... you need to get a row, or rows, out of it:
while($row = $result->fetchObject()){
$meal_id = $row->meal_id;
}
You also need to close the connection when you have what you want, BEFORE running another query:
$result->close
But since you seem to just be getting started, you should probably learn about PDO as well. In my experience it's a better fit for a lot of projects.