0

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");"
Improve this question
6
  • 2
    I normally serialize the data before saving then unserialize when retrieving. Commented Jun 19, 2018 at 10:03
  • Your $insertKeys is 0, while I think you want it to be all the keys from the array, while the values then should be inserted to their keys? Commented Jun 19, 2018 at 10:04
  • Using the json sample you provided and your code I get a normally formed insert statement (see example here: ideone.com/BWHEhB ). Which probably means that the data you receive from the input is not properly decoded. Can you post the dump of your raw input from $data var ? Commented Jun 19, 2018 at 10:06
  • 1
    To store array in database you should use serialize() function and if you want that array in json then while retrieving that array unserialize it and convert to json format. Commented Jun 19, 2018 at 10:09
  • @IgorIlic here is the raw $data: {"data":"[{\"candidate_id\":\"SM_009\",\"FirstName\":\"dffdf\",\"LastName\":\"dffdf\",\"DOB\":\"1990-01-02\"}]"} Commented Jun 19, 2018 at 10:14

2 Answers 2

Reset to default
0

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

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

Comments

-1

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();
}
Improve this answer

4 Comments

here is the insert statement generated using your code: "INSERT INTO profile SET 0 = '[{\"candidate_id\":\"SM_009\",\"FirstName\":\"3434\",\"LastName\":\"erer\",\"DOB\":\"1990-01-09\"}]'"
if you have json-array before foreach - You must convert it to regular array. For example, $array = json_decode($jsonData). If you use json_decode, it means that php://input receives JSON data.
I have modified the code as below: $data = json_decode(file_get_contents("php://input"),true); $json = $data['data']; but still the same issue
Make sure that you receive a json string by "file_get_contents" function. See above - I tried to emulate your situation and use a json string instead of "php://input".

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.