Reputation: 33

Insert json to mysql

I am looking for a way to insert JSON to SQLite. I have a php script that looks like this.

<?php 
require 'connection.php';

$var_fname      = "Mildred";
$var_password       = "ewan";
$var_email  = "saf";
$var_username = "enanpogi";
$var_lastname = "Mildred";
$var_aviaryName = "Mildred";
$var_location = "myLocation";

 $insert_stmt = $db->prepare(""
                . "INSERT INTO users"
                . "( fname, lname, email, password, username, aviary_name, location ) "
                . "VALUES( :name, :lastname, :email, :password, :username, :aviaryName, :location)" );
                // prepare and bind
                $insert_stmt->execute(array(
                    ':name'         => $var_fname,
                    ':lastname'     => $var_lastname,
                    ':password'     => $var_password,
                    ':email'        => $var_email,
                    ':username'     => $var_username,
                    ':aviaryName'   => $var_aviaryName,
                    ':location'     => $var_location
                ));
                $lastId = $db->lastInsertId();

                if ($insert_stmt){
                    echo "inserted</br>";
                    echo $lastId;
                    //get all information of newly inserted and make json encode to prepare for insertion in sqlite_array_query

                    $stmt = $db->prepare('SELECT * '
                        . 'FROM users '
                        . 'WHERE id = :lastInsertedID ');
                    $stmt->bindParam(':lastInsertedID', $lastId);
                    $stmt->execute();

                    $results = $stmt->fetch(PDO::FETCH_ASSOC);

                    if($results > 0 ){
                        //echo sizeof($results);
                        echo "</br>";
                        echo json_encode(array("user_data"=>$results) );
                    }
                }
?>

when I run that script, I was able to get below result

{"user_data":{"id":"28","fname":"Mildred","lname":"Mildred","email":"saf","username":"enanpogi","aviary_name":"Mildred","password":"ewan","location":"myLocation","phonenumber":null,"active":null}}

I am not sure if it is JSON Object or JSON array..

Now, I need help to insert that JSON to SQLite in my Android JAVA app.

Upvotes: 0