Jump to content

PDO double entries


ludo1960

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
Link to comment
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);

 

Link to comment
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

Link to comment
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.

Link to comment
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?

Link to comment
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
)

 

Link to comment
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;
Link to comment
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']
Link to comment
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?

Link to comment
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.

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

 

Link to comment
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!

 

Link to comment
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

Link to comment
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

 

Link to comment
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

Link to comment
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.

Link to comment
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.

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.