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 !

Please dont use the mysql_ database extension, it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the PDO database extensions. Start here its really pretty easy — RiggsFolly
– RiggsFolly, Commented Aug 2, 2016 at 17:55
Your script is at risk of SQL Injection Attack Have a look at what happened to Little Bobby Tables Even if you are escaping inputs, its not safe! Use prepared statement and parameterized statements — RiggsFolly
– RiggsFolly, Commented Aug 2, 2016 at 17:56
Thank you a lot but it is not my question. There is no security features in my script, because its not my problem. — Jojo Bebert
– Jojo Bebert, Commented Aug 2, 2016 at 18:00

Emilio Borraz · Accepted Answer · 2016-08-02 18:07:40Z

0

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;

edited Aug 2, 2016 at 18:07

answered Aug 2, 2016 at 18:02

Emilio Borraz

5263 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jojo Bebert Over a year ago

Oh ok thank you a lot.. But i'm not using Pdo yet ... But if that's the only way, ok ..

Emilio Borraz Over a year ago

Please note that in MySQL the values' order mathers when you don't specify the fields to be inserted.

Jojo Bebert Over a year ago

I know i'm annoying, but is there any way to make it without PDO ? This seems pretty long to learn ...

Emilio Borraz Over a year ago

Yes, with Prepared Statements, please check the two links below: php.net/manual/en/mysqli.quickstart.prepared-statements.php php.net/manual/en/security.database.sql-injection.php

Jojo Bebert Over a year ago

Thank you it seems that i 've got something there also : stackoverflow.com/questions/17757087/…

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related