Jump to content

ludo1960

Members
  • Content Count

    32
  • Joined

  • Last visited

Community Reputation

0 Neutral

About ludo1960

  • Rank
    Member
  1. ludo1960

    PDO double entries

    If you are a black belt at multi-D arrays the size of Donald Trumps' head, you're probably right, if not, use the flatten function.
  2. ludo1960

    PDO double entries

    For anybody following on, here is the finished working code. If you're faced with a crazy multi dimensional array from a JSON source that you have to get into a mysql database, this is for you! <?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 = 41; $endnum = 45; //$placeholders = []; //not required! $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); $myarray = array( 'Page_id' => $i, //your database table column name => data you want to insert 'County' => $resp['county'], 'Country' => $resp['country'], 'Post_Town' => $resp['0post_town'], ); $mykeys = implode(', ', array_keys($myarray)); $myplaceholders[] = '(' . implode (", ", array_fill(0, count($myarray), '?')) . ')'; array_push($values, ...array_values($myarray)); } //endfor $res = $db->prepare("INSERT INTO YourDB ($mykeys) VALUES " . join(', ', $myplaceholders)) ; $res->execute($values);
  3. ludo1960

    PDO double entries

    That works just fine, where can I find info describing the use of "..." never seen that in anybody elses code before?
  4. ludo1960

    PDO double entries

    Hmmm, the only time i've seen odd characters floating about, was when I copied and pasted from this site. Here's the code again. i've triec mousepad and open office but nothing shows up, even tried https://marketplace.visualstudio.com/items?itemName=ShaneRay.InvisibleCharacterVisualizer#qna still no superfluous characters $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 )");
  5. ludo1960

    PDO double entries

    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));
  6. ludo1960

    PDO double entries

    $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
  7. ludo1960

    PDO double entries

    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
  8. ludo1960

    PDO double entries

    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!
  9. ludo1960

    PDO double entries

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

    PDO double entries

    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?
  11. ludo1960

    PDO double entries

    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']
  12. ludo1960

    PDO double entries

    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;
  13. ludo1960

    PDO double entries

    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?
  14. ludo1960

    PDO double entries

    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?
  15. ludo1960

    PDO double entries

    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
×

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.