JSON to MYSQL using PDO

I have the following code and I am going to INSERT JSON to MYSQL ... but its not working.. I am getting  Error!: SQLSTATE[HY093]: Invalid parameter number<br>

<?php



error_reporting(E_ALL);
echo '<pre>';

$sets = array();
$useraccounts = array("cricshare", "gp3123123123", "spider3323");

foreach ($useraccounts as $useracct)
{
    $page = 1;
    do
    {
        $url = "https://api.dailymotion.com/user/$useracct/videos?page=$page&limit=100";
        echo PHP_EOL . "READING $url";
        $jso = file_get_contents($url);
        if (!$jso) trigger_error("READ FAILURE: $url", E_USER_ERROR);
        $data = json_decode($jso);
        if (!$data) trigger_error("JSON FAILURE: $url", E_USER_ERROR);
        if (!$data->list)
        {
            var_dump($data);
            trigger_error("DATA FAILURE: $url", E_USER_ERROR);
        }
        $sets = array_merge($sets, $data->list);
        $hasMore = $data->has_more;
        if (!$hasMore)
            break;
        $page++;
    } while ($hasMore);
}

$JSON= json_encode($sets, JSON_PRETTY_PRINT);
echo '<pre>';
echo $new;

$user = "xx";
$pass = "xxx";

try
{
    $dbh = new PDO('mysql:host=localhost;dbname=xxx', $user, $pass);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);






$stmt = $dbh->prepare("INSERT INTO DMVideosStage
(id, title, channel, owner)
    values(:id,:title, :channel, :owner)
    on duplicate key update title= :title, channel= :channel, owner= :owner");


//$stmt->bindParam(':id', $id);
    $stmt->bindParam(':id', $JSON->id, PDO::PARAM_STR);
$stmt->bindParam(':title', $JSON->title, PDO::PARAM_STR);
$stmt->bindParam(':channel', $JSON->channel, PDO::PARAM_STR);
$stmt->bindParam(':owner', $JSON->owner, PDO::PARAM_STR);

$stmt->execute() ;

$dbh = null;
}

catch(PDOException $e)
{
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

Open in new window

LVL 5
CalmSoulAsked:
Who is Participating?
 
Chris StanyonCommented:
Here's the detail of what you need to do:

$stmt->bindParam(':id', $id, PDO::PARAM_STR);
$stmt->bindParam(':title', $title, PDO::PARAM_STR);
$stmt->bindParam(':channel', $channel, PDO::PARAM_STR);
$stmt->bindParam(':owner', $owner, PDO::PARAM_STR);

foreach ($sets as $record):
	$id = $record->id;
	$title = $record->title;
	$channel = $record->channel;
	$owner = $record->owner;
	$stmt->execute();
endforeach;

Open in new window

0
 
Phil PhillipsDirector of DevOps & Quality AssuranceCommented:
From: http://www.php.net/manual/en/pdo.prepare.php

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

So, you need to either turn emulation mode on (line 46), or use unique parameter names.
0
 
Ray PaseurCommented:
A typical design would have the AUTO_INCREMENT PRIMARY KEY named "id" and you would not use that in the INSERT query - you would let MySQL take care of that for you.  Without seeing the table definitions, we can't be sure whether this is an issue, but it's a common problem.

You may want to consider MySQL's very useful REPLACE INTO query, instead of INSERT.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
CalmSoulAuthor Commented:
@Ray: I tried that now I am getting error:

Error!: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'id' cannot be null<br>
0
 
CalmSoulAuthor Commented:
I think problem is $JSON data is not getting to my insert query !!
0
 
jimyXCommented:
Did you test to print the JSON encoded data before entering the insert?
It's always useful, when you work with encoding, to set a condition(s) to validate the result data to where you should continue or prompt for invalid data.
0
 
CalmSoulAuthor Commented:
Here is the decoded sample:

READING https://api.dailymotion.com/user/cricshare/videos?page=1&limit=100
READING https://api.dailymotion.com/user/cricshare/videos?page=2&limit=100
READING https://api.dailymotion.com/user/cricshare/videos?page=3&limit=100array(296) {
  [0]=>
  array(4) {
    ["id"]=>
    string(7) "x2k3s57"
    ["title"]=>
    string(39) "11060227_10153090348971041_2026016609_n"
    ["channel"]=>
    string(6) "people"
    ["owner"]=>
    string(6) "x27dc6"
  }
  [1]=>
  array(4) {
    ["id"]=>
    string(7) "x2k1y42"
    ["title"]=>
    string(39) "11043308_10206140795625987_1202790175_n"
    ["channel"]=>
    string(3) "fun"
    ["owner"]=>
    string(6) "x27dc6"
  }

Open in new window


but when I do echo $decoded['title']

<br>
<b>Notice</b>:  Undefined index: title in <b>/xxxxx.php</b> on line <b>53</b><br>
0
 
jimyXCommented:
An what did you find in that line?
0
 
Chris StanyonCommented:
The SQLSTATE[23000] error you're getting is because you're not inserting an ID. Ray's comment about not using the ID in your query is not relevant for you. That only works if your ID is an auto-increment field - yours isn't, so you can't drop it from your query.

Not sure what the relevance is on the code you've just posted. There's a fair chance that Undefined Index error is caused because $decoded is an array or arrays, so you wan't have a 'title' index unless you loop through the array.

In your original code, you seem to json_encode the $sets array into a JSON variable and then try to insert that into your table. That's not going to work -  you need to loop through the $sets array and insert the data from there:

foreach ($sets as $record):
   // your data is now in $record, so that's what you need to insert
endforeach;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.