Multi-dimensional array for use in a prepared statement

PHP
1

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.

One answer may be use many conditionals,

if(isset price){}else if(isset service)etc…

What is your opinion?

2

First things first. Where does this array come from in the first place? I smell an XY problem in the making.

3

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.

Got it?

4

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.

5

lI want to focus on these two statements separately:

I do not quite understand this…are you saying that on UPDATE the fields remain unaltered if the value for UPDATE is NULL?

6

You would just set your price variable something like this, where if price_show is available use one value if not use another value.

$price = (isset($value['price_show']) ? $value['price'] : '');

Maybe you want price_show (if available) otherwise price? I don’t know.

$price = (isset($value['price_show']) ? $value['price_show'] : $value['price']);

So your foreach is simply like this.

if($stmt=$connection->prepare('UPDATE services_list SET servicename=?,price=? WHERE serviceID=?'))
{


	$stmt->bind_param('sss',$service,$price,$ID);
	foreach ($servicedta as $value) {
		$service = $value['service'];
		$price = (isset($value['price_show']) ? $value['price_show'] : $value['price']);
		$ID =  $value['id'];
		$stmt->execute();
	}
	$stmt->close();

}
7

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`;
8

What am I suppose to actually see with all this SQL code.
Can you tell me please?I am a little confused here.

9

When you copied and pasted it into your database commandline interface which results, errors, warnings etc. did you not understand what they indicated?

10

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.

11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.