PDO double entries

ludo1960 · January 18, 2019

Hi folks,

Can anybody spot my rookie mistake in a PDO insert query? $myarray prints out as expected, but the database has results doubled up i.e. Page_id 1 to 10 then again Page_id 1 to 10

<?php

include 'connection.php' ;

$startnum = 1;

$endnum = 10;

for($i = $startnum; $i <= $endnum; ++$i) {

$url = "http://api.somesite.com&page_number=$i";

$response = json_decode(file_get_contents($url), true)

$myarray = array(

'Page_id' => $i,

'County' => $response['county'],

'Country' => $response['country']

);

echo '<pre>'; print_r($myarray); echo '</pre>';

$mykeys = implode (", ", array_keys($myarray)) ;

$myvals = implode (", ",array_fill(0, count($myarray), '?'));

$res = $db->prepare("INSERT INTO TestDB ($mykeys) VALUES ($myvals)") ;

$res->execute(array_values($myarray));

} //endfor

Barand · January 18, 2019

A prepared query should contain placeholders for values, not the values.

Line 12 is missing a ";" at the end;

The word "indentation" seems to missing from your vocabulary.

Preparing or running queries inside loops should always be avoided.

I would use a single mutiple record insert

$startnum = 1;
$endnum = 10;

$placeholders = [];
$values = [];

for($i = $startnum; $i <= $endnum; ++$i) {
 
    $url = "http://api.somesite.com&page_number=$i";
     
    $response = json_decode(file_get_contents($url), true);
    
    $mykeys = 'page_id, ' . implode(', ', array_keys($response));
    
    $placeholders[] = "(?,?,?)";
    array_push($values, $i, $response['county'], $response['country']);
     
} //endfor

     
$res = $db->prepare("INSERT INTO TestDB ($mykeys) VALUES " . join(',', $placeholders)) ;
 
$res->execute($values);

Edited January 18, 2019 by Barand

ludo1960 · January 18, 2019

OK, tried your code and the testDB still has extra records in it:

<?php

include 'connection.php' ;

$startnum = 10;
$endnum = 14;

$placeholders = [];
$values = [];

for($i = $startnum; $i <= $endnum; ++$i) {

$url = "http://api.somesite.com&page_number=$i";

$resp = json_decode(file_get_contents($url), true);

$myarray = array(

'Page_id' => $i,
'County' => $resp['county'],
'Country' => $resp['country']
);

$mykeys = implode(', ', array_keys($myarray)); //prints out correctly

//$mykeys = 'page_id, ' . implode(', ', array_keys($response)); //prints out page_id twice

$placeholders[] = "(?,?,?)";

array_push($values, $i, $resp['county'], $resp['country']);

} //endfor

$res = $db->prepare("INSERT INTO testDB ($mykeys) VALUES " . join(', ', $placeholders)) ;

$res->execute($values);

//echo pdo2html($db, "SELECT * FROM Zoopladata"); does not work

Barand · January 18, 2019

1 hour ago, ludo1960 said:

OK, tried your code and the testDB still has extra records in it

I cannot understand why there are more than 5 records being written (10 to 14)

1 hour ago, ludo1960 said:

//$mykeys = 'page_id, ' . implode(', ', array_keys($response)); //prints out page_id twice

If you bothered to look what it is doing you would see why that does and mine didn't.

1 hour ago, ludo1960 said:

//echo pdo2html($db, "SELECT * FROM Zoopladata"); does not work

A home-grown function used for testing. I originally forgot to remove it.

ludo1960 · January 19, 2019

Actually I did bother to read your code, when you are at the learning stage it is a lot to take in, are you saying //$mykeys = 'page_id, ' . implode(', ', array_keys($response)); //prints out page_id twice is correct?

Barand · January 19, 2019

Yes. The $response array only contains county and country keys, so I added the page_id manually.

The $myarray you created contains all three keys, which is why adding the page_id manually gives it twice.

