Insert an array with multiple values, some of which are an array on its own to database

Question

ISSUE

Hello, I have multiple PHP Arrays with multiple values that I am inserting to a SQL database from an XML file. Some of those values are an array on its own (Multi-dimensional array) but the value only gets stored as "Array", none of the sub-values get passed.

EXAMPLE ARRAY

[0] => Array 
 (
  [A] => This is the Title
  [B] => This is the Description
  [C] => Array
      (
         [0] => Value 1
         [1] => Value 2
         [2] => Value 3
       )
    )

I have no problems inserting single values, so A and B will get inserted without a problem. But C will only get inserted as "Array".

INSERTION CODE

  // This is the PHP array that contains multiple items
  $Items 

  // There can be multiple Arrays in this XML so i have to loop through each one
  foreach($Items as $Item => $value) {

    $result = $mysqli->query("INSERT INTO database_name (A, B, C)
                              VALUES ('$value[A]', '$value[B]', '$value[C]');");
   }

I want to serialize the [C] and put it in a single column. But if i do it in the foreach loop like this serialize("$value[C]") only "Array" gets passed to the value. I am a bit lost.

I will appreciate any help I can get.

Easiest way? json_encode it.

Farkie
– Farkie

2016-05-16 13:54:12 +00:00
Commented May 16, 2016 at 13:54 — Farkie
– Farkie, Commented May 16, 2016 at 13:54

Gokul Shinde · Accepted Answer · 2016-05-16 13:57:49Z

2

Check each value as an array. If yes, serialize it else use as it is. Check below code, it might help you

// This is the PHP array that contains multiple items
$Items 

// There can be multiple Arrays in this XML so i have to loop through each one
foreach($Items as $Item => $value) {
    $A = is_array($value[A]) ? serialize($value[A]) : $value[A];
    $B = is_array($value[B]) ? serialize($value[B]) : $value[B];
    $C = is_array($value[C]) ? serialize($value[C]) : $value[C];

    $result = $mysqli->query("INSERT INTO database_name (A, B, C)
                            VALUES ('$A', '$B', '$C');");

  $A=$B=$C='';
}

Make empty temporary variables each time to hold new values.

answered May 16, 2016 at 13:57

Gokul Shinde

9753 gold badges10 silver badges30 bronze badges

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

Comments

Webomatik · Accepted Answer · 2016-05-16 13:58:35Z

1

The easiest and fastest way to serialize (and de-serialize later when needed) is using JSON. Your query would then become:

$result = $mysqli->query("INSERT INTO database_name (A, B, C)
VALUES ('$value[A]', '$value[B]', '" . json_encode($value[C]) . "');");

answered May 16, 2016 at 13:58

Webomatik

8367 silver badges7 bronze badges

1 Comment

Svedr Over a year ago

This was perfect, simple and it works. I will accept your answer in 5 minutes.

Alex · Accepted Answer · 2016-05-16 13:56:58Z

1

You should use prepared statement for mysqli and json_encode to serialize values:

foreach($Items as $Item => $value) {
     $stmt = $mysqli->prepare("INSERT INTO database_name (A, B, C)
                              VALUES (? , ?, ?)")
     $stmt->bind_param("sss", json_encode($value[A]), json_encode($value[B]), json_encode($value[C]));

     $stmt->execute();

or if you are sure that only C value is array:

foreach($Items as $Item => $value) {
     $stmt = $mysqli->prepare("INSERT INTO database_name (A, B, C)
                              VALUES (? , ?, ?)")
     $stmt->bind_param("sss", $value[A], $value[B], json_encode($value[C]));

     $stmt->execute();

answered May 16, 2016 at 13:56

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Comments

Loek · Accepted Answer · 2016-05-16 13:57:00Z

1

You are referencing the Array object when call $value[C]

Loop over the [C] to get its contents:

foreach($items as $item) {
    foreach($item as $itemDeep) {
        // do stuff
    }
}

answered May 16, 2016 at 13:57

Loek

4,15323 silver badges35 bronze badges

Collectives™ on Stack Overflow

Insert an array with multiple values, some of which are an array on its own to database

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related