ludo1960 Posted May 13, 2019 Share Posted May 13, 2019 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 More sharing options...
gw1500se Posted May 13, 2019 Share Posted May 13, 2019 $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. Link to comment Share on other sites More sharing options...
Barand Posted May 13, 2019 Share Posted May 13, 2019 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 More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 can I put a foreach round the $res->execute() ? Link to comment Share on other sites More sharing options...
Barand Posted May 13, 2019 Share Posted May 13, 2019 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  Link to comment Share on other sites More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 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 Link to comment Share on other sites More sharing options...
Barand Posted May 13, 2019 Share Posted May 13, 2019 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 ] ); } Â Link to comment Share on other sites More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 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 ] ); } Â Link to comment Share on other sites More sharing options...
Barand Posted May 13, 2019 Share Posted May 13, 2019 Either they are in the $newarr twice or the code is being run twice (or already in the db before running once) Link to comment Share on other sites More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 $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();  Link to comment Share on other sites More sharing options...
gw1500se Posted May 13, 2019 Share Posted May 13, 2019 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. Link to comment Share on other sites More sharing options...
Barand Posted May 13, 2019 Share Posted May 13, 2019 Sounds like something is causing the code to be run twice so you need to determine what that something is. Link to comment Share on other sites More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 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 Link to comment Share on other sites More sharing options...
gw1500se Posted May 13, 2019 Share Posted May 13, 2019 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? Link to comment Share on other sites More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 db truncated each time before I vist the page, and yep, no HTML Link to comment Share on other sites More sharing options...
gw1500se Posted May 13, 2019 Share Posted May 13, 2019 Truncated?? I would expect 'delete'. Link to comment Share on other sites More sharing options...
Barand Posted May 13, 2019 Share Posted May 13, 2019 Why? TRUNCATE is faster than DELETE plus it resets the auto_increment counter. Link to comment Share on other sites More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 Just tried delete and create a new table, still inserted twice! Link to comment Share on other sites More sharing options...
Barand Posted May 13, 2019 Share Posted May 13, 2019 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(); } Â Link to comment Share on other sites More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 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 More sharing options...
gw1500se Posted May 13, 2019 Share Posted May 13, 2019 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. Link to comment Share on other sites More sharing options...
Barand Posted May 13, 2019 Share Posted May 13, 2019 How are you determining that it has been added twice? Link to comment Share on other sites More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 HTML displays correctly as does php echo Link to comment Share on other sites More sharing options...
ludo1960 Posted May 13, 2019 Author Share Posted May 13, 2019 I am using PHPmyadmin, but have also tried from the mysql shell and the results tally Link to comment Share on other sites More sharing options...
gw1500se Posted May 13, 2019 Share Posted May 13, 2019 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.