How to insert an array in a MySQL table with PHP?

Question

I need to migrate a JSON file that has my users data to a table in MySQL with PHP, I already did a connection, with the DB I created a table were y decode the JSON into an array. What I cannot do is insert the array data into the MySQL Users table.

My JSON looks like this:

{
    "nombre":"asudhau",
    "apellido":"uhsaudhau",
    "pass":"$2y$10$KAg3wt7bBjphgdCNJf4VXe.an8lOnlOvWVdVsh2Qsws0dbhWiDwkO",
    "mail":"[email protected]",
    "sexo":"F",
    "id":34,
    "nacimiento":"2016-10-06"
}
{
    "nombre":"abudasd",
    "apellido":"hasdua",
    "pass":"$2y$10$c781KdL3ERgDCnP6MR28xuf\/dnKjuVajklc0uSj2FnBrZSB1H88Si",
    "mail":"[email protected]",
    "sexo":"F",
    "id":35,
    "nacimiento":"1990-02-03"
}
{
    "nombre":"audihaiudh",
    "apellido":"uiahsdiuahdi",
    "pass":"$2y$10$Q7VjafKxt\/kuJS1BrslF0uSZPwHe7Hvp6olMxetgY31KcmMT9dIo2",
    "mail":"[email protected]",
    "sexo":"F",
    "id":36,
    "nacimiento":"1999-02-03"
}

I got the values of each user this way:

$conn = new PDO('mysql:host=localhost;dbname=dh_usuarios;charset=UTF8mb4', 'root', '');
   $jsondata = file_get_contents('../usuarios.json');
   $usuariosArray = explode(PHP_EOL, $jsondata);

    foreach ($usuariosArray as $usuario) {
      $jsondata = json_decode($usuario.PHP_EOL, true);
      $jsonarray[] = array($jsondata);
    }
      foreach ($jsonarray as $row) {

      $nombre = $row[0]['nombre'];
      $apellido = $row[0]['apellido'];
      $pass = $row[0]['pass'];
      $mail = $row[0]['mail'];
      $sexo = $row[0]['sexo'];
      $id = $row[0]['id'];
      $nacimiento = $row[0]['nacimiento'];
}

Now I want to insert this users data to a table that i have already created in MySQL and I don't know how to do it.

I tried this, but it just doesn't insert the data.

$stmt = $conn->prepare('
    INSERT INTO dh_usuarios(nombre, apellido, pass, mail, sexo, id, nacimiento)
    VALUES(:nombre, :apellido, :pass, :mail, :sexo, :id, :nacimiento);
   ');

  $stmt->execute([
    ':nombre' => $nombre,
    ':apellido' => $apellido,
    ':pass' => $pass,
    ':mail' => $mail,
    ':sexo' => $sexo,
    ':id' => $id,
    ':nacimiento' => $nacimiento,
  ]
);

Help me what should I do? (sorry for my bad English, I'm from Argentina)

i don't seems to be an error from the database, i think i made a mistake somewhere but i can't figure it out.... — Shiori Naka
– Shiori Naka, Commented Nov 6, 2016 at 4:45
Try var_dump($jsondata) to check if you have something there. — Louys Patrice Bessette
– Louys Patrice Bessette, Commented Nov 6, 2016 at 4:48
ok. Then try to echo $jsonarray[0]['nombre']... If it's ok, then check if there is a PDO error. Add this after "execute" : if (!$stmt) { print_r($conn->errorInfo()); }else{ echo "stmt is ok."; } — Louys Patrice Bessette
– Louys Patrice Bessette, Commented Nov 6, 2016 at 6:01

Community · Accepted Answer · 2017-05-23 11:48:44Z

There are two ways to fix your code.

1) Fixing JSON format (recommended)

Your JSON file is not valid, you have multiple root elements. If you can change it, that would be the first step to do:

[
    {...},
    {...},
    {...}
]

After you fixed the format of your JSON file, you can simplify your PHP code. The following part:

$usuariosArray = explode(PHP_EOL, $jsondata);



foreach ($usuariosArray as $usuario) {
    $jsondata = json_decode($usuario.PHP_EOL, true);
         $jsonarray[] = array($jsondata);
    }
}

becomes:

$jsonarray = json_decode($jsondata, true);

Now you can loop through the array (please notice that [0] index has been removed):

foreach ($jsonarray as $row) {
    $nombre = $row['nombre'];
    $apellido = $row['apellido'];
    $pass = $row['pass'];
    $mail = $row['mail'];
    $sexo = $row['sexo'];
    $id = $row['id'];
    $nacimiento = $row['nacimiento'];
}

2) Replacing explode with regex

If you can't fix the format of your JSON file, explode() needs to be replaced because it currently explodes the JSON file by each row (you are using end of line character as delimiter).

With using preg_match_all you can retrieve each object from your invalid JSON file, using a complex (and not bulletproof) regex pattern:

$pattern = '
/
\{              # { character
    (?:         # non-capturing group
        [^{}]   # anything that is not a { or }
        |       # OR
        (?R)    # recurses the entire pattern
    )*          # previous group zero or more times
\}              # } character
/x
';

preg_match_all($pattern, $jsondata, $matches);

Now you can loop through your $matches and decode them as JSON:

foreach ($matches[0] as $match) {
  $jsonarray[] = json_decode($match, true);
}

The last step is the same as above.

I change everything as you say but still doesnt insert it to the table...

Collectives™ on Stack Overflow

How to insert an array in a MySQL table with PHP?

1 Answer 1

1) Fixing JSON format (recommended)

2) Replacing explode with regex

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1) Fixing JSON format (recommended)

2) Replacing explode with regex

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related