CODEWITHSUNDEEP
phpmysql
sanar
sanar

Reputation: 447

Inserting JSON data into MySQL

I have a solution with PHP as server-side, Vue JS for front-end and MySQL as DB.

The UI bundles data as JSON and posts it to PHP through axios, and PHP in turn will decode the JSON and inserts into MySQL.

Here is my PHP code (omitting the other lines like connecting etc.):

$data = file_get_contents("php://input"); 
$jsonData = json_decode($data, true); 

//echo var_dump($jsonData);    
// Below is the jsonData as dumped
    //[{"candidate_id":"SM_009","FirstName":"test","LastName":"dummy","DOB":"1990-06-05"}]

$tableName = 'profile';  
foreach((array)$jsonData as $id=>$row) {

      $insertPairs = array();
      foreach ((array)$row as $key=>$val) {          
          $insertPairs[addslashes($key)] = addslashes($val);         
      }      
      $insertKeys = '`' . implode('`,`', array_keys($insertPairs)) . '`';
      $insertVals = '"' . implode('","', array_values($insertPairs)) . '"';
      $sql = "INSERT INTO `{$tableName}` ({$insertKeys}) VALUES ({$insertVals});" ;
      //echo var_dump($sql);
      $stmt = $con->prepare($sql);
      $stmt->execute();   
}

However, here is the actual insert statement generated, which is obviously wrong.

INSERT INTO `profile` (`0`) VALUES ("[{\"candidate_id\":\"SM_009\",\"FirstName\":\"test\",\"LastName\":\"dummy\",\"DOB\":\"1990-06-05\"}]");

Where am I doing wrong? Any help would be greatly appreciated..

Thanks

Note: When I use the same dumped jsondata as hardcoded string, it works.

$data ='[{"candidate_id":"SM_009","FirstName":"test","LastName":"dummy","DOB":"1990-06-12"}]';
//$data = file_get_contents("php://input");

...

Generated statement:

 "INSERT INTO `profile` (`candidate_id`,`FirstName`,`LastName`,`DOB`) VALUES ("SM_009","test","dummy","1990-06-12");"

Upvotes: 0

Views: 7870

Answers (2)

Igor Ilic
Igor Ilic

Reputation: 1368

The reason you are still receiving the json in your insert statement is because you decoded the first part of your json string and received the data array which still contains the json string inside of it. To resolve this just decode the $jsonData variable again like so:

<?php 
$data = file_get_contents("php://input"); 
$jsonData = json_decode($data, true);
$jsonData = json_decode($jsonData['data'], true); //Decode the data as well 

$tableName = 'profile';
foreach((array)$jsonData as $id => $row){
    $insertPairs = array();
    foreach ((array)$row as $key=>$val) {
      $insertPairs[addslashes($key)] = addslashes($val);
    }
    $insertKeys = '`' . implode('`,`', array_keys($insertPairs)) . '`';
    $insertVals = '"' . implode('","', array_values($insertPairs)) . '"';
    $sql = "INSERT INTO `{$tableName}` ({$insertKeys}) VALUES ({$insertVals});" ;
    $stmt = $con->prepare($sql);
    $stmt->execute(); 
}

You can check out a working example here: https://ideone.com/i86iVP

Upvotes: 0

MrSmile
MrSmile

Reputation: 1233

You can do like this:

$jsonString = '{"data":[{"candidate_id":"SM_009","FirstName":"test","LastName":"dummy","DOB":"1990-06-12"}]}';
$jsonArray = json_decode($jsonString,true);
$data = $jsonArray['data'];

//$data = json_decode(file_get_contents("php://input"),true);
//$json = json_decode($data, true); $json = $data['data'];
//json_decode($_GET['data']);

$tableName = 'profile';
foreach((array)$data as $id=>$row) {

$insertPairs = array();
foreach ((array)$row as $key=>$val) {
    $key = addslashes($key);
    $val = addslashes($val);
    $insertPairs[] = " `{$key}` = '{$val}' ";
}

$sqlInsert = implode(", ", $insertPairs);
$sql = "INSERT INTO `{$tableName}` SET {$sqlInsert} ";
echo var_dump($sql);
/*
 string(126) "INSERT INTO `profile` SET `candidate_id` = 'SM_009' , `FirstName` = 'test' , `LastName` = 'dummy' , `DOB` = '1990-06-05' "
 */
//    $stmt = $con->prepare($sql);
//    $stmt->execute();
}

Upvotes: -1

Related Questions