Generic INSERT INTO

Question

Generic INSERT INTO

GeRoHi Jun '16

Created Jun '16	Last Reply Jun '16	Replies 1	Views 1592	Votes 0

GeRoHi 2.9k

Jun '16

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 !

Jonathan Aaron Steel
79.0k

Accepted
answer

edited Jun '16
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

GeRoHi · Answer 1 · 2016-06-03T01:46:27-07:00

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 !