PDO double entries

[ludo1960]

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]

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]

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]

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]

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]

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]

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]

$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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

$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]

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]

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]

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]

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