Abhijit Borkakoty Abhijit Borkakoty - 4 months ago 19
Ajax Question

trouble in inserting record to database using PHP mysqli oops

I am using PHP mysqli to access and insert record to database and also prepared statements but somewhere there is an error i couldn't figure out.. pointing out the mistake will be very much helpful

mailer.php

<?php

class Submit {

const DB = 'localhost',
USER = 'test',
PASS = '123456',
DB_NAME = 'testing';


private $mysql;


public function __construct() {

$this->mysql = new mysqli(self::DB , self::USER , self::PASS , self::DB_NAME);

if ($this->mysql->connect_errno) {
echo "Error: " . $this->mysql->connect_error;
echo "<br>";
echo "Error code: " . $this->mysql->connect_errno;
}


}

public function addRecord($record) {

$status = false;

$query = "INSERT INTO mytable (name,message) VALUES (?,?)";
$stmt = $this->mysql->prepare($query);

if ( $stmt ) {

$stmt->bind_param('ss', $record->name , $record->message);

if ($stmt->execute()) {
$status = ($stmt->affected_rows == 1) ? true : false;

$stmt->fetch_object();
$stmt->close();
}
}

return $status;
}

}


$submit = new Submit();

$result = null;

if (isset($_POST['submit']) ) {
$name = isset($_POST['name']) ? trim($_POST['name']) : '';
$message = isset($_POST['message']) ? trim($_POST['message']) : '';

$result = $submit->addRecord($name,$message);

if ($result) {
echo "Message Saved";


}
}


Also i am using ajax call from an external file containing a form and scripts within that

index.php

<!DOCTYPE html>
<html>
<head>
<title>Contact Form | PHP, AJAX and MySQL</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
</head>
<body>
<br /><br />
<div class="container" style="width:500px;">
<form id="submit_form">
<label for="name">Name</label>
<input type="text" name="name" id="name" class="form-control" />
<br />
<label for="message">Message</label>
<textarea name="message" id="message" class="form-control"></textarea>
<br />
<input type="submit" name="submit" id="submit" class="btn btn-info" value="Submit" />
<span id="error_message" class="text-danger"></span>
<span id="success_message" class="text-success"></span>
</form>
</div>
</body>
</html>
<script>

jQuery(function($){

$('form#submit_form').submit(function(e){
e.preventDefault();
var name = $(this).find('#name').val(),
message = $(this).find('#message').val();

if(name == '' || message == '') {
$('#error_message').html("All Fields are required");
}

else {
$('#error_message').html('');
$.ajax({
url:"mailer.php",
method:"POST",
data:{
name: name,
message: message
},
success:function(data){
$("form").trigger("reset");
$('#success_message').fadeIn().html(data).fadeOut(3000);

}
});
}
});
});

</script>

Answer

You are giving 2 parameters to your addRecord() method, but it expects only 1. But, it seems it expects an object which you are not initializing so I adjusted it, so it takes the two parameters you are giving it.

public function addRecord($name, $message) {

    $status = false;

    $query = "INSERT INTO mytable (name,message) VALUES (?,?)";
    $stmt = $this->mysql->prepare($query);

    if ( $stmt ) {

        $stmt->bind_param('ss', $name , $message);

        if ($stmt->execute()) {
            $status = $stmt->affected_rows === 1;
        }
    }
    return $status;
}

Also I removed some unnecessary steps in the method:

$status = ($stmt->affected_rows == 1) ? true : false;
$status = $stmt->affected_rows === 1; 

The comparison itself will return a boolean, so no need to use an explicit structure.

$stmt->fetch_object();
$stmt->close();

Fetching the object without ever using it is a waste. When leaving the scope of the method, the garbage collector will unset the stmt.

Code to test the function:

class Submit {

const DB = 'localhost',
      USER = 'test',
      PASS = '123456',
      DB_NAME = 'testing';
  private $mysql;

  public function __construct() {
      $this->mysql = new mysqli(self::DB , self::USER , self::PASS , self::DB_NAME);
      if ($this->mysql->connect_errno) {
          echo "Error: " . $this->mysql->connect_error;
          echo "<br>";
          echo "Error code: " . $this->mysql->connect_errno;
      }
    }

    public function addRecord($name, $message) {
        $status = false;
        $query = "INSERT INTO mytable (name,message) VALUES (?,?)";
        $stmt = $this->mysql->prepare($query);
        if ( $stmt ) {
            $stmt->bind_param('ss', $name , $message);
            if ($stmt->execute()) {
                $status = $stmt->affected_rows === 1;
            }
        }
        return $status;
    }
}


$submit = new Submit();

$result = null;
$name = "dsfdsf";
$message = "message";
$result = $submit->addRecord($name,$message);
var_dump($result); // bool(true) 
Comments