How to save different Json data to same database table of Mysql using Php

Question

I'm trying to save some json data that gets generated randomly . It can have 6 keys or just 3 keys as shown below.

Array sample:
sample 1:
{
  "report": {
    "a-key": "a-value",
    "b-key": "b-value",
    "c-key": "c-value",
    "d-key": "d-value",
    "e-key": "e-value",
    "f-key": "f-value", 
  }
}

Sample 2:
{
  "report": {
    "a-key": "a-value",
    "c-key": "c-value",
    "f-key": "f-value", 
  }
}

Database Table name : plogs
columns: id,logdate, logtime, a, b, c, d, e,
id is unique key that auto increments

Below is the Php code i tried to use. I was able to successfully generate a text file for each json input without any errors.however I'm not able to save the values to the database. Can you please let me know where im making a mistake. P.S Part of the code i got it off some tutorials

<?php 


// Send `204 No Content` status code.
http_response_code(204);

// Get the raw POST data.
$data        = file_get_contents('php://input');
$newfilename = date('Y-m-d-H-i-s') . ".txt";
file_put_contents($newfilename, $data);

// a new json file with one of the 2 sample arrays

$date1      = date('Y-m-d');
$time1      = date('H:i:s');
$b         = NULL;
$d         = NULL;
$f         = NULL;
$connect    = mysqli_connect("localhost", "dbuser", "dbpassword", "dbname") or die ("error"); //Connect PHP to MySQL Database
$query      = '';

$array      = json_decode($data, true); //Convert JSON String into PHP Array

function array_keys_exist(array $array, $keys)
{
    $count = 0;
    if (!is_array($keys)) {
        $keys = func_get_args();
        array_shift($keys);
    }
    foreach ($keys as $key) {
        if (isset($array[$key]) || array_key_exists($key, $array)) {
            $count++;
        }
    }

    return count($keys) === $count;
}



foreach ($array as $row) //Extract the Array Values by using Foreach Loop
    {
    if (array_keys_exist($array, 'b-key', 'd-key', 'f-key')) 
        {
        $query .= "INSERT INTO plogs(logdate, logtime, a, b, c, d, e, f) VALUES ('" . $date1 . "', '" . $time1 . "', '" . $row["a-key"] . "', '" . $row["b-key"] . "', '" . $row["c-key"] . "', '" . $row["d-key"] . "', '" . $row["e-key"] . "', '" . $row["f-key"] . "' ); "; 

        } else 
        {

        $query .= "INSERT INTO plogs(logdate, logtime, a, b, c, d, e, f) VALUES ('" . $date1 . "', '" . $time1 . "', '" . $row["a-key"] . "', '" . $s1 . "', '" . $row["c-key"] . "', '" . $s2 . "', '" . $row["e-key"] . "', '" . $s1 . "'); "; 
        }

    }


mysqli_multi_query($connect, $query); 
mysqli_close($connect);

puntolino · Accepted Answer · 2020-06-11 09:06:52Z

1

in the code of the foreach loop you are checking if the array keys are in the array variable, you should check in the $row variable as in:

if (array_keys_exist($row, 'b-key', 'd-key', 'f-key'))  {

SIDE NOTE: remember to sanitize strings if they come from users, otherwise your code will be unsecure.

EDIT: for real security, as Dharman pointed out in comments, the proper way to proceed is to use prepared statements with parameter binding.

cheers

edited Jun 11, 2020 at 9:06

answered Jun 10, 2020 at 16:15

puntolino

786 bronze badges

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

1 Comment

Dharman Over a year ago

Never sanitize strings. Use parameter binding.

Collectives™ on Stack Overflow

How to save different Json data to same database table of Mysql using Php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related