Saving JSON string to MySQL database

Question

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";

    ?>

sevenadrian · Accepted Answer · 2012-07-04 00:59:21Z

17

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: %s\n", 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!

answered Jul 4, 2012 at 0:59

sevenadrian

4162 silver badges4 bronze badges

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

2 Comments

Sharon Watinsan Over a year ago

i don't understand what jsonGiven is. My JSON string is this {"name":"jack","school":"colorado state","city":"NJ","id":null}. And i am unable to read it from my PHP code.

sevenadrian Over a year ago

jsonGiven would be the example parameter name where the value is your json string. An example would be example.com/thisScript.php?jsonGiven={"name":"jack",etc..}

Objectoop Oop · Accepted Answer · 2012-07-04 07:09:23Z

1

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 !

answered Jul 4, 2012 at 7:09

Objectoop Oop

619 bronze badges

Comments

André Catita · Accepted Answer · 2012-07-04 00:07:26Z

0

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());

answered Jul 4, 2012 at 0:07

André Catita

1,32316 silver badges19 bronze badges

5 Comments

Sharon Watinsan Over a year ago

In my Firebug console, I am posting {"name":"jack","school":"colorado state","city":"NJ","id":null} so how do i fetch this value and save it to $json as shown in your code ?

Sharon Watinsan Over a year ago

what do you mean by escape. sorry i am new to PHP

Sharon Watinsan Over a year ago

How do i receive the JSON string to $json ?

André Catita Over a year ago

I thought you already had it, if you're saying it shows on your Firebug console, it shows up from where? Who creates it? Please explain, so I can help you turn it into the $json.

Ken Gordon Over a year ago

What if your json information comes in from a form? How would you encode it?

Collectives™ on Stack Overflow

Saving JSON string to MySQL database

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related