ludo1960 · January 19, 2019

What should the $res array look like i.e.

$res = $db->prepare("INSERT INTO testDB ($mykeys) VALUES " . join(', ', $placeholders)) ;

When I print_ R I get :

Page_id, County, CountryArray ( [0] => 10 [1] => Oxfordshire [2] => England [3] => 11 [4] => Oxfordshire [5] => England [6] => 12 [7] => Oxfordshire [8] => England [9] => 13 [10] => Oxfordshire [11] => England )

That don't look right to me?

Barand · January 19, 2019

$mydata =[10=>['county' => 'Cheshire', 'country' => 'England'],
              ['county' => 'Lancashire', 'country' => 'England'],
              ['county' => 'Cumbria', 'country' => 'England'],
              ['county' => 'Derbyshire', 'country' => 'England'],
              ['county' => 'Yorkshire', 'country' => 'England'],
              ['county' => 'Lincolnshire', 'country' => 'England'],
              ['county' => 'Surrey', 'country' => 'England'],
              ['county' => 'Hampshire', 'country' => 'England'],
              ['county' => 'Pembrokeshiire', 'country' => 'Wales'],
              ['county' => 'Devon', 'country' => 'England']
          ];
$db->exec("DROP TABLE IF EXISTS testdb");
$db->exec("CREATE TABLE testdb (
            page_id int,
            county varchar(50),
            country varchar(50),
            primary key (page_id)
            )");
            
$startnum = 10;
$endnum = 14;

$placeholders = [];
$values = [];

for($i = $startnum; $i <= $endnum; ++$i) {
    /*
    $url = "http://api.somesite.com&page_number=$i"; 
    $response = json_decode(file_get_contents($url), true);
    */
    
    $response = $mydata[$i];                                       // no access to your data so substituting from array $mydata
    
    $mykeys = 'page_id, ' . implode(', ', array_keys($response));
    
    $placeholders[] = "(?,?,?)";
    array_push($values, $i, ...(array_values($response)));
     
} //endfor

     
$res = $db->prepare("INSERT INTO TestDB ($mykeys) VALUES " . join(', ', $placeholders)) ;
 
$res->execute($values);


echo $res->queryString;
echo '<pre>$values = ', print_r($values, 1), '</pre>';

The query looks this, with sets of 3 placeholders for each of 5 records

INSERT INTO TestDB (page_id, county, country) VALUES (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?)

The $values array has 15 values (1 for each placeholder in the query)

$values = Array
(
    [0] => 10
    [1] => Cheshire
    [2] => England
    
    [3] => 11
    [4] => Lancashire
    [5] => England
    
    [6] => 12
    [7] => Cumbria
    [8] => England
    
    [9] => 13
    [10] => Derbyshire
    [11] => England
    
    [12] => 14
    [13] => Yorkshire
    [14] => England
)

ludo1960 · January 20, 2019

Aha I think I see where my problem is, you have the $myarray declared outside the for loop whereas I have $myarray inside the for loop to hold the results of the $resp array: Does the "don't prepare or execute queries in a loop" rule extend to arrays also?

<?php

include 'connection.php' ;

$startnum = 10;

$endnum = 11;

$placeholders = [];

$values = [];

$myarray = [];

for($i = $startnum; $i <= $endnum; ++$i) {

$url = "http://api.somesite.com&page_number=$i";

$resp = json_decode(file_get_contents($url), true);

$myarray = array(

' Page_id' => $i,

' County' => $resp['county'],

' Country' => $resp['country']

);

$mykeys = implode(', ', array_keys($myarray));

$placeholders[] = "(?,?,?)";

array_push($values, $i, $myarray['County'], $myarray['Country']);

} //endfor

$res = $db->prepare("INSERT INTO testDB ($mykeys) VALUES " . join(', ', $placeholders)) ;

echo $res->queryString;

Barand · January 20, 2019

