We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Generic INSERT INTO

Hello everybody,

I would like to make a very generic INSERT request :

//Player registration
    $table=$this->persistent->table;
    $fields="`id`,";
    $values="NULL,";
    foreach($_POST as $name => $input ){
        if(!empty($input)){
            $fields.="`$name` ,";
            $values.="'$input' ,";
        }
    }
    $fields=rtrim($fields,",");
    $values=rtrim($values,",");

    $this->modelsManager->executeQuery(
        "INSERT INTO `$table` ( $fields ) VALUES ( $values ) "
    );

But my I got a scanning error, event if the request that it generated is well formed (and works, if I copy paste it in phpmyadmin)

I've tried the raw sql way :

$rawSQL="INSERT INTO `$table` ( $fields ) VALUES ( $values ) ";
    $player=new Player();
    $request=new Resultset(null,$player,$player->getReadConnection()->query($rawSQL));

Which is even more strange : I got an SQLSTATE[HY000]: General error, but the record works, all fields are well stored in my db ! (But it interrupts my script..)

What am I doing wrong ?

Thanks a lot !



78.5k
Accepted
answer
edited Jun '16

Why in the world would you ever do that with a string?!

$fields="`id`,"; 
$fields=rtrim($fields,",");

?!

PDO accepts an array for fields and data which corresponds to it. IMHO, this is best approach if you really want to avoid PHQL / ORM style:

public boolean insertAsDict (string $table, array $data, [array $dataTypes]) inherited from Phalcon\Db\Adapter

Inserts data into a table using custom RBDM SQL syntax

 //Inserting a new robot
 $success = $connection->insertAsDict(
 "robots",
 array(
      "name" => "Astro Boy",
      "year" => 1952
  )
 );

 //Next SQL sentence is sent to the database system
 INSERT INTO `robots` (`name`, `year`) VALUES ("Astro boy", 1952);

https://docs.phalcon.io/en/latest/api/Phalcon_Db_Adapter_Pdo_Mysql.html



2.9k

I'm not avoiding phql on purpose, I'm actually using it for an UPDATE on the same controller action. But I was unable to do the INSERT with modelsManager (maybe because I had no model file for the table I was trying to append to ?)

Anyway, using PDO solved my problem, thanks !