Insert data into mysql

[ludo1960]

Hi guys,

I have an array:

array 
(
[apple]=> red,
[orange] => orange,
[banana] => yellow
)

That I want to insert into the apidata table, the PDO connection is good so I guess my code is wrong:

    $mykeys = implode(', ', array_keys($newarr));
    
    $myplaceholders[] = '(' . implode (", ", array_fill(0, count($newarr), '?')) . ')';
    
    $values = array_values($newarr);

$res = $db->prepare("INSERT INTO apidata (item, value) VALUES $myplaceholders") ;

$res->execute([$mykeys, $values]); 

The apidata table has three fields id (auto_increment) item and value, where am I going wrong?

Thanks

[gw1500se]

$res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?,?)") ;

For inserting multiple records you will need to iterate through the array and call 'execute' for each value pair.

[Barand]

You currently have

$res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?,?,?)");
$res->execute("apple,orange,bannana", "red,orange,yellow");

You should have
 

$res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?,?)");
$res->execute("apple", "red");
$res->execute("orange", "orange");
$res->execute("banana", "yellow");

So you are going wrong everywhere

[ludo1960]

can I put a foreach round the $res->execute() ?

[Barand]

Alternative

$res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?,?), (?,?), (?,?) ");
$res->execute( [ 'apple', 'red, 'orange', 'orange', 'banana', 'yellow' ] );

 

3 minutes ago, ludo1960 said:

can I put a foreach round the $res->execute() ?

Yes, iteration would be the best way to go if you want the separate inserts. I was just indicating you need three of them
 

Edited May 13, 2019 by Barand

[ludo1960]

Nearly there, data is inserted twice:

    $keys = array_keys( $newarr ); 
    $vals = array_values( $newarr );
    $size = sizeof( $newarr ); 

    $res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?, ?)") ;
    
    for($x = 0; $x < $size; $x++ ) { 
    $res->execute([ $keys[$x], $vals[$x] ]); 

another hint please

Edited May 13, 2019 by ludo1960

[Barand]

I don't see why that would insert the data twice, unless you run the code twice.

Why don't you just use a foreach loop?
 

foreach ($newarr as $item => $val) {
    $res->execute( [ $item, $val ] );
}

 

[ludo1960]

A lot neater, thats'for sure, alas the data is still doubled up.

    $res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?,?)") ;
    
    foreach ($newarr as $item => $val) {
        $res->execute( [ $item, $val ] );
    }

 

[Barand]

Either they are in the $newarr twice or the code is being run twice (or already in the db before running once)

[ludo1960]

$newarr has 23 items, data base has been truncated, 46 items are inserted. I've put the code on a php page and visited it in the brower, could that cause the doubling up? Or is there a command that says "i've finished inserting data, close the query"? Clutching at straws now

The connection.php from yourself:

<?php      

const HOST     = 'localhost';
const USERNAME = 'xxxxxxxxxxxxxxxxxxx';
const PASSWORD = 'xxxxxxxxxxxxxxxxxxx';
const DBNAME   = "xxxxxxxxxxxxxxxxxxx";

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

 

Edited May 13, 2019 by ludo1960

[gw1500se]

I'm guessing you have your page set up so it is running or being called twice with the same data. You need to show more of the code. In particular how you trigger the page that runs this code.

[Barand]

Sounds like something is causing the code to be run twice so you need to determine what that something is.

[ludo1960]

As requested the complete test.php:

<?php    

include 'connection.php' ;

$url = "http://admin.contenta.com/api";

$jsondata = file_get_contents($url);

$response = json_decode($jsondata, true);

$myarray = $response['links'] ;

foreach ($myarray as $k => $v) {
    $newarr[$k] = $v['href']; 
}

    $res = $db->prepare("INSERT INTO apidata (item, value) VALUES (?,?)") ;
    
    foreach ($newarr as $item => $val) {
        $res->execute( [ $item, $val ] );
    }

echo "<pre>";  print_r( $newarr ); echo "</pre>"; // here are the 23 itemss returned

Then I just visit in the browser http://mytestsite.com/test.php

Edited May 13, 2019 by ludo1960

[gw1500se]

So there is no HTML? You just click the link and display a blank page? Do you clear the DB before running the test each time?

[ludo1960]

db truncated each time before I vist the page, and yep, no HTML

[gw1500se]

Truncated?? I would expect 'delete'.

[Barand]

Why? TRUNCATE is faster than DELETE plus it resets the auto_increment counter.

[ludo1960]

Just tried delete and create a new table, still inserted twice!

[Barand]

What about

    echo recordCount($db) . '<br>';

    foreach ($newarr as $item => $val) {
        $res->execute( [ $item, $val ] );
    }

    echo recordCount($db) . '<br>';
    

where recordCount() is

    function recordCount($db)
    {
        $res = $db->query("SELECT COUNT(*) FROM apidata");
        return $res->fetchColumn();
    }

 

Edited May 13, 2019 by Barand

[ludo1960]

Adding the function and the counts so:

    echo recordCount($db) . '<br>';
   
	foreach ($newarr as $item => $val) {
        $res->execute( [ $item, $val ] );
    }
    
	echo recordCount($db) . '<br>';

gives me 

0

23

but still get the data doubled up, beginning to think i've got a bum copy of mariaDB version 10.1.38

[gw1500se]

Unlikely (although a bad install is not impossible). If you add some HTML and echo something, see if it echos twice or if the page is reloaded.

[Barand]

How are you determining that it has been added twice?

[ludo1960]

HTML displays correctly as does php echo

[ludo1960]

I am using PHPmyadmin, but have also tried from the mysql shell and the results tally

[gw1500se]

I don't know what "displays correctly" means. You need to determine if the page is being executed twice which seems to be the only way this can happen.