What are you talking about? I don't have a $myarray or any other superfluous intermediate array to store the values from the $respnse, I use the keys and values directly from the $response array

ludo1960 · January 20, 2019

oops, I meant your $mydata array is outwith the loop, whereas $myarray is inside the loop

$mydata =[10=>['county' => 'Cheshire', 'country' => 'England'],
              ['county' => 'Lancashire', 'country' => 'England'],
              ['county' => 'Cumbria', 'country' => 'England'],
              ['county' => 'Derbyshire', 'country' => 'England'],
              ['county' => 'Yorkshire', 'country' => 'England'],
              ['county' => 'Lincolnshire', 'country' => 'England'],
              ['county' => 'Surrey', 'country' => 'England'],
              ['county' => 'Hampshire', 'country' => 'England'],
              ['county' => 'Pembrokeshiire', 'country' => 'Wales'],
              ['county' => 'Devon', 'country' => 'England']

Barand · January 20, 2019

The comment in my code

16 hours ago, Barand said:

// no access to your data so substituting from array $mydata

explained what the $mydata array was for. It merely provides the data to the $response and is nothing to do with its processing

ludo1960 · January 20, 2019

That's what I don't understand, the $response array has the ['county'] and ['country'] values in it, but I can't access them from within a loop. this a chicken and egg situation. How am I supposed to access the values from the $response array and make them available within the loop?

Barand · January 20, 2019

18 hours ago, Barand said:

array_push($values, $i, ...(array_values($response)));

That is what this line does - puts the values for the page_id ($i) and those from the $response array into the $values array.

[Edit]

Your version " array_push($values, $i, $myarray['County'], $myarray['Country']); " does exactly the same thing.

Edited January 20, 2019 by Barand

ludo1960 · January 20, 2019

I only want certain values from the $resp array, and change the key names to match the database column names e.g. $resp['county'] = County or $['post code'] to become Post_Code as mysql does not like 'post code' as a column name: Just a few lines before the array_push... you have $response = $mydata[$i] What does that do?

I tried to select a few array values from my array_push but it does not work:

array_push($values, $i, $resp['county'], $resp['country']);

Barand · January 20, 2019

Per haps it is time for you to tell me

what your table structure is
what your response data looks like
what you are trying to do, exactly

Then perhaps I will be better able to help (though no guarantees).

ludo1960 · January 21, 2019

Okay, here goes...

connection.php (used your example connection.php) and working!

function pdoConnect()

{

$dsn = "mysql:dbname=".DBNAME."; host=".HOST."; charset=utf8";

$db = new pdo($dsn, USERNAME, PASSWORD,

[

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,

PDO::ATTR_EMULATE_PREPARES => false,

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

]);

return $db;

}

$db = pdoConnect();

$db->query("DROP TABLE IF EXISTS test2DB");

$db->query("CREATE TABLE test2DB (

ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

Page_id INT,

County VARCHAR(50) NOT NULL,

Country VARCHAR(50) NOT NULL,

Post_Town VARCHAR(50) NOT NULL

)");

index.php connects to API and sends a single Page_id to mysql database, includes an array flatten function because the API returns a dogs dinner of a multi dimensional array.

<?php

function FlattenMultiArray($array,$bKeepKeys=true,$key_prefix='')

{

$array_flattened=Array();

foreach($array as $key=>$value){

if(Is_Array($value)){

$array_flattened=Array_Merge(

$array_flattened,

FlattenMultiArray($value,$bKeepKeys,$key)

);

}

else{

if($bKeepKeys){

$array_flattened["{$key_prefix}{$key}"]=$value;

}

else{

$array_flattened[]=$value;

}

return $array_flattened;

}

include 'connection.php';

$startnum = 1;

$endnum = 10;

$placeholders = [];

$values = [];

$i = 12;

$url = "http://apii.somesite.com&page_number=$i";

$response = json_decode(file_get_contents($url), true);

$resp = FlattenMultiArray($response, true, NULL);

//echo "<pre>"; print_r($resp); echo "</pre>";

$myarray = array(

'Page_id' => $i,

'County' => $resp['county'],

'Country' => $resp['country'],

'Post_Town' => $resp['0post_town']

);

//echo "<pre>"; print_r($myarray); echo "</pre>";

$mykeys = implode(', ' , array_keys($myarray));

$myvalues = array_values($myarray);

$placeholders[] = "(?,?,?,?)";

$res = $db->prepare("INSERT INTO test2DB ($mykeys) VALUES " . join(', ', $placeholders));

$res->execute($myvalues);

echo $res->queryString;

What is required is for a range of pages to be processed ie start page 10 and process to page 14. As soon as I introduce a for loop, my cunning plan goes to pot! Oh and when i paste code into the editor here all my nice coding formatting goes to pot too!

Edited January 21, 2019 by ludo1960

Barand · January 21, 2019

What is your code that tries to do that with a for loop (and "goes to pot")? Where are you putting th loop?

I have shown you code that uses a loop so I cant understand why you are having problems.

As for the code pasting, use the <> button in the toolbar then paste.

ludo1960 · January 21, 2019

Ok heres the for loop,

for($i = $startnum; $i <= $endnum; ++$i) {

    $url = "http://api.somesite.com&page_number=$i";

    $response = json_decode(file_get_contents($url), true);

    $resp = FlattenMultiArray($response, true, NULL);

    $myarray = array(

        'Page_id' => $i,
        'County' => $resp['county'],
        'Country' => $resp['country'],
        'Post_Town' => $resp['0post_town']

        );

    $mykeys = implode(', ' , array_keys($myarray));

    $placeholders[] = "(?,?,?,?)";

    array_push($values, array_values($myarray));

    } //endfor

$res = $db->prepare("INSERT INTO test2DB ($mykeys) VALUES " . join(', ', $placeholders));

$res->execute($values);

echo $res->queryString;

When I try this i get a http error 500 and nothing is posted to the database

Barand · January 21, 2019

Have you tried to determine at which point the error is occurring? Does this bit work OK?

for($i = $startnum; $i <= $endnum; ++$i) {

    $url = "http://api.somesite.com&page_number=$i";

    $response = json_decode(file_get_contents($url), true);

    $resp = FlattenMultiArray($response, true, NULL);
}// end for

ludo1960 · January 21, 2019

$startnum = 1;
$endnum = 5;

$placeholders = [];
$values = [];


for($i = $startnum; $i <= $endnum; ++$i) {
    $url = "http://api.somesite.com&page_number=$i";

    $response = json_decode(file_get_contents($url), true);

    $resp = FlattenMultiArray($response, true, NULL);
    
    print_r($resp['county']);

}// end for

Prints out county name 5 times , so I guess that means it works

Edited January 21, 2019 by ludo1960

Barand · January 21, 2019

I cannot see anything obvious.

I don't know what editor you are using but I did get a syntax error when I copied and pasted some of your code. The error disappeared when I deleted the whitespace between the lines, so it looks like some weird unprintable characters are lurking in there.

ludo1960 · January 21, 2019

I use visual studio code (latest version), is this Bill Gates revenge? What editor have you got? The only line that looks suspicious is:

array_push($values, array_values($myarray));

Edited January 21, 2019 by ludo1960

kicken · January 21, 2019

There is a UTF Byte order mark at the end of the code when I copy and paste it that causes an error. Not sure if that is in your original file or just an artifact of the copy/paste process.

If you copied code from somewhere into visual studio then you may have picked it up by accident.

Barand · January 21, 2019

$db->exec("DROP TABLE IF EXISTS test2DB");
//<---- I GOT THE ERROR HERE
$db->exec("CREATE TABLE test2DB (

Sign In

PDO double entries

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information