Kuntal Parbat Kuntal Parbat - 2 months ago 9
JSON Question

How to save special array data into Three MySQL table in PHP

I have JSON which need to insert into MySQL table.

What I already did is:
I have convert it to array using

$json_data = json_decode($geo_json, true);

and get an output of array but I do not know how to inset into second and third MySQL table.

My MySQL Table:

Table 1:

geo_id | user_id | geo_title | geo_description | geo_date |geo_status |geo_action |action_reason | geo_json | remote_ip | map_type |geo_snapshot

I can able to insert into above table easily but problem is in table two and Three listed below.

Table 2:

id | layer_id | map_id | layer_name | user_id | draw_type | latlng | radious

Table 3:

data_id | geo_key | geo_value | map_id | layer_id

Array I am getting:

Array
(
[type] => FeatureCollection
[features] => Array
(
[0] => Array
(
[type] => Feature
[properties] => Array
(
[action] => a
[poi_name] => a
[fac_type] => 17
[ph_number] => a
[hno] => a
[postcode] => a
[mail] => a
[str_nm] => a
[photo] => developer-page.png
[comment] => a
[url] => a
[sub_loc] => a
[employee] => a
)

[geometry] => Array
(
[type] => Point
[coordinates] => Array
(
[0] => 88.434448242188
[1] => 22.510971144638
)

)

)

[1] => Array
(
[type] => Feature
[properties] => Array
(
[action] => b
[poi_name] => b
[fac_type] => 18
[ph_number] => b
[hno] => b
[postcode] => b
[mail] => b
[str_nm] => b
[photo] => 1475131600_developer-page.png
[comment] => b
[url] => b
[sub_loc] => b
[employee] => b
)

[geometry] => Array
(
[type] => Point
[coordinates] => Array
(
[0] => 88.321151733398
[1] => 22.50906814933
)

)

)

)

)


Now problem is to insert above data into two separate tables:

Table 2: This is only require to insert draw_type | latlng from above php array.

Example: draw_ type: point and latlng : coordinates

Table 3:
This is require to insert geo_key | geo_value | map_id | layer_id from above PHP array.
Example: geo_key : properties [action,poi_name,fac_type,ph_number,hno,postcode,mail,str_nm, photo, comment, url, sub_loc, employee]

geo_value : [properties values ]

map_id :[this will be table 1 insert id]

layer_id : [this can be blank]

Please guide me and show me how to start.

HZS HZS
Answer

$json_data["features"][$array_index]["geometry"]["type"]

For table2:

foreach($json_data["features"] as $info){
    $type = $info["geometry"]["type"];
    $latlng_0 = $info["geometry"]["coordinates"][0];
    $latlng_1 = $info["geometry"]["coordinates"][1];

    // DO INSRET with $type, $latling_0, $latling_1
    $sql = "INSERT INTO Table2 (draw_type, latlng0, latlng1) VALUES ('".$type."','".$latlng_0."','".$latlng_1."')";
    ....
}

For table3:

Is 'map_id' a auto increment key in table1?

You'll need to know map_id first by select * where (conditions) after successful insert data to table1

And if 'layer_id' can accept blank (null) data, it'll be fine if you don't specific value in INSERT command. Just make sure your table have correct settings.