Jump to content
ludo1960

PDO double entries

Recommended Posts

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

)");

 

Share this post


Link to post
Share on other sites
3 hours ago, ludo1960 said:

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

That line is wrong. It is pushing an array on to the $values array. You need to push the individual values from the array, hence the "..." in

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

The array flattening is just a waste of time and effort. Get the data from the multi-D array directly. It's not rocket science.

$myarray = array(

    'Page_id' => $i,  //your database table column name => data you want to insert
    'County' => $response['county'],
    'Country' => $response['country'],
    'Post_Town' => $response['listings'][0]['post_town']
);

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Below is the output from

echo '<pre>', print_r($response, 1), '</pre>';

showing a cut-down version of your response array. All you need to do is step through the sequence of keys on the way down to the item you want.

Capture.PNG

Share this post


Link to post
Share on other sites

The County and Country were the easy ones, do the $response['price'] ? Please!

Share this post


Link to post
Share on other sites
  1. I don't have the full array
  2. I have shown you how to do it

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.