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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phil PhillipsDevOps ArchitectCommented:
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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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 StanyonWebDevCommented:
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
Chris StanyonWebDevCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.