How to use prepared statements with external variables in MySQLi

Question

How to use prepared statements with external variables in MySQLi

Navigation

#1 by (3 votes)

6

I have the following code, in 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 by writing the variables to the database. Since I put this code alone in a file, all of these variables are in external files. The structure looks something like this:

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

script.php - > where variables are declared, and some mathematical operations are performed. e.g.

    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 named by index.php in action of form , in which script.php (in head ) is called:

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 file prepare.php , which includes the variables in the database (already with script.php modifications).

So following some tutorials, and some questions from here, I tried like this:

<?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 does not write to the bank, and it does not return any errors to me ... What am I doing wrong?

php sql mysqli

asked by anonymous 24.09.2015 / 00:46

1 answer

Installing SQL Server on the client CSS: Problem in Firefox and Chrome using padding

score 3 · Accepted Answer

First, I recommend that if you use object-oriented so use all mysqli events like oop and one more detail it seems that you forgot 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 use procedural, then do not use $stmt->bind_param use mysqli_stmt_bind_param , like this:

$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 writing, it may be autocommit that is turned off.

With this you can try to "connect" 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 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();

If you return 0 it is because not is a problem with autocommit , because even off it emits affected greater than 0 if the query succeeds.

If you want to use rollbacks , then turn off autocommit and at the end use mysql_commit after processing is ok:

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

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

Documentation: