steven smith steven smith - 5 months ago 33
MySQL Question

Cannot use a scalar value as an array while using PDO

I am trying to input data from a JSON API into a MySQL DB but I have an error.

This is the error.


Warning: Cannot use a scalar value as an array in C:\xampp\htdocs\simplehtmldom\example\scraping\example_scraping_imdb.php on line 8

Warning: Cannot use a scalar value as an array in C:\xampp\htdocs\simplehtmldom\example\scraping\example_scraping_imdb.php on line 9

Warning: Cannot use a scalar value as an array in C:\xampp\htdocs\simplehtmldom\example\scraping\example_scraping_imdb.php on line 10

Warning: Cannot use a scalar value as an array in C:\xampp\htdocs\simplehtmldom\example\scraping\example_scraping_imdb.php on line 11

Warning: Cannot use a scalar value as an array in C:\xampp\htdocs\simplehtmldom\example\scraping\example_scraping_imdb.php on line 12

Warning: Cannot use a scalar value as an array in C:\xampp\htdocs\simplehtmldom\example\scraping\example_scraping_imdb.php on line 13

Warning: Cannot use a scalar value as an array in C:\xampp\htdocs\simplehtmldom\example\scraping\example_scraping_imdb.php on line 14

Warning: Cannot use a scalar value as an array in C:\xampp\htdocs\simplehtmldom\example\scraping\example_scraping_imdb.php on line 15


Al those lines are the bind param statements in the PHP code.

Here is the code that I use (The JSON objects are nested):

<?php

$db = new PDO('mysql:host=localhost;dbname=wtd','root','');
$jsondata = file_get_contents('https://hugo.events/genre/pop/next/200/100');
$data = json_decode($jsondata, true);
$stmt = $db->prepare("insert into events values(?,?,?,?,?,?,?,?)");
foreach ($data as $row) {
$stmt->bindParam(1, $row['hits']['hits']['_id']);
$stmt->bindParam(2, $row['hits']['hits']['fields']['name']);
$stmt->bindParam(3, $row['hits']['hits']['fields']['start']);
$stmt->bindParam(4, $row['hits']['hits']['fields']['venue.location']);
$stmt->bindParam(5, $row['hits']['hits']['fields']['description']);
$stmt->bindParam(6, $row['hits']['hits']['fields']['header']);
$stmt->bindParam(7, $row['hits']['hits']['fields']['logo']);
$stmt->bindParam(8, $row['hits']['hits']['fields']['genres']);
$stmt->execute();
}


EDIT: Sample of JSON data:

https://ghostbin.com/paste/437q7

Hope someone has a sollution. Thanks in advance.

Answer

According to documentation:

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

You need to create list of independent variables to bind them into statement:

foreach ($data as $row) {
    $v1 = $row['hits']['hits']['_id'];
    $v2 = $row['hits']['hits']['fields']['name'];
    $v3 = $row['hits']['hits']['fields']['start'];
    ...
    $stmt->bindParam(1, $v1);
    $stmt->bindParam(2, $v2);
    $stmt->bindParam(3, $v3);
    ...
    $stmt->execute();
}

Or you can try to use bindValue instead:

foreach ($data as $row) {
    $stmt->bindValue(1, $row['hits']['hits']['_id']);
    $stmt->bindValue(2, $row['hits']['hits']['fields']['name']);
    $stmt->bindValue(3, $row['hits']['hits']['fields']['start']);
    ... 
    $stmt->execute();
}

UPDATE After reviewing json you provided I have not that many words to say. I have no idea what is your expectations are, but you definetely can not do foreach ($data as $row) { against that data.

You can play a bit with your data here

So to get that _id you should call:

$data[1]['hits']['hits'][1]['_id'];

And to get fields you need to:

$data[1]['hits']['hits'][0]['fields'];

Probably you use data different from what you did post here.

UPDATE 2 SO is not to develop for you. Try to do your homework first next time please. But here is what you are looking for I guess:

foreach ($data[1]['hits']['hits'] as $row) {
    $v1 = $row['_id'];
    $v2 = $row['fields']['name'][0];
    $v3 = $row['fields']['start'][0];
    ....
}