CODEWITHSUNDEEP
phpmysqlarraysjsonpdo
Shiori Naka
Shiori Naka

Reputation: 1

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

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)

Upvotes: 0

Views: 98

Answers (1)

grandpalacko
grandpalacko

Reputation: 31

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.

Upvotes: 1

Related Questions