Save json data in MySQL using php

Question

I want to save my following data into MySQL Database.

following code is showing me two warnings

1- Illegal string offset.

2- Only variables can be passed by reference in.

Please help.

$db = new PDO('mysql:host=localhost;dbname=whois;charset=utf8mb4', 'root', '');
$items = array(
        'Domain Name:' => 'domain.name',
        'Domain ID:' => 'domain.handle',
        'Sponsoring Registrar:' => 'domain.sponsor',
        'Registrar ID:' => 'domain.sponsor',
        'Domain Status:' => 'domain.status.',
        'Status:' => 'domain.status.',
        'Name Server:' => 'domain.nserver.',
        'Nameservers:' => 'domain.nserver.',
        'Maintainer:' => 'domain.referer',
);

$stmt = $db->prepare("INSERT INTO temdata VALUES(?,?,?,?,?,?,?,?,?)");

foreach ($items as $row) {

    $stmt->bindParam(1, $row['domainname']);
    $stmt->bindParam(2, $row['domainid']);
    $stmt->bindParam(3, $row['registrar']);
    $stmt->bindParam(4, $row['registrarid']);
    $stmt->bindParam(5, $row['domainstatus']);
    $stmt->bindParam(6, $row['status']);
    $stmt->bindParam(7, $row['server']);
    $stmt->bindParam(8, $row['nameservers']);
    $stmt->bindParam(9, $row['maintainer']);
    $stmt->execute();
}

Let's start with this one. $row is not an array, it's a value. So $row['domainname'], $row['domainid'], ... are all wrong. — Rajdeep Paul
– Rajdeep Paul, Commented Feb 10, 2017 at 17:55
If you used named parameter, after bothering to load an array of data, this might well be a lot easier — RiggsFolly
– RiggsFolly, Commented Feb 10, 2017 at 17:55
now its showing me Fatal error: Call to undefined method PDOStatement::bind_param() — Siddhesh
– Siddhesh, Commented Feb 10, 2017 at 18:01
Lets start at the beginning. Are you using PDO or MYSQLI_ datbase extension — RiggsFolly
– RiggsFolly, Commented Feb 10, 2017 at 18:01
$db = new PDO('mysql:host=localhost;dbname=whois;charset=utf8mb4', 'root', ''); — Siddhesh
– Siddhesh, Commented Feb 10, 2017 at 18:02

Nisse Engström · Accepted Answer · 2017-02-10 21:51:11Z

1

I think this will work, but I personally would want something more robust before I released it.

$items = array(
        'Domain Name:' => 'domain.name',
        'Domain ID:' => 'domain.handle',
        'Sponsoring Registrar:' => 'domain.sponsor',
        'Registrar ID:' => 'domain.sponsor',
        'Domain Status:' => 'domain.status.',
        'Status:' => 'domain.status.',
        'Name Server:' => 'domain.nserver.',
        'Nameservers:' => 'domain.nserver.',
        'Maintainer:' => 'domain.referer',
);

$stmt = $db->prepare("INSERT INTO temdata VALUES(?,?,?,?,?,?,?,?,?)");
$col=1;
foreach ($items as $item) {
    $stmt->bindParam($col, $item);
    $col++;
}
$stmt->execute();

edited Feb 10, 2017 at 21:51

Nisse Engström

4,76323 gold badges29 silver badges44 bronze badges

answered Feb 10, 2017 at 18:23

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

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

2 Comments

Siddhesh Over a year ago

That worked. Thank you soo much. you are a lifesaver :)

Rajdeep Paul Over a year ago

@Siddhesh Please accept the answer to close the question, otherwise it'd be floating around as open question. How to accept answer on Stack Overflow?

Yaman Jain · Accepted Answer · 2017-02-10 18:30:34Z

0

First of all remove trailing dot (.) from array values

$items = array(
    'Domain Name:' => 'domain.name',
    'Domain ID:' => 'domain.handle',
    'Sponsoring Registrar:' => 'domain.sponsor',
    'Registrar ID:' => 'domain.sponsor',
    'Domain Status:' => 'domain.status',
    'Status:' => 'domain.status',
    'Name Server:' => 'domain.nserver',
    'Nameservers:' => 'domain.nserver',
    'Maintainer:' => 'domain.referer',
);
$stmt = $db->prepare("INSERT INTO temdata VALUES(?,?,?,?,?,?,?,?,?)");

$i = 1;
foreach ($items as $row) {
    $stmt->bindParam($i, $row);
    $i++;
}

$stmt->execute();
`

answered Feb 10, 2017 at 18:30

Yaman Jain

1,24511 silver badges17 bronze badges

Collectives™ on Stack Overflow

Save json data in MySQL using php

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related