Take a look here this hastebin…beneath the prepared statement you will see a multidimensional array.
The inner arrays have an ID which corresponds to a row in a DB table.
So the aim is to update the respective fields in the table…as seen in the inner array.
So problem 1 is looping through the array and updating the fields(that is probably easy…a foreach loop will do the job
Problem 2 and far more important,you will see that the second inner array has an element which the first does not…price show…and that is an example…there might be cases that either of the arrays(which of course maybe more than two) lack an element from the 4 (the max can be)(and ID plus service data such as name,price…etc)
How am I going to construct the prepared statement that caters for this fact?
Varying number of elements for each inner array.
look at this fiddle…click edit and then update some values in the input boxes(there are services with their prices)…upon updating the values see the HTML of the inputs…they get a name attribute value of this form: form[0][service](if it is the service input that is being edited)…
with this way only the updated values are serialized and sent to the server as an array(with ajax)…the IDs are get with a separate logic…in the server that IDs get to be attached to the arrays which hold the updated values from the inputs.
One possibility might be to have the query use all possible fields. As long as a field can accept a NULL or has a DEFAULT value the row should take what values do exist.
If the table was created so that a field must have a value and you can’t do an ALTER then you could use PHP code to check for missing values and either skip that row or assign values where they’re missing.
Sorry, I was thinking INSERT instead of UPDATE. With UPDATE if you try to insert a NULL into a NOT NULL field it will error. You could do UPDATE IGNORE but that is probably not what you want. Try pasting this into your database CLI to see the difference
CREATE TABLE `temp_test_table` (
`auto_field` TINYINT AUTO_INCREMENT
, `default_null_field` VARCHAR(11) DEFAULT NULL
, `not_null_field` INT(5) NOT NULL DEFAULT 99999
, PRIMARY KEY (`auto_field`)
) ENGINE=InnoDB DEFAULT CHARSET='utf8mb4';
SET @nulltext = null;
INSERT INTO `temp_test_table`
(`default_null_field`)
VALUES (@nulltext);
SELECT `auto_field`, `default_null_field`, `not_null_field`
FROM `temp_test_table`;
SET @autonum1 = null;
SET @nulltext1 = 'defaultnull';
SET @notnullnum1 = 12345;
INSERT INTO `temp_test_table`
(`auto_field`, `default_null_field`, `not_null_field`)
VALUES (@autonum1, @nulltext1, @notnullnum1);
SELECT `auto_field`, `default_null_field`, `not_null_field`
FROM `temp_test_table`;
SET @autonum2 = 5;
SET @nulltext2 = null;
SET @notnullnum2 = null;
UPDATE `temp_test_table`
SET `auto_field` = @autonum2
, `default_null_field` = @nulltext2
, `not_null_field` = @notnullnum2
WHERE `auto_field` = 1;
SELECT `auto_field`, `default_null_field`, `not_null_field`
FROM `temp_test_table`;
SET @autonum3 = 9;
SET @nulltext3 = null;
SET @notnullnum3 = null;
UPDATE IGNORE `temp_test_table`
SET `auto_field` = @autonum3
, `default_null_field` = @nulltext3
, `not_null_field` = @notnullnum3
WHERE `auto_field` = 1;
SELECT `auto_field`, `default_null_field`, `not_null_field`
FROM `temp_test_table`;
DROP TABLE `temp_test_table`;
When you copied and pasted it into your database commandline interface which results, errors, warnings etc. did you not understand what they indicated?
A bit late to the party, but I would construct the form in a way that every field (necessary for the update) is contained. that’s way easier to do that cater for variable size updates.