How to write values from objects in a php array to mysql through pdo, with a loop

Question

Have a php array of objects, each object has some values from the same set of keys. Have a mysql database where the fields match the set of keys in each object. Want to write values from each object in the array to mysql using pdo, and a loop. Any suggestions would be great!

PHP: (initiate some variables), 3 arrays with data turned into objects put into a "$_STORAGE" array that represents session variable:

<?php
$Q = $A1 = $A2 = $A3 = $A4 = $A5 = $A6 = "";

$array1 = array(
    "Q" => "q1",
    "A1" => "a",
    "A3" => "c",
    "A6" => "f"
);

$array2 = array(
    "Q"  => "q2",
    "A4" => "j",
    "A5" => "k",
);

$array3 = array(
    "Q" => "q3",
    "A1" => "m",
    "A2" => "n",
    "A3" => "o",
    "A4" => "p",
    "A5" => "q",
    "A6" => "r"
);

$_POST1 = (object) $array1;
$_POST2 = (object) $array2;
$_POST3 = (object) $array3;

$_STORAGE = [];
$_STORAGE['answers'][0] = $_POST1;
$_STORAGE['answers'][1] = $_POST2;
$_STORAGE['answers'][2] = $_POST3;

?>

Here's the MySql table (data_table) written to the database (data_base). The fields are the same as the object keys:

CREATE TABLE data_base.data_table
(ID INT NOT NULL AUTO_INCREMENT, Q VARCHAR(20), A1 VARCHAR(20),  A2
VARCHAR(20), A3 VARCHAR(20),  A4 VARCHAR(20),  A5 VARCHAR(20),  
A6 VARCHAR(20), Date TIMESTAMP , PRIMARY KEY (ID));

Here's the pdo code (without exception handling) that writes one object to the database:

<?php
//connect to database
include "connection_info_data_base.php";
// sql query insert into data_table
$sql = "INSERT INTO `data_table` (`Q`,`A1`,`A2`,`A3`,`A4`,`A5`,`A6`,) VALUES(:Q,
:A1, :A2, :A3, :A4, :A5, :A6)";
$stmt =  $db->prepare($sql);
$stmt->bindParam(':Q', $Q);
$stmt->bindParam(':A1', $A1);
$stmt->bindParam(':A2', $A2);
$stmt->bindParam(':A3', $A3);
$stmt->bindParam(':A4', $A4);
$stmt->bindParam(':A5', $A5);
$stmt->bindParam(':A6', $A6);

//insert $sql query to server
$stmt->execute();
?>

How to write a loop that will assign values from $_STORAGE["answers"][0] to the variables $Q, $A1, $A2, $A3, $A4, $A5, $A6, with "" as value if there is not value for that key in the object, then run the pdo code, insert the values into row 1 in database, start loop again, rewrite the variable values from $_STORAGE["answers"][1], overwriting all values and run pdo code again to write row 2 in database, and then again for row 3?

Murilo · Accepted Answer · 2017-03-22 20:52:20Z

1

I think this is what you are looking for:

$sql = "INSERT INTO 'data_table' ('Q','A1','A2','A3','A4','A5','A6',) VALUES(:Q, :A1, :A2, :A3, :A4, :A5, :A6)";
foreach ($_STORAGE['answers'] as $answer) {
    $stmt =  $db->prepare($sql);
    $stmt->bindParam(':Q', valueOrEmpty($answer, 'Q'));
    $stmt->bindParam(':A1', valueOrEmpty($answer, 'A1'));
    $stmt->bindParam(':A2', valueOrEmpty($answer, 'A2'));
    $stmt->bindParam(':A3', valueOrEmpty($answer, 'A3'));
    $stmt->bindParam(':A4', valueOrEmpty($answer, 'A4'));
    $stmt->bindParam(':A5', valueOrEmpty($answer, 'A5'));
    $stmt->bindParam(':A6', valueOrEmpty($answer, 'A6'));
    $stmt->execute();
}

function valueOrEmpty($object, $key) {
    return isset($object->{$key}) ? $object->{$key} : "";
}

edited Mar 22, 2017 at 20:52

answered Mar 22, 2017 at 20:47

Murilo

6797 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ffflabs · Accepted Answer · 2017-03-22 20:52:56Z

1

You should declare a base array with just empty values:

$base_array = array(
   "Q" => "",
  "A1" => "",
  "A2" => "",
  "A3" => "",
  "A4" => "",
  "A5" => "",
  "A6" => ""
);

Then use array_merge to ovewrite those values with the available keys in each of your arrays:

$array1 = array(
  "Q" => "q1",
  "A1" => "a",
  "A3" => "c",
  "A6" => "f"
);

$array2 = array(
  "Q"  => "q2",
  "A4" => "j",
  "A5" => "k",
);

