CL So CL So - 5 months ago 55
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++) {
$name=$arr[$x];
$user=new DB\SQL\Mapper($f3->get('DB'),'User');
$user->name=$name;
$user->save();
}


Finally, only one record inserted to database

I also tried
reset
, but still only one record

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


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
$db->log()
after
save()
, and now I know why there is only one record, because php stopped in first loop.

And then I enable
general_log_file
, 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
/var/log/mysql.log
and
/var/log/mysql.err
is empty.

Now I have no idea how to find out the problem

Answer

Your second attempt is correct:

foreach($names as $name) {
    $user->reset();
    $user->name=$name;
    $user->save();
}

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.

UPDATE:

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
Comments