How to Store decoded json data into variables and insert them in to mysql table

Question

Hi In my android application Iam retrieving the data from sqlite and now by using JSON i want to store that data into mysql table.Please help me to achieve this

I'm not getting how to store all the values in a table only single row is inserting each time I try to insert.

This is the code.

<?php

error_reporting(0);

include_once 'db_conn.php';  //Include the database connection strings


$received_json = $_POST["sparesJSON"];

if (get_magic_quotes_gpc())

{
    $received_json = stripslashes($received_json);
}

$received_json = json_decode($received_json);

//catch variable

$item_name = $received_json[0]->item_name;

$quantity = $received_json[0]->quantity;

$total_price = $received_json[0]->total_price;

$cycle_id = $received_json[0]->cycle_id;

$date = $received_json[0]->date;

for($i=0;i<count($received_json;i++)
{


            $insert_spares = "insert into spares_items (item_name, quantity, total_price,    cycle_id, date) values (\"$item_name\", \"$quantity\", \"$total_price\", \"$cycle_id\", \"$date\")";

            mysql_query($insert_spares);
}

//encode result array in json

echo json_encode($cycle_id);


//send this as an response to the Android
?>

Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). You will also want to [Prevent SQL Injection!](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Nov 21 '14 at 22:12

score 1 · Accepted Answer · answered Nov 21 '14 at 22:11

1

$received_json = json_decode($received_json);



for($i=0;$i<count($received_json);$i++)
{


    $item_name = $received_json[$i]->item_name;

    $quantity = $received_json[$i]->quantity;

    $total_price = $received_json[$i]->total_price;

    $cycle_id = $received_json[$i]->cycle_id;

    $date = $received_json[$i]->date;    
    $insert_spares = "insert into spares_items (item_name, quantity, total_price,    cycle_id, date) values (\"$item_name\", \"$quantity\", \"$total_price\", \"$cycle_id\", \"$date\")";

    mysql_query($insert_spares);
}

answered Nov 21 '14 at 22:11

gvk

466
2
6

can i know what should be the response also at the end? – Vandana Nov 21 '14 at 22:12
Gather all $cycle_id values into an array. Output them after the loop. – gvk Nov 21 '14 at 22:14
actually in my situation there is no need of gather or storing them.what i need is just compose json data from sqlite and store them is mysql thats it.Now can i skip the response...? – Vandana Nov 21 '14 at 22:17
can I also know how to eliminate duplicate values while inserting into table...? – Vandana Nov 21 '14 at 22:20
For the first one: yes, you can skip the response if you don't need it on the client-side. For the second one: depends on which values will be duplicated. You can maintain an array that tracks all the values seen so far and check against it before inserting a new row. – gvk Nov 21 '14 at 22:29
can you please help me with an example..?since I'm new to php Iam unable to get this – Vandana Nov 21 '14 at 22:33
Added another answer. Compare this against the first answer and you'll see how to do it :-) – gvk Nov 21 '14 at 22:37

score 0 · Answer 2 · answered Nov 21 '14 at 22:37

$received_json = json_decode($received_json);

$inserted_names = array();

for($i=0;$i<count($received_json);$i++)
{


    $item_name = $received_json[$i]->item_name;
    // if we have already inserted this name, don't insert again
    if (in_array($item_name, $inserted_names)) {
        continue;
    }

    $quantity = $received_json[$i]->quantity;

    $total_price = $received_json[$i]->total_price;

    $cycle_id = $received_json[$i]->cycle_id;

    $date = $received_json[$i]->date;    
    $insert_spares = "insert into spares_items (item_name, quantity, total_price,    cycle_id, date) values (\"$item_name\", \"$quantity\", \"$total_price\", \"$cycle_id\", \"$date\")";

    mysql_query($insert_spares);
    // save item name to list of names already inserted
    array_push($inserted_names, $item_name);
}

How to Store decoded json data into variables and insert them in to mysql table

2 Answers2