add to mysql db data from json file

Question

Is something wrong with the code? It's not insert the data to database, i don't know what is wrong. Can someone help me please?

Here is the php code

<?php

include 'config.php';

$data = file_get_contents('programs.json');
$json = json_decode($data, TRUE);
if ($data != '' && count($json) > 0) {
    $sql = "SELECT pid FROM programs";
    $result = mysqli_query($conn, $sql);
    $transArr = array();
    if (mysqli_num_rows($result) > 0) {
        while ($row = mysqli_fetch_assoc($result)) {
            $transArr[] = $row["pid"];
        }
    }
    $bulk_query = '';
    $bulk_update_query = '';
    $pid = '';
    $pname = '';
    $start_date = '';
    $end_date = '';
    $logo = '';
    $short_description = '';
    $description = '';
    $ended_date = '';
    $url = '';
    $program_status = '';
    $summary = '';
    $categories = '';
    foreach ($json as $key => $jsonRow) {
        
       
        $pid = isset($jsonRow['id']) ? $jsonRow['id'] : '';
        $pname = isset($jsonRow['name']) ? $jsonRow['name'] : '';
        $start_date = isset($jsonRow['start_date']) ? $jsonRow['start_date'] : '';
        $end_date = isset($jsonRow['end_date']) ? $jsonRow['end_date'] : '';
        $logo = isset($jsonRow['logo']) ? $jsonRow['logo'] : '';
        $short_description = isset($jsonRow['short_description']) ? $jsonRow['short_description'] : '';
        $description = isset($jsonRow['description']) ? $jsonRow['description'] : '';
        $ended_date = isset($jsonRow['ended_date']) ? $jsonRow['ended_date'] : '';
        $url = isset($jsonRow['url']) ? $jsonRow['url'] : '';
        $program_status = isset($jsonRow['program_status']) ? $jsonRow['program_status'] : '';
        $summary = isset($jsonRow['summary']) ? floatval($jsonRow['summary'])  : '';
        $categories = isset($jsonRow['parent_category']['name']) ? $jsonRow['parent_category']['name'] : '';
        

        if (!in_array($pid, $transArr)) {
            $bulk_query.="('$pid','$pname','$start_date','$end_date','$logo','$short_description','$description',$ended_date,$url,'$program_status','$summary','$categories'),";
        } else {
            $sql = "UPDATE programs SET pid='$pid',name='$pname',start_date='$start_date',end_date='$end_date',"
                    . "logo='$logo',short_description='$short_description',description='$description',ended_date=$ended_date,url=$url,"
                    . "program_status='$program_status',summary='$summary',categories='$categories' WHERE pid=$pid";
            mysqli_query($conn, $sql);
        }
    }
    $bulk_query = rtrim($bulk_query, ",");
    $sql = "INSERT INTO programs (pid,pname,start_date,end_date,logo,short_description,description,ended_date,url,program_status,summary,categories) VALUES $bulk_query";
    $conn->query($sql);
    createFileBackup();
    echo ' Record Added';
}

?>

The json data im trying to insert to db

[
    {
        "id": 12356,
        "name": "test",
        "start_date": "2017-06-14T21:00:00+00:00",
        "end_date": null,
        "logo": "test.png",
        "short_description": "test short_description",
        "description": "test description",
        "ended_date": null,
        "url": "http://www.test.com/",
        "program_status": "Active",
        "commissions": {
            "details": [
                {
                    "category": {
                        "id": 1,
                        "name": "Sales",
                        "cookie_length": 30,
                        "type": "sale",
                        "payout_type": "percent",
                        "tiers": [
                            {
                                "tier": {
                                    "level": 1,
                                    "action": "12.00",
                                    "subaction": "12.00",
                                    "formatted": {
                                        "action": "12,00%",
                                        "subaction": "12,00%"
                                    }
                                }
                            }
                        ]
                    }
                }
            ],
            "summary": {
                "percent": "12,00%",
                "flat": "-"
            },
            "tiers": {
                "count_type": "clicks",
                "amount": 1,
                "levels": []
            }
        },
        "categories": [
            {
                "parent_category": {
                    "name": "test",
                    "child_categories": [
                        {
                            "child_category": {
                                "name": "test2"
                            }
                        }
                    ]
                }
            }
        ]
    },
    {
        "id": 123456,
        "name": "test",
        "start_date": "2017-06-14T21:00:00+00:00",
        "end_date": null,
        "logo": "test.png",
        "short_description": "test short_description",
        "description": "test description",
        "ended_date": null,
        "url": "http://www.test.com/",
        "program_status": "Active",
        "commissions": {
            "details": [
                {
                    "category": {
                        "id": 1,
                        "name": "Sales",
                        "cookie_length": 30,
                        "type": "sale",
                        "payout_type": "percent",
                        "tiers": [
                            {
                                "tier": {
                                    "level": 1,
                                    "action": "12.00",
                                    "subaction": "12.00",
                                    "formatted": {
                                        "action": "12,00%",
                                        "subaction": "12,00%"
                                    }
                                }
                            }
                        ]
                    }
                }
            ],
            "summary": {
                "percent": "12,00%",
                "flat": "-"
            },
            "tiers": {
                "count_type": "clicks",
                "amount": 1,
                "levels": []
            }
        },
        "categories": [
            {
                "parent_category": {
                    "name": "test",
                    "child_categories": [
                        {
                            "child_category": {
                                "name": "test2"
                            }
                        }
                    ]
                }
            }
        ]
    }
    ]

And the db structure is

Be more precise. Do you get any error mressages or something? Implement some errorhandling, that could help you a lot. — Twinfriends
– Twinfriends, Commented Jun 15, 2017 at 9:29
there no error's in my hosting error log, can you provide me please with errorhandling code to add? — user3899237
– user3899237, Commented Jun 15, 2017 at 9:32
im using the same code to a similar json file and it's working great. but when i tryed to do the same with this json data i had no luck — user3899237
– user3899237, Commented Jun 15, 2017 at 9:35

user3899237 · Accepted Answer · 2017-06-15 12:35:22Z

1

i fix the issue with this $new_name = str_replace("'", "''", "$pname"); that i found online and now i can insert to db vars with apostrophes. thank you for ur help

answered Jun 15, 2017 at 12:35

user3899237

551 silver badge8 bronze badges

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

Collectives™ on Stack Overflow

add to mysql db data from json file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related