0

Previously I have posted a similar question on passing JSON data to drop down menu using PHP. How can i arrange data array from API request into HTML SELECT OPTION LIST?

Right now I would like to INSERT the JSON datat in to MySQL database

Below is my api_redcap.php code for calling the data from other web server:

<?php 
     $data = array(
          'token' => '4B0D42AB9D061C0FADD724D2E908349D',
          'content' => 'report',
          'format' => 'json',
          'report_id' => '71',
          'rawOrLabel' => 'label',
          'rawOrLabelHeaders' => 'label',
          'exportCheckboxLabel' => 'false',
          'returnFormat' => 'json'); 

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL, 'http://redcap-virtualbox/redcap/api/');

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

curl_setopt($ch, CURLOPT_VERBOSE, 0);

curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);

curl_setopt($ch, CURLOPT_AUTOREFERER, true);

curl_setopt($ch, CURLOPT_MAXREDIRS, 10);

curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');

curl_setopt($ch, CURLOPT_FRESH_CONNECT, 1);

curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($data, '', '&'));

$output = curl_exec($ch);

$json = json_decode($output);    
?>

Below is my output from api_redcap.php.:

[{"project_code":"16001","dept":"Orthopaedic Surgery (ORTHO)","group":"","name":"Jameson Lua Yao Chung"},{"project_code":"16002","dept":"Rheumatology Allergy & Immunology (RAI)","group":"","name":"Koh Ee Tzun"},{"project_code":"16003","dept":"Orthopaedic Surgery (ORTHO)","group":"","name":"Ang Wei Luong"},{"project_code":"16004","dept":"Rheumatology Allergy & Immunology (RAI)","group":"","name":"Lim Xin Rong"},{"project_code":"16005","dept":"Physiotherapy (PT)","group":"","name":"Li Kun Man"}]

My table name is called PROJECT_CODE in MySQL database and the attributes are as follow:

Table Name - PROJECT_CODE Field Name: PROJECT_code, DEPARTMENT, REQUESTOR,

I would like to insert the project_code, dept and name variables from JSON into the MySQL table fields respectively. Please help. Thanks

Les88
  • 35
  • 1
  • 6
  • The variable $json should be an array that you can access like any other array object. You just need to execute an MyQSL INSERT after getting the values. If you want to see the structure of the array, use ```echo "

    json array:

    ".print_r($json,true)."
    \n";``` just after your ```json_decode``` statement.     – Sloan Thrasher Jun 08 '18 at 02:29
  • @SloanThrasher Thanks but could you show me a sample code on how is the MySQL insert done for json. Using foreach? – Les88 Jun 08 '18 at 02:45

1 Answers1

1
$json = json_decode($output, true);
foreach($json as $val)
{
    $project_code = $val['project_code'];
    $dept = $val['dept'];
    $name = $val['name'];

    /*
    * Generate insert query as below
    * "INSERT INTO MyGuests (project_code, dept, name) VALUES ($project_code, $dept, $name);";
    */

}

Also, you can insert multiple record in single loop Please check below reference link https://www.w3schools.com/php/php_mysql_insert_multiple.asp

In reference link 3 different methods given,so, you can use any one whatever suitable for you

Jaydp
  • 1,029
  • 9
  • 17