CL So CL So - 1 year ago 120
SQL Question

How to insert multiple records without primary key in Fat-Free Framework

I try to insert multiple records to a no PK table

for ($x = 0; $x < count($arr); $x++) {
$user=new DB\SQL\Mapper($f3->get('DB'),'User');

Finally, only one record inserted to database

I also tried
, but still only one record

for ($x = 0; $x < count($arr); $x++) {
$user=new DB\SQL\Mapper($f3->get('DB'),'User');

If I add an auto_increment primary key to this table, then it is no problem.

Update 1:

I tested the problem again in web browser. And then I got this error

Internal Server Error

PDOStatement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

So I cannot execute
, and now I know why there is only one record, because php stopped in first loop.

And then I enable
, and I found this log in the file

160621 18:00:53 47 Connect dev@localhost on my_db
47 Query SET NAMES utf8
47 Query SHOW columns FROM `my_db`.`UserAccount`
47 Query INSERT INTO `UserAccount` (`name`) VALUES ('uName')
47 Quit

So I try to run this SQL
INSERT INTO `UserAccount` (`name`) VALUES ('uName')
and got no error.

I also checked
is empty.

Now I have no idea how to find out the problem

Answer Source

Your second attempt is correct:

foreach($names as $name) {

It's meant to work, even for mappers with no primary key.

Just bear in mind though that without a primary key, you won't be able to update $user. Quoted from the docs:

Although the issue of having primary keys in all tables in your database is argumentative, F3 does not stop you from creating a data mapper object that communicates with a table containing no primary keys. The only drawback is: you can't delete or update a mapped record because there's absolutely no way for F3 to determine which record you're referring to.

NB: if you're still struggling with record insertion, you can output the database log $db->log() to see which queries are run behind the hood.


In order to be able to output the DB log, you can hook on the ONERROR event:

$f3->ONERROR=function($f3) {
  echo $f3->get('DB')->log();
$mapper->save();// SQL error will trigger ONERROR