Jump to content

Insert data into mysql


ludo1960
 Share

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

Link to comment
Share on other sites

  • Replies 56
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

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

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share


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