Extract associative array index name to automatch the database column name

Question

Below is my code -

$insert_details = array("username"=>"pavan", "firstname"=>"pavan", "lastname"=>"r", "profile_about"=>"My name is Pavan R.");
$connection->insert($insert_details);    

        public function insert(array $insert_details) {

            $insert_query = "INSERT INTO user (username,firstname,lastname,profile_about) VALUES ($insert_details['username'],$insert_details['firstname'],$insert_details['lastname'],insert_details['profile_about'])";
            $run_insert_query = mysqli_query($this->mysql_con, $insert_query);

            if ($run_insert_query) {

                $select_query = "SELECT * FROM user ORDER BY id DESC LIMIT 1";
                $run_select_query = mysqli_query($this->mysql_con, $select_query);

                while ($selected_row = mysqli_fetch_array($run_select_query)) {
                    $id = $selected_row['id'];
                    $username = $selected_row['username'];
                    $firstname = $selected_row['firstname'];
                    $lastname = $selected_row['lastname'];
                    $profile_about = $selected_row['profile_about'];
                }

                $es_insert = array();
                $es_insert['body']  = array('id' => $id, 'username' => $username, 'firstname' => $firstname, 'lastname' => $lastname, 'profile_about' =>  $profile_about);
                $es_insert['index'] = 'test';
                $es_insert['type']  = 'jdbc';
                $es_insert['id']    = $id;
                $check_insert = $this->es_con->index($es_insert);

                if($check_insert) {
                    echo nl2br("Successfully inserted to both database and elasticsearch\n");
                }
            }
            else {
                echo nl2br("Failed to insert into database hence closing the connection\n");            
            }
        }

When I run the code I get the following error -

PHP Parse error:  syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in /var/www/html/es/combined.php on line 38

This is because of the SQL query ($insert_query). Can someone please help me debug this?

Also is there a way to extract the index names from array and pass it to database fields.In the above code, I've declared an associative array with index names same as my database column names. Is it possible to get those array index names and optimize the SQL query to just -

$insert_query = "INSERT INTO user VALUES ($insert_details['username'],$insert_details['firstname'],$insert_details['lastname'],insert_details['profile_about'])";

It should automatically extract the suitable column names from the array index name.

You need to remove the lines on top of the public function aswell. — Daan, Jul 20 '15 at 14:52
*"Can someone please help me debug this?"* You will first need to show that you put in significant effort yourself (including researching the error) and it would certainly be helpful to show what line 38 is. — Anonymous, Jul 20 '15 at 14:52
I've declared the function inside a class and function is called outside class. — Pavan R, Jul 20 '15 at 14:53
Should we **guess which line is line 38** Kinda difficult as you only put 36 lines in your example code — RiggsFolly, Jul 20 '15 at 14:54

score 0 · Accepted Answer · answered Jul 20 '15 at 14:52

0

You cannot quote array keys in "-quoted strings:

php > echo "$arr['foo']";
PHP Parse error:  syntax error, unexpected '' (T_ENCAPSED_AND_WHITESPACE), expecting identifier (T_STRING) or variable (T_VARIABLE) or number (T_NUM_STRING) in php shell code on line 1

Either go with

$sql = "... $insert_details[username] ..."
                            ^-------^---no quotes

or

$sql = "... {$insert_details['username']} ..."
            ^---------------------------^---brace syntax

And note that you are vulnerable to sql injection attacks.

answered Jul 20 '15 at 14:52

Marc B

356,200
43
426
500

Is it possible to get those array index names and optimize the SQL query to just - `$insert_query = "INSERT INTO user VALUES ($insert_details['username'],$insert_details['firstname'],$insert_details['lastname'],insert_details['profile_about'])";`It should automatically extract the suitable column names from the array index name. – Pavan R Jul 20 '15 at 15:08
technically, yes. use a prepared statement and bind your placeholders to the individual array elements, but that's not 'automatic'. – Marc B Jul 20 '15 at 15:13
Can you please explain me with an example. – Pavan R Jul 20 '15 at 15:19

Extract associative array index name to automatch the database column name

1 Answers1