Mysqli Prepare Statement Insert Not Inserting

Question

I'm already connected to the database. When I echo all of the variables, they work, but they just will not insert into my database table. I have the table name correct. Here is the code:

<?php

$pid = '1'; 
$pname = 'name'; 
$poster_id = '2';
$poster_name = 'name2'; 
$message = 'This is the message';

$datetime = date("M d, Y");

// insert into database
$ins  = "INSERT INTO messages (profile_id, profile_name, poster_id, poster_name, message, countnum, postdate) VALUES (?, ?, ?, ?, ?, ?, ?)";

$stmt = $con->prepare($ins);
$num = 1;
$stmt->bind_param('isissis', $pid, $pname, $user_id, $user, $comment, $num, $datetime);
$stmt->execute();

?>

Thanks for any help in advance.

Answer 1

You have a few variables that don't match.

$poster_id - $poster_name - $message

that are aligned with and in your binds:

$user_id, $user, $comment

---

This should work now:

<?php

$pid = '1'; 
$pname = 'name'; 
$poster_id = '2';
$poster_name = 'name2'; 
$message = 'This is the message';

$datetime = date("M d, Y");

// insert into database
$ins  = "INSERT INTO messages (profile_id, profile_name, poster_id, poster_name, message, countnum, postdate) VALUES (?, ?, ?, ?, ?, ?, ?)";

$stmt = $con->prepare($ins);
$num = 1;
$stmt->bind_param('isissis', $pid, $pname, $poster_id, $poster_name, $message, $num, $datetime);
$stmt->execute();

?>

Yet, you should replace $stmt->execute(); with if(!$stmt->execute()){trigger_error("there was an error....".$con->error, E_USER_WARNING);}

in order to catch the errors.

Also add error reporting to the top of your file(s) which will help find errors.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);

// rest of your code 

which would have signaled an undefined variable warning.

Sidenote: Error reporting should only be done in staging, and never production.

---

An insight

As pointed out by Ghost:

$datetime format M d, Y is suspicious too, it could screw up Y-m-d H:i:s's format of column DATETIME if its indeed that way.

therefore you may need to change

$datetime = date("M d, Y");

to

$datetime = date("Y-m-d H:i:s");

or

$datetime = date("Y-m-d");

depending on what your column type is set to.