Saving JSON string to MySQL database

I have a JSON string with me

{"name":"jack","school":"colorado state","city":"NJ","id":null}

I need it to be saved in the Database. How could i do this ?

My PHP code (I have only establish the connection to MySQL, but i am unable to save the records)

   <?php
    // the MySQL Connection
    mysql_connect("localhost", "username", "pwd") or die(mysql_error());
    mysql_select_db("studentdatabase") or die(mysql_error());

    // Insert statement

    mysql_query("INSERT INTO student
    (name, school,city) VALUES(------------------------- ) ") // (How to write this)
    or die(mysql_error());  


    echo "Data Inserted or failed";

    ?>

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

We’ll use json_decode json_decode documentation

Also be sure to escape! here’s how I would do it below…

/* create a connection */
$mysqli = new mysqli("localhost", "root", null, "yourDatabase");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %sn", mysqli_connect_error());
    exit();
}

/* let's say we're grabbing this from an HTTP GET or HTTP POST variable called jsonGiven... */
$jsonString = $_REQUEST['jsonGiven'];
/* but for the sake of an example let's just set the string here */
$jsonString = '{"name":"jack","school":"colorado state","city":"NJ","id":null}
';

/* use json_decode to create an array from json */
$jsonArray = json_decode($jsonString, true);

/* create a prepared statement */
if ($stmt = $mysqli->prepare('INSERT INTO test131 (name, school, city, id) VALUES (?,?,?,?)')) {

    /* bind parameters for markers */
    $stmt->bind_param("ssss", $jsonArray['name'], $jsonArray['school'], $jsonArray['city'], $jsonArray['id']);

    /* execute query */
    $stmt->execute();

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();

Hope this helps!

Method 2

This is example for help you

<?php
 $json = '{"name":"jack","school":"colorado state","city":"NJ","id":null}';// You can get it from database,or Request parameter like $_GET,$_POST or $_REQUEST or something :p
 $json_array = json_decode($json);

 echo $json_array["name"];
 echo $json_array["school"];
 echo $json_array["city"];
 echo $json_array["id"];
?>

Hope this help !

Method 3

Decode into an array and pass it in your mysql_query, the code below is not using mysql_real_escape_string or any other means of security, which you should implement.

Assume $json is {“name”:”jack”,”school”:”colorado state”,”city”:”NJ”,”id”:null}

$json_array = json_decode($json);

You now have indexes in a php array, such as: $json_array[‘name’]

mysql_query("INSERT INTO student (name, school,city) VALUES('".$json_array['name']."', '".$json_array['school']."', '".$json_array['city']."') ") or die(mysql_error());

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating