PHP Prepared statement not inserting data in mysql database

Question

I'm working on a simple project where I'm collecting the JSON data from a register page and trying to insert in mysql database using prepared statement.

This is sample query after getting the details from form

INSERT INTO `registered_users`(`USER_EMAIL`, `USER_NAME`,`USER_PWD`, `ADDED_BY`) VALUES ('aaaa@aaa.ddd ','aaaaaaa','$2y$10$NEJFPvgnR/WhDkZKWChknOzfAe6Pzk.9LOYip9y36OOoyHDQKVFPm','aaaa@aaa.ddd') 

I checked manually by running this query , it got inserted which means there's no issue with insert statement , don't know what is the issue with prepared statement , please help .

Here's the code

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
error_reporting(E_ALL);
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 * @author : Mahaveer
 * @date : 25/03/2018
 */

require_once 'database_connection.php';

class DBWebServices extends DBConnection {

    function insertUserData($register_json) {


        try {


            //decode json data to make array
            $json_data_array = json_decode($register_json, true);

            //reference : https://stackoverflow.com/questions/37367992/php-inserting-values-from-the-form-into-mysql
            //https://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php
            //https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

            $query = "INSERT INTO registered_users(`USER_EMAIL`,`USER_NAME`, `USER_PWD`, `ADDED_BY`) VALUES ((?),(?),(?),(?)); ";

            //prepare the stament
            $stmt = $this->connectDB()->prepare($query);


            if (!($stmt)) {
                echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            //Then start binding the input variables to the prepared statement:



            if (!$stmt->bind_param("ssss", $email, $name, $pwd, $modified_by)) {
                echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }



            $email = $json_data_array['register_user_email'];
            $name = $json_data_array['register_user_name'];
            $pwd = $json_data_array['user_password'];
            $modified_by = $json_data_array['register_user_email'];



            //Execute the query


            if (!($query_result=$stmt->execute())) {
                echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }


            if (!$query_result) {

                echo $stmt->error;
            }


            $stmt->close();
            //defining response in case of success or failure
            $response = array();

            if ($query_result) {
                // successfully inserted into database
                $response["success"] = 1;
                $response["message"] = "User successfully added.";
                // echoing JSON response
                echo json_encode($response);
            } else {
                //insert failed
                $response["success"] = 0;
                $response["message"] = "User cannot be added.";

                // echoing JSON response
                echo json_encode($response);
            }
        } catch (Exception $ex) {
            echo 'Exception occurred ' . $ex->getTraceAsString();
        }
    }

}

Code For Database connection :

<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

    
class DBConnection {
    
    protected function connectDB() {
        // import database connection variables
        require_once './config.php';

        // Connecting to mysql database & choosing database
        $conn = new mysqli(DB_HOST, DB_USER_NAME, DB_PWD, DB_NAME);
        
        // Check connection
        if ($conn->connect_error) {
             die("Connection failed: " . $conn->connect_error);
               }
        
        echo 'Connection to database was successful....';
        return $conn;

    }    

}

Error stack trace produces following message :

Exception occurred #0 C:\xampp\htdocs\LockerWebApp\webservices_php\locker_web_services.php(62): mysqli_stmt->execute() #1 C:\xampp\htdocs\LockerWebApp\webservices_php\validate_n_submit_user.php(94): DBWebServices->insertUserData('{"register_user...') #2 C:\xampp\htdocs\LockerWebApp\user_registraion.php(7): include('C:\\xampp\\htdocs...') #3 {main} 

and Error Message is : mysql server has gone away which is weird.

I'm stuck here it's my first php project and due to this error I can not move forward...

Answer 1

As per suggestion from @pragman I updated the code, I saved database connection to a variable and after executing the query closed the connection and it worked.

Here's the code snippet :

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
error_reporting(E_ALL);
/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 * @author : Mahaveer
 * @date : 25/03/2018
 */

require_once 'database_connection.php';

class DBWebServices extends DBConnection {

