How to insert array values to a database table as key value pairs in php

Question

I am using array where I insert data to a table call z_error_log. These inserted data gives me an output as follows in the table under the column 'data'.

(user_role,email,nic,password,payment,reg_date)=(2,dfghjkbhjn@gmail.com,123456789045,$2y$10$R4MOTLHlhe1M9GCt0utxQO3d43oFrH.34ivCPPtHpral.cM/kW5sq,0,2020-01-13 14:44:27)

But What I actually need to do is, to insert those data to the table where it stored as follows.

user_role = 2,email=dfghjkbhjn@gmail.com,nic=123456789045,password=$2y$10$R4MOTLHlhe1M9GCt0utxQO3d43oFrH.34ivCPPtHpral.cM/kW5sq,payment,reg_date=2020-01-13 14:44:27

I need to assign the value right after its name as in the above.

Here I add the Part of the class Vendor_cont and function in the Log_model.php file where I use to enter data to the z_error_log table.

================Vendor_cont.php====================

class Vendor_cont
{

//CLASS Content

  }
            }

            // ===================EDITED If Recaptcha Fail=====================================

             else {

                             $userData = array(
                             'user_role'=>'2',
                             'email' => $email,
                             'nic' => $nic,
                             'password' =>$password,
                             'payment' =>0,
                             'reg_date' =>date('Y-m-d H:i:s')
                          );

                $_SESSION['Emessages'] = 'Recaptcha Failed. Please try again later'; 

                $this->LModel->createErrorLog(3,'Vendor_cont/register_vendor/recaptcha_fail',$email,$userData);


                }

        //========================================================================================

}

==================Log_model.php==================

    public function createErrorLog($user,$function,$error_data,$data_obj){ 

            $ip=$_SERVER['REMOTE_ADDR'];
            $browser_os=  $_SERVER['HTTP_USER_AGENT'];

            $data= (array) $data_obj;

            // print_r($data);

            if (is_array($data)) {
                $val = '(' . implode(',', array_keys($data)) . ')';
                $val .= '=(' . implode(',', $data) . ')';
            } else {
                $val = $data;
            }


    // =============EDITED==============

            $oStmt= $this->oDb->prepare('INSERT INTO z_error_log (`function`, `error_data`,`data`,`user` ,`ip`,`browser_os`) VALUES (:function,:error_data,:data,:user,:ip,:browser_os)');
            $oStmt->bindParam(':function', $function, \PDO::PARAM_STR);
            $oStmt->bindParam(':error_data', $error_data, \PDO::PARAM_STR);
            $oStmt->bindParam(':data', $val, \PDO::PARAM_STR);
            $oStmt->bindParam(':user', $user, \PDO::PARAM_INT);
            $oStmt->bindParam(':ip', $ip, \PDO::PARAM_STR);
            $oStmt->bindParam(':browser_os', $browser_os, \PDO::PARAM_STR);
            $oStmt->execute();

            return  $this->oDb->lastInsertId();

        }

I really appreciate your help. Thanks in advance.

It would be easier just to encode the data as JSON, than inventing your own format and writing the code for it — ADyson, Jan 13 '20 at 10:25
is this you question is well ? https://stackoverflow.com/questions/59704029/error-showing-once-data-insert-to-the-table-notice-object-of-class-stdclass-cou — , Jan 13 '20 at 11:05
@Dilek, No its one of my group members, we work together in a project — Kash, Jan 13 '20 at 11:54

Dinesh Chandra · Accepted Answer · 2020-01-13T12:08:08.440

Please modify your createErrorLog() function using below code. I hope you can get your desired output by using this.

$email = "test@gmail.com";
$nic ="nic";
$password ="Pass@123";

$userData = array(
     'user_role'=>'2',
     'email' => $email,
     'nic' => $nic,
     'password' =>$password,
     'payment' =>0,
     'reg_date' =>date('Y-m-d H:i:s')
);
foreach($userData as $key=>$val) {
    $finalValue .= $key.'='.$val.',';
}
echo rtrim($finalValue,",");

Replace your createErrorLog() function in your model file with below code and you are done.Remember to create data column in database as text type and pass the value as string.

public function createErrorLog($user,$function,$error_data,$data_obj){ 
        $ip=$_SERVER['REMOTE_ADDR'];
        $browser_os=  $_SERVER['HTTP_USER_AGENT'];
        $data= (array) $data_obj;

        foreach($data as $key=>$val) {
            $finalValue .= $key.'='.$val.',';
        }
        $val = rtrim($finalValue,",");

        // =============EDITED==============
            $oStmt= $this->oDb->prepare('INSERT INTO z_error_log (`function`, `error_data`,`data`,`user` ,`ip`,`browser_os`) VALUES (:function,:error_data,:data,:user,:ip,:browser_os)');
            $oStmt->bindParam(':function', $function, \PDO::PARAM_STR);
            $oStmt->bindParam(':error_data', $error_data, \PDO::PARAM_STR);
            $oStmt->bindParam(':data', $val, \PDO::PARAM_STR);
            $oStmt->bindParam(':user', $user, \PDO::PARAM_INT);
            $oStmt->bindParam(':ip', $ip, \PDO::PARAM_STR);
            $oStmt->bindParam(':browser_os', $browser_os, \PDO::PARAM_STR);
            $oStmt->execute();
            return  $this->oDb->lastInsertId();
    }

Thank you for your supporting, but I don't know how to use this and insert to this table, can you please explain a little bit more — Kash, Jan 13 '20 at 11:52
i have modified the answer. Make changes at Log_model.php file — Dinesh Chandra, Jan 13 '20 at 12:09
It gives me the desired out put, But it throws an error Notice: Undefined variable: finalValue in C:\xampp\htdocs\promise\e_proc_2\Model\Log_model.php on line 199 — Kash, Jan 13 '20 at 12:17
Initialize this variable before foreach loop. as $finalValue =''; — Dinesh Chandra, Jan 13 '20 at 12:27

score 2 · Answer 2 · answered Jan 13 '20 at 10:40

MySQL does not understand array so either you can convert into json or add every data into seperate column For adding in seperate column you could use implode with column names could do the trick. Your posted question is quite huge, can you trim it with dummy code Also refer this

How to insert array values to a database table as key value pairs in php

2 Answers2