Jump to content
ludo1960

PDO double entries

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
$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
)

 

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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 by Barand

Share this post


Link to post
Share on other sites

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']);
 

 

Share this post


Link to post
Share on other sites

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).

Share this post


Link to post
Share on other sites

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 by ludo1960

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites
$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 by ludo1960

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by ludo1960

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites
$db->exec("DROP TABLE IF EXISTS test2DB");
//<---- I GOT THE ERROR HERE
$db->exec("CREATE TABLE test2DB (

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.