    function insertUserData($register_json) {


        try {


            //decode json data to make array
            $json_data_array = json_decode($register_json, true);

            //reference : https://stackoverflow.com/questions/37367992/php-inserting-values-from-the-form-into-mysql
            //https://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php
            //https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

            $query = "INSERT INTO registered_users(`USER_EMAIL`,`USER_NAME`, `USER_PWD`, `ADDED_BY`) VALUES ((?),(?),(?),(?)); ";
            
            $dbcon = $this->connectDB();

            //prepare the stament
            $stmt = $dbcon->prepare($query);


            if (!($stmt)) {
                echo "Prepare failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            //Then start binding the input variables to the prepared statement:



            if (!$stmt->bind_param("ssss", $email, $name, $pwd, $modified_by)) {
                echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }



            $email = $json_data_array['register_user_email'];
            $name = $json_data_array['register_user_name'];
            $pwd = $json_data_array['user_password'];
            $modified_by = $json_data_array['register_user_email'];



            //Execute the query


            if (!($query_result=$stmt->execute())) {
                echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }


            if (!$query_result) {

                echo $stmt->error;
            }


            $stmt->close();
            $dbcon->close();
            //defining response in case of success or failure
            $response = array();

            if ($query_result) {
                // successfully inserted into database
                $response["success"] = 1;
                $response["message"] = "User successfully added.";
                // echoing JSON response
                echo json_encode($response);
            } else {
                //insert failed
                $response["success"] = 0;
                $response["message"] = "User cannot be added.";

                // echoing JSON response
                echo json_encode($response);
            }
        } catch (Exception $ex) {
            echo 'Exception occurred ' . $ex->getTraceAsString();
        }
    }

}

Answer 2

<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 * @author : Mahaveer
 * @date : 25/03/2018
 */

require_once 'database_connection.php';
class DBWebServices extends DBConnection{


function insertUserData($register_json) {


    try{


    //decode json data to make array
    $json_data_array = json_decode($register_json,true);


echo $json_data_array['register_user_name'];
echo $json_data_array['register_secret_q'];
echo $json_data_array['register_secret_answer'];
echo $json_data_array['user_password'];
echo $json_data_array['userIPAddress'];
echo $json_data_array['timestamp'];
echo $json_data_array['timestamp'];
echo $json_data_array['register_user_email'];


    //reference : https://stackoverflow.com/questions/37367992/php-inserting-values-from-the-form-into-mysql
    //https://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php
    //https://websitebeaver.com/prepared-statements-in-php-mysqli-to-prevent-sql-injection

    $query = "INSERT INTO registered_users(USER_EMAIL,USER_NAME,SECRET_Q,SECRET_A,USER_PWD,USER_IP,TIMESTAMP,LAST_MODIFIED,ADDED_BY) 
                  VALUES (?,?,?,?,?,?,?,?,?) ;";


    //prepare the stament
    $stmt = $this->connectDB()->prepare($query);



    $bind_value=$stmt->bind_param("sssssssss", $json_data_array['register_user_email'],
                          $json_data_array['register_user_name'],
                          $json_data_array['register_secret_q'],
                          $json_data_array['register_secret_answer'],
                          $json_data_array['user_password'],
                          $json_data_array['userIPAddress'],
                          $json_data_array['timestamp'],
                          $json_data_array['timestamp'],
                          $json_data_array['register_user_email']);  

    if(!$bind_value){

        echo "Is problem with bind values?: (" . $bind_value->errno . ") " . $bind_value->error;
    }


    //Execute the query
    $query_result = $stmt->execute();
    $stmt->close();

   //defining response in case of success or failure
    $response = array();

    if($query_result){
        // successfully inserted into database
        $response["success"] = 1;
        $response["message"] = "User successfully added.";
        // echoing JSON response
        echo json_encode($response);

    }else {
        //insert failed
        $response["success"] = 0;
        $response["message"] = "User cannot be added.";

        // echoing JSON response
        echo json_encode($response);


    }

    } catch (Exception $ex) {
        echo 'Exception occurred '.$ex->getTraceAsString();

    }    

}   


}