Jump to content
ludo1960

Insert data into mysql

Recommended Posts

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

Share this post


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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Truncated?? I would expect 'delete'.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

How are you determining that it has been added twice?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

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.