准备好的语句没有填充DB

Question

I'm trying to make my first prepared statement work, but so far I'm unsuccesful. I hope you're able to help me out. I have an index.html with a simple form that parses it's data to insert.php. However, the data is not being written into my DB. Here's what I've got:

insert.php

if (isset($_POST['submit'])) {

    $mysqli = new     mysqli("hosts","user","pass","db");

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s
", mysqli_connect_error());
exit();
}

$stmt = $mysqli->prepare("INSERT INTO mail_platform (first_name, last_name, email,     preference_exchange, preference_news) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param('sssii', $first_name, $last_name, $email, $preference_exchange,     $preference_news);

$first_name = isset($_POST['first_name'])
      ? $mysqli->real_escape_string($_POST['first_name'])
      : '';
$last_name = isset($_POST['last_name'])
      ? $mysqli->real_escape_string($_POST['last_name'])
      : '';
$email = isset($_POST['email'])
      ? $mysqli->real_escape_string($_POST['email'])
      : '';
$preference_exchange = isset($_POST['preference_exchange'])
      ? $mysqli->real_escape_string($_POST['preference_exchange'])
      : '';
$preference_news = isset($_POST['preference_news'])
      ? $mysqli->real_escape_string($_POST['preference_news'])
      : '';

$stmt->execute();

$stmt->close();
}

echo "Thank you for signing up!";

?>

index.html

<form method="post" action="insert.php">
First name: <input type="text" name="first_name"><br>
Last name: <input type="text" name="last_name"><br>
E-mail: <input type="text" name="email"><br>
Please choose what kind of e-mails you would like to receive:<br>
News from my exchange: <input type="checkbox" name="preference_exchange" value="true">   <br>
Generel news: <input type="checkbox" name="preference_news" value="true"><br>
<input type="submit" value="Subscribe">
</form>

And here's my MySQL:

CREATE TABLE `mail_platform` (
`ID` int(20) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(60) CHARACTER SET utf8 NOT NULL,
`last_name` varchar(60) CHARACTER SET utf8 NOT NULL,
`email` varchar(100) CHARACTER SET utf8 NOT NULL,
`registration_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`preference_exchange` tinyint(1) NOT NULL DEFAULT '0',
`preference_news` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`)
)

Thanks in advance!

Answer 1

First, break yourself of the habit of using mysqli_real_escape_string(). The best part of using query parameters is that it's not necessary to do escaping. In fact, you should not, because you'll end up with literal backslash characters in the strings stored in your database.

Second, you should always check the return status of prepare() and execute(). If any error occur in parsing or execution, these functions return false. Check for this and then if that has happened, you should look at the error returned.

The reason it's important to check for errors is that if the statement fails, you won't know it or the reason why unless you examine the error.

Also if you use PHP 5.3, you can use the ?: shortcut to make this code a little more brief.

$stmt = $mysqli->prepare("INSERT INTO mail_platform
    (first_name, last_name, email, preference_exchange, preference_news) 
    VALUES (?, ?, ?, ?, ?)");
if ($stmt === false) {
    trigger_error($mysqli->error, E_USER_ERROR);
}

$stmt->bind_param('sssii', $first_name, $last_name, $email, 
    $preference_exchange, $preference_news);

$first_name = $_POST['first_name'] ?: '';
$last_name = $_POST['last_name'] ?: '';
$email = $_POST['email'] ?: '';
$preference_exchange = $_POST['preference_exchange'] ?: '';
$preference_news = $_POST['preference_news'] ?: '';

if ($stmt->execute() === false) {
    trigger_error($stmt->error, E_USER_ERROR);
}

Notice that an error on prepare() returns its message in $mysqli->error, but an error on execute() returns its message in $stmt->error;

---

Re your comment:

I just tested it myself with PHP 5.3.15 and MySQL 5.6.12. It worked perfectly. So I'm not sure what to suggest as a reason it fails in your case. Are you saying it doesn't return any error, but the row just never shows up in your table?

If you added another line for $stmt->execute() without adding error handling for that, you're back to the problem of not knowing whether it succeeded. In other words, it sounds like you did the following:

$stmt->execute(); // without checking if this had an error

if ($stmt->execute() === false) { // execute a second time
    trigger_error($stmt->error, E_USER_ERROR);
}

You don't need to execute twice. I showed calling execute() as part of the if statement, but this is a pretty common style of coding when you want to check the return value without storing the return value in a variable. It still performs the execute.