How to use Prepared statements with external variables in Mysqli

Question

I have the following code, which I want to use prepared statments:

prepare.php:

<?php
include "../conex.php"; // conecta

mysqli_set_charset($mysqli,"utf8"); // Transforma em UTF8 pra gravar acentos no servidor

// Inserção de variáveis do formulário no banco de dados

$sql = "INSERT INTO tabela (userid, username, meses, percdev)
VALUES ('$user_id', '$user_name', '$meses', '$percdev')";

if ($mysqli->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $mysqli->error;
}

$mysqli->close();

This code works correctly, recording the variables in the database. Since I put this code alone in a file, all these variables are in external files. The structure looks something like this:

index.php -> form where you take the values of the variables

script.php -> where variables are declared, and some mathematical operations are performed. p.ex:

    setlocale(LC_ALL, 'pt_BR', 'pt_BR.utf-8', 'pt_BR.utf-8', 'portuguese');
date_default_timezone_set('America/Sao_Paulo');

include "datas/tempo.php";

$percDevIns = empty($_POST['Tpercentdev']) ? NULL : $_POST['Tpercentdev'];

$valorIns = (788.00 * $percDevIns) / 100;

saida.php -> The file called by index.php in action of form, in which is called the script.php (in the head):

include "../../models/scripts/script.php";
// todo o HTML e PHP da página de saída

And at the end of this file is the call to the archive prepare.php, which includes variables in the bank (already with modifications of script.php).

So following a few tutorials, and a few questions from here, I tried so:

<?php
include "../conex.php";

// Transforma em UTF8 pra gravar acentos no servidor

mysqli_set_charset($mysqli,"utf8");

// Inserção de variáveis do formulário no banco de dados

$sql = "INSERT INTO tabela (userid, username, meses, percdev)
VALUES (?, ?, ?, ?)";

$stmt->bind_param('isii', $user_id, $user_name, $meses, $percdev);

$stmt->execute();
printf("Error: %s.\n", $stmt->error);
$stmt->close();
$mysqli->close();

But it doesn’t record in the bank, and it doesn’t return any errors... What I’m doing wrong?

Answer 1

First, I recommend using object-oriented so use all mysqli events like Oop and one more detail it looks like you forgot the mysqli_stmt_prepare, do so:

<?php
include "../conex.php";

// Transforma em UTF8 pra gravar acentos no servidor

$mysqli->set_charset("utf8")

// Inserção de variáveis do formulário no banco de dados

$sql = "INSERT INTO tabela (userid, username, meses, percdev)
VALUES (?, ?, ?, ?)";

if ($stmt = $mysqli->prepare($sql))
{
    $stmt->bind_param('isii', $user_id, $user_name, $meses, $percdev);

    $stmt->execute();
    printf("Error: %s.\n", $stmt->error);
    $stmt->close();
    $mysqli->close();
}

If you’re going to use procedural, then don’t use $stmt->bind_param use mysqli_stmt_bind_param, thus:

$sql = 'INSERT INTO tabela (userid, username, meses, percdev)
VALUES (?, ?, ?, ?)';

if ($stmt = mysqli_prepare($mysqli, $sql))
{
    mysqli_stmt_bind_param($stmt, 'isii', $user_id, $user_name, $meses, $percdev);

    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
}

If the script is running normally, but the results are not recording, it may be autocommit off.

With this you can try to "turn on" autocommit:

<?php
include "../conex.php";

// Transforma em UTF8 pra gravar acentos no servidor
mysqli_set_charset($mysqli,"utf8");

$mysqli->autocommit(true);//Liga o autocommit

You can also test whether the INSERT, UPDATE or REPLACE affected the table using mysqli_affected_rows:

$stmt->execute();
printf("Error: %s.\n", $stmt->error);
printf("%d linhas inseridas.\n", $stmt->affected_rows);
$stmt->close();

Return 0 it is because nay is a problem with the autocommit, that’s right disconnected he issue the value of affected lines greater than 0 if the query succeeds.

If you want to (need to) use rollbacks, then turn off the autocommit and in the end use mysql_commit after the process is ok:

$stmt->execute();
printf("Error: %s.\n", $stmt->error);
$stmt->close();

$mysqli->commit();
$mysqli->close();

Documentation:

• http://php.net/manual/en/mysqli.autocommit.php
• http://php.net/manual/en/mysqli.commit.php
• http://php.net/manual/en/mysqli.affected-rows.php