How to Implode $_SESSION ARRAY then insert into Mysql

Question

I first retrieve data from TEST database

$Sql = "SELECT * FROM test";
$result = array();
$res = mysqli_query($conn, $Sql);

while($row = mysqli_fetch_array($res, MYSQL_NUM)){
$result[] = $row;
}

Stored Data in a SESSION

$_SESSION['Sql'] = $result;

Prints perfect from SESSION or Result

echo '<pre>';
print_r($_SESSION['Sql']);
echo '</pre>';

echo '<pre>';
print_r($result);
echo '</pre>';

Result - only 2 records in database with 3 columns

Array
(
[0] => Array
    (
        [0] => 1
        [1] => Kent Mercer
        [2] => 53
    )

[1] => Array
    (
        [0] => 2
        [1] => Linda Carter
        [2] => 63
    )

)

I then attempt to Insert into TEST2 Database

  $fields = implode(",", array_keys($_SESSION['Sql']));
  $newdata = implode(",", $_SESSION['Sql']);

  $query = ("INSERT INTO test2 ($fields)
  VALUES ('$newdata')");

  if (mysqli_query($conn, $query)) {

  echo "New record created successfully";
  } 

  else{

  echo "Error: " . $query . "<br>" . mysqli_error($conn);

  }

I receive following ERROR

 Error: INSERT INTO test2 (0,1) VALUES ('Array,Array')
 You have an error in your SQL syntax; check the manual that corresponds
 to your MySQL server version for the right syntax to use near '0,1) 
 VALUES ('Array,Array')' at line 1

I changed to MYSQLI_ASSOC & MYSQLI_BOTH. same error. Thank you for help. — Kent Mercer, Aug 24 '17 at 23:05
You're open to SQL injection. I would create a class to build your query (see [this](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter) answer for more info. And then I would map your variables to the appropriate columns using dynamic variables to insert into PDO or mysqli. — ctwheels, Aug 25 '17 at 00:18

mickmackusa · Accepted Answer · 2017-08-25T21:27:47.960

You probably have your notices turned off.

You have a multidimensional array, so you will have to access deeper before you can implode.

See your trouble:

$_SESSION['Sql']=[[1,'Kent Mercer',53],[2,'Linda Carter',63]];
var_export(implode(',',$_SESSION['Sql']));

Output:

<br />
<b>Notice</b>:  Array to string conversion in <b>[...][...]</b> on line <b>5</b><br />
<br />
<b>Notice</b>:  Array to string conversion in <b>[...][...]</b> on line <b>5</b><br />
'Array,Array'

How to prepare your data for the INSERT query:

Code: (Demo)

$_SESSION['Sql']=[[1,'Kent Mercer',53],[2,'Linda Carter',63]];

$str = implode(',', array_map(function($a){return "({$a[0]},'{$a[1]}',{$a[2]})";},$_SESSION['Sql']));
// wrap each row of data in its own set of parentheses
// this assumes that `id` and `age` are expecting numbers, and `name` is expecting a string.

echo "These are the parenthetical values:\n";
echo $str;
echo "\n\nQuery: INSERT INTO `test2` (`id`,`name`,`age`) VALUES $str";
// for best practice, wrap your tablename and columns in backticks.
// NAME is a mysql keyword

Output:

These are the parenthetical values:
(1,'Kent Mercer',53),(2,'Linda Carter',63)

Query: INSERT INTO `test2` (`id`,`name`,`age`) VALUES (1,'Kent Mercer',53),(2,'Linda Carter',63)

The next step in your learning is mysqli prepared statements with placeholders for security reasons.

Here is a snippet that has built-in error checking with the statement preparation, binding, and execution so that you can isolate any issues. (I didn't test this before posting, if there are any problems please leave a comment. I don't want anyone copying a typo or flawed piece of code.)

Code:

$_SESSION['Sql']=[[1,'Kent Mercer',53],[2,'Linda Carter',63]];
if(!($stmt=$mysqli->prepare('INSERT INTO `test2` (`id`,`name`,`age`) VALUES (?,?,?)'))){  // use ?s as placeholders to declare where the values will be inserted into the query
    echo "<p>Prepare failed: ",$mysqli->error,"</p>";  // comment this out or remove error details when finished testing
}elseif(!$stmt->bind_param("isi",$id,$name,$age)){  // assign the value types and variable names to be used when looping
    echo "<p>Binding failed: (",$stmt->errno,") ",$stmt->error,"</p>";  // comment this out or remove error details when finished testing
}else{
    foreach($_SESSION['Sql'] as $i=>$row){
        list($id,$name,$age)=$row;  // apply the $row values to each iterated execute() call
        if(!$stmt->execute()){  // if the execute call fails
            echo "<p>Execute failed: (",$stmt->errno,") ",$stmt->error,"</p>";  // comment this out or remove error details when finished testing
        }else{
            echo "<p>Success on index $i</p>";  // Insert was successful
        }
    }
}

How to Implode $_SESSION ARRAY then insert into Mysql

1 Answers1

Linked