Optimizing INSERT sql with php-Mysql (Generate Sql Query from Json )

Question

Hi guys how are you doing ?

This is my INSERT, gettin variables from the front end :

 function insert_evaluation() {  

/* Gettin tons of  POST variables from ANGULARJS 1.5 FRONT END*/
 $data = json_decode(file_get_contents("php://input"));    


/* INSERTING TONS OF VARIABLES INTO MYSQL, I WOULD LIKE THIS TO BE AUTOMATED, WRITING .$data->idintervenant. FOR EXAMPLE IS REALLY ANNOYING GRRRRR. MY DREAM WOULD BE A LOOP THAT WOULD DO ALL OF THE JOB AUTOMATICALLY INSERTING THE VARIABLES AUTOMATICALLY IN THE RIGHT ORDER  */
$q = "INSERT INTO evaluations VALUES ('','".$data->idintervenant."','".$data->idresto."','".$data->noteglobale."','".$data->service."','".$data->ambiance."','".$data->attente."','".$data->caisses."','".$data->cuisines."','".$data->toilettes."')"; 

$qry = mysql_query($q);

 if ($qry) {
        $arr = array('msg' => "SUCCESS RECORD!!!", 'error' => '');
        $jsn = json_encode($arr);
        // print_r($jsn);
    } else {
        $arr = array('msg' => "", 'error' => 'ERROR RECORDING');
        $jsn = json_encode($arr);
        // print_r($jsn);
    }
    exit();  

}

Do you See how it is boring and time wasting to rewrite all the variables names like .$data->noteglobale. for example ?!

The problem is that i don't want to re-write all the variables that are coming from the front end, I would like to automatize all of that ... I was thinking about a FOR loop ... Somebody have any idea ?

It would make it dynamic, for exemple if the front end sends 10 variables, and if lately, if the ANGULARJS front end posts 12 variables to the PHP back end, it would still work, with only a SQL table modification !

My main problem : How not to rewrite the variables names and make it automatic ?

I need the insert to be generated automatically, i do not want to rewrite all the variables coming from the angularJs front end, do you see what i mean ?

Edit 1 Finally I got this :

 $data = json_decode(file_get_contents("php://input"), true);    
$columns = implode(", ",array_keys($data));
$escaped_values = array_map('mysql_real_escape_string', array_values($data));
$values  = implode(", ", $escaped_values);
var_dump($escaped_values);
$sql = "INSERT INTO evaluations VALUES ($values)";

It's nearly working ! Except that i don't know how to add the first empty column , in my $value ... I would like to push an empty value (-> It's because of the auto increment-null situed at the first column in the table evaluations")

Edit 2 : Ok thanks a lot for your help it's finally working this is my code(Sorry for the french stuff) :

 function insert_evaluation() {  

    /* Récupération des données POST */
    $data = json_decode(file_get_contents("php://input"), true);    
    $columns = implode(",",array_keys($data));
    var_dump($columns);

   $escaped_values = array_map('intval', array_values($data));
    $values  = implode(",", $escaped_values);
    var_dump($escaped_values);

$q = "INSERT INTO evaluations (idevaluation,".$columns.") VALUES (null,".$values.")";
    echo $q;

    $qry = mysql_query($q);

     if ($qry) {
            $arr = array('msg' => "Impression enregistree avec succès!!!", 'error' => '');
            $jsn = json_encode($arr);
            // print_r($jsn);
        } else {
            $arr = array('msg' => "", 'error' => 'Erreur dans la mise à jour de l enregistrement');
            $jsn = json_encode($arr);
            // print_r($jsn);
        }
        exit();  

    }

An finally, the echo $q gives me this, a correct dynamic generated SQL query :

INSERT INTO evaluations (idevaluation,noteglobale,service,ambiance,attente,caisses,cuisines,toilettes,idintervenant,idresto) VALUES (null,1,1,1,1,1,1,1,3,2)

I'll be using it on that : http://nicolash.org/evalueResto/

Tx for the security advices too !

Answer 1

First of all you need to use a validation process before inserting the incoming data from the client, please use PDO objects to ensure you don't get SQL Injection.

And a easy way to insert all the values from the incoming object is by casting it as an array and use the implode function to have it as a string.

<?php
$obj  = new stdClass();
$obj->test1 = 'data 1';
$obj->prop2 = 'data 2';

$query = 'INSERT INTO .... VALUES ("'.implode('","', (array)$obj) . '")';
echo $query;