I am having trouble trying to connect to a MySQL DB to insert certain JSON values from a .json file.
I am still fairly new to working with data, connecting to a DB via PHP and such.
The db is in the same cpanel/host/server as where this file is found. Please let me know if I need to change, add or improve anything.
What I am trying to do, is read the file.json and then insert those entries into a remote DB that is on my server.
What I am looking for is how to insert these values into insert the data into a MYSQL, not print it on a page.
This question doesn't answer my question: How to extract and access data from JSON with PHP?
<!DOCTYPE html>
<html>
<body>
<h1>Insert Data into DB</h1>
<?php
$username = "user";
$password = "pass";
// Create connection
$con = new PDO('mysql:host=host;dbname=DBNAME', $username, $password);
//read the json file contents
$jsondata = file_get_contents('http://path.to.file.com/file.json');
//convert json object to php associative array
$data = json_decode($jsondata, true);
foreach ($data as $jsons)
{
$id = null;
$fname = null;
$lname = null;
$email = null;
$phone = null;
$date = null;
$state = null;
foreach($jsons as $key => $value)
{
if($key == 'id') {
$id = $value;
}
if($key == 'date_created') {
$date = $value;
}
if($key == '1') {
$email = $value;
}
if($key == '3.3') {
$fname = $value;
}
if($key == '3.6') {
$lname = $value;
}
if($key == '5') {
$phone = $value;
}
if($key == '6') {
$state = $value;
}
}
//insert into mysql table
$sql = "INSERT INTO contact(id, date, first, last, phone, email, state)
VALUES('$id', '$date', '$fname', '$lname', '$phone', '$email', '$state')";
if(!mysql_query($sql,$con))
{
die('Error : ' . mysql_error());
}
}
?>
</body>
</html>
here is an example of a JSON entry
{
"total_count": 209,
"entries": [
{
"id": "544537",
"form_id": "2",
"post_id": null,
"date_created": "2022-10-21 17:26:18",
"date_updated": "2022-10-21 17:26:18",
"is_starred": "0",
"is_read": "0",
"ip": "68.126.222.136",
"source_url": "/contact\",
"user_agent": "Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/106.0.0.0 Safari\/537.36",
"currency": "USD",
"payment_status": null,
"payment_date": null,
"payment_amount": null,
"payment_method": null,
"transaction_id": null,
"is_fulfilled": null,
"created_by": null,
"transaction_type": null,
"status": "active",
"1": "email@email.com",
"2": "Contractor\/GC",
"3.3": "first",
"3.6": "last",
"4": "Company",
"5": "(111)132-4567",
"6": "California",
"7": "I am seeking for a bid to furnish and install",
"is_approved": "3",
"3.2": "",
"3.4": "",
"3.8": "",
"8": "",
"workflow_current_status_timestamp": false,
"gpnf_entry_parent": false,
"gpnf_entry_parent_form": false,
"gpnf_entry_nested_form_field": false
},
No comments:
Post a Comment