-1

The following json array data is fetched to a php script to ultimately create a record in a MySQL database:

{"address": "4 Ficticious Ave", 
"city": "Miami", 
"country": "United States", 
"email": "jane_doe@gmail.com", 
"first_name": "Jane", 
"last_name": "Doe", 
"state": "FL", 
"zip_code": "03423", 
"response_data": 
"[{"key":"7122", "value":"37-52"},
{"key":"7123","value":"Female"},
{"key":"7124","value":"$35,000 to $50,000 USD"},
{"key":"6176","value":"Miami"},
{"key":"6177","value":"FL"},
{"key":"6179","value":"United States"}]"}

I've gotten the following php script to get data from the json array and create a record in a MySQL database by inserting the data. However, all but the data from the response_data key|value pairs gets populated -- the associated MySQL columns are all null:

// Identify the content as json
header("Content-Type: application/json; charset=UTF-8");

// get the contents of the JSON file
$data = file_get_contents("php://input");


//this normalize routine was provided by @Elementary in 
// response to my request on Stack Overflow 09052018...

//begin normalize the json in order to be properly decoded
$start=strpos($data,':',strpos($data,'response_data'));
$get=substr($data,$start+1,strrpos($data,'"')-$start);
$data=str_replace($get,trim(trim($get),'"'),$data);
//end normalize


//decode JSON data to PHP array
$content = json_decode($data, true);

//Fetch the details of customer
$Cust_Fname = $content['first_name'];
$Cust_Lname = $content['last_name'];
$Cust_Email = $content['email'];
$Street_Address = $content['address'];
$City = $content['city'];
$State = $content['state'];
$Country = $content['country'];
$Postal_Code = $content['zip_code'];

//also fetch the appended "array" of key/value fields...
$Response_AgeKey = $content['reponse_data'][0]['key'];
$Response_GenderKey = $content['reponse_data'][1]['key'];
$Response_IncomeKey = $content['reponse_data'][2]['key'];
$Response_CityKey = $content['reponse_data'][3]['key'];
$Response_StateKey = $content['reponse_data'][4]['key'];
$Response_CountryKey = $content['reponse_data'][5]['key'];
$Response_Age = $content['reponse_data'][0]['value'];
$Response_Gender = $content['reponse_data'][1]['value'];
$Response_Income = $content['reponse_data'][2]['value'];
$Response_City = $content['reponse_data'][3]['value'];
$Response_State = $content['reponse_data'][4]['value'];
$Response_Country = $content['reponse_data'][5]['value'];

The MySQL database shows the record having been created and containing all fields of data except for data coming from response_data. Thinking there might be a problem with my syntax, I have tried to replace the response_data variables with this:

//try this syntax instead…
$Response_AgeKey = $content['reponse_data']['key'][0];
$Response_GenderKey = $content['reponse_data']['key'][1];
$Response_IncomeKey = $content['reponse_data']['key'][2];
$Response_CityKey = $content['reponse_data']['key'][3];
$Response_StateKey = $content['reponse_data']['key'][4];
$Response_CountryKey = $content['reponse_data']['key'][5];
$Response_Age = $content['reponse_data']['value'][0];
$Response_Gender = $content['reponse_data']['value'][1];
$Response_Income = $content['reponse_data']['value'][2];
$Response_City = $content['reponse_data']['value'][3];
$Response_State = $content['reponse_data']['value'][4];
$Response_Country = $content['reponse_data']['value'][5];

The same result is obtained -- a record is created in the MySQL database, but the response_data array fields do not populate the associated MySQL columns. I could use help in learning of some other way to identify and get data from the response_data array. Note please that I do not want to insert the response_data array into MySQL as a json array -- instead, the data from the array should go into associated MySQL columns!

Celia
  • 11
  • 3
  • 1
    You should not have to normalise valid json. Who creates this JSONString, you or do you get it from somewhere else. – RiggsFolly Sep 06 '18 at 07:49
  • Hi, the json data is coming from a webhook that captures user input on a another server. Without the normalization, the record was not created in MySQL. – Celia Sep 06 '18 at 07:55
  • 1
    `reponse` !== `response` so it is just a TYPO, your first attempt will work in you spell things correctly – RiggsFolly Sep 06 '18 at 07:57
  • 1
    Yea I realise the JSON is invalid, but it should not be, I was going to say if it was you that is creating it, you should post that code also and we can fix the JSONString generation as well – RiggsFolly Sep 06 '18 at 07:58

1 Answers1

0

I've resolved the issue and am now able to get all of the data from the json array to insert into associated columns in MySql. The resolution involved fetching the details of 'response_data' into an associative array in php:

//also fetch the appended "array" of key/value fields...
$Response_Array = $content['response_data'];

Then, I viewed the php array to verify the data passed and to note the structure:

//look at the array to verify data is fetched
var_dump($Response_Array);

Finally, I brought values of the key|value pairs into variables:

//bring the values of response_data array into variables 
$Response_Age = $Response_Array[0]['value'];
$Response_Gender = $Response_Array[1]['value'];
$Response_Income = $Response_Array[2]['value'];
$Response_City = $Response_Array[3]['value'];
$Response_State = $Response_Array[4]['value'];
$Response_Country = $Response_Array[5]['value'];

Now, when the MySQL record is created, all data is inserted as needed [MySQL insertion code and error routines not provided].

Thanks all for the help that sent me in the direction I needed to solve.

Celia
  • 11
  • 3