$array3 = array(
   "Q" => "q3",
  "A1" => "m",
  "A2" => "n",
  "A3" => "o",
  "A4" => "p",
  "A5" => "q",
  "A6" => "r"
);

$mydata=[];
$mydata[] = array_merge($base_array,$array1);
$mydata[] = array_merge($base_array,$array2);
$mydata[] = array_merge($base_array,$array3);

Then create a prepared statement:

$sql = "INSERT INTO `data_table` (`Q`,`A1`,`A2`,`A3`,`A4`,`A5`,`A6`,) 
        VALUES(:Q, :A1, :A2, :A3, :A4, :A5, :A6)";
$stmt =  $db->prepare($sql);

Then iterate over $mydata and execute the insertion on each loop

foreach($mydata as $data_array) {

    $stmt->bindParam(':Q', $data_array['Q']);
    $stmt->bindParam(':A1', $data_array['A1']);
    $stmt->bindParam(':A2', $data_array['A2']);
    $stmt->bindParam(':A3', $data_array['A3']);
    $stmt->bindParam(':A4', $data_array['A4']);
    $stmt->bindParam(':A5', $data_array['A5']);
    $stmt->bindParam(':A6', $data_array['A6']);

    //insert $sql query to server
    $stmt->execute();

}

I can see you have some steps I haven't reproduced like casting the arrays into objects, then using a $_STORAGE variable, whatever. That part I didn't consider for this answer and it seems irrelevant for the scope of the question.

answered Mar 22, 2017 at 20:52

ffflabs

17.6k5 gold badges57 silver badges79 bronze badges

3 Comments

Murilo Over a year ago

The only problem here is that he explicitly requested to iterate over an array of objects not arrays

ffflabs Over a year ago

Yup, you're right. It just cut a corner there because it seemed fruitless to cast the arrays as objects.

F. Certainly. Over a year ago

Just trying Murilo's answer. The cast is fruitless but represents the actual more complex code. Form questions are answered and not written directly to a database but passed around in a session variable. When they are all answreed they are picked from the session variable, an array of objects, and written to the db.

Don't Panic · Accepted Answer · 2017-03-22 22:56:57Z

1

Prepare the statement as you already are.

$sql = "INSERT INTO `data_table` (`Q`,`A1`,`A2`,`A3`,`A4`,`A5`,`A6`,) 
                          VALUES (:Q, :A1, :A2, :A3, :A4, :A5, :A6)";
$stmt =  $db->prepare($sql);

Define your complete set of keys:

$complete_keys = ['Q','A1','A2','A3','A4','A5','A6'];

Iterate $_STORAGE. For each item there, create an insert array using the complete keys, and the value from the $_STORAGE item, and execute the prepared statement with that array:

foreach ($_STORAGE['answers'] as $answer) {
    $values = [];
    foreach ($complete_keys as $key) {
        $values[":$key"] = isset($answer->$key) ? $answer->$key : null;
    }
    $stmt->execute($values);
}

answered Mar 22, 2017 at 22:56

Don't Panic

41.9k11 gold badges70 silver badges87 bronze badges

1 Comment

F. Certainly. Over a year ago

Thnx, that's great.

Hayden · Accepted Answer · 2017-03-22 23:26:18Z

0

You can do it with just one query but you'll have to use positional parameters instead of named parameters. If you want to use named parameters, you will need to execute each query at the end of each iteration as you cannot have two parameters of the same name in a query.

$query = "INSERT INTO `data_table` (`Q`,`A1`,`A2`,`A3`,`A4`,`A5`,`A6`) VALUES ";

$parameter_values = [];
$parameters = [];

foreach($_STORAGE['answers'] as $answer) {

  $parameter_values = array_merge($parameters, [
    $answer->Q, 
    $answer->A1, 
    $answer->A2, 
    $answer->A3, 
    $answer->A4, 
    $answer->A5, 
    $answer->A6, 
  ]);

  $parameters[] = "(?,?,?,?,?,?,?)";

}

$query.= implode(',', $parameters) . ';';

$stmt =  $db->prepare($query);
$stmt->execute($parameter_values);

Your SQL query should look something like...

INSERT INTO `data_table` (`Q`,`A1`,`A2`,`A3`,`A4`,`A5`,`A6`) VALUES
  (?,?,?,?,?,?,?),(?,?,?,?,?,?,?),(?,?,?,?,?,?,?);

answered Mar 22, 2017 at 23:26

Hayden

2,1222 gold badges14 silver badges20 bronze badges

1 Comment

F. Certainly. Over a year ago

Thnx,that's great.

Collectives™ on Stack Overflow

How to write values from objects in a php array to mysql through pdo, with a loop

4 Answers 4

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related