problem with a query prepared in php and mysqli

Question

problem with a query prepared in php and mysqli

Navigation

#1 by (1 votes)
#2 by (0 votes)

3

I would like to ask for your help with the code of a registration form which I am trying to change to use prepared queries:

$registros2=mysqli_query($conexion,"SELECT id_cliente FROM clientes WHERE correo='$correo'");
$fila2=mysqli_fetch_array($registros2); 

mysqli_query($conexion,"INSERT INTO codigos (codigo,fecha_antigua,id_cliente) VALUES ('$codigo','$fecha','$fila2[id_cliente]')");

I do not know exactly how to make a query prepared from this query due to $ row2 [client_id]. Thanks in advance for the help you can give me.

php mysqli

asked by jmz 27.07.2018 в 02:34

source

2 answers

Problem counting all records by specialty and adding record with total Error creating bean with name 'adminServiceDAOImpl': Unsatisfied dependency expressed through field 'adminDao';

score 1 · Answer 1

I greet you and tell you the following; first your SELECT() query should look like this

$registros2 = $conexion->prepare("SELECT id_cliente FROM cliente WHERE correo = ? ");
$registros2->bind_param("s", $correo);
$registros2->execute();

EXPLANATION

The variable $registros2 equals the connection object $conexion and   this in turn accesses the prepare method; how this query works   with external values and we want to use prepared statements; we use   placeholders ? to identify the location of the   variable $correo

With the bind_param() method we indicate the dynamic parameter that is going   to receive is the variable $ mail; turn inside quotes   We indicate the type of data that we are sending; where s is for    strings e i for integers

Finally with the variable $ registro2 we access the method execute()   which in turn will execute the previous sentence

FOR YOUR SECOND SENTENCE, IT SHOULD STAY

$stmt = $conexion->prepare("INSERT INTO codigos(codigo, fecha_antigua, id_cliente) VALUES (?, ?, ?)");
$stmt->bind_param("s", $codigo);
$stmt->bind_param("s", $fecha_antigua);
$stmt->bind_param("i", $id_cliente);
$stmt->execute();

You just have to verify that the data types in bind_param() correspond because I put them to how I interpret them to be

s => string
i => integer
d => double
b => blob

The need to use placeholders, which in mysqli are the ? and in PDO are the : plus a descriptive name, it is to indicate to where the interpolation of that marker will occur by the dynamic value what is coming of some variable

score 0 · Answer 2

Neither of your two queries meets the criteria of prepared queries .

In summary, a prepared consultation comprises several stages:

Write the query to prepare, which should not carry data in itself, but markers for each data.

Send to prepare the query with prepare

Pass the values separately using the bind_param method

Run the query with execute

Eventually make a bind_value to get the values (in case of queries SELECT ). Or use another method of storing results.

To meet these criteria, you can write your code like this:

$sqlCliente="SELECT id_cliente FROM clientes WHERE correo=?";
$stmtCliente=mysqli_prepare($conexion,$sqlCliente);
/*
    La letra "s"  indica que el tipo de dato es varchar (string)
    si fuese un entero se pondría una "i"
    si fuese un doble una "d"  y si fuese del tipo blob una "b"
*/
mysqli_stmt_bind_param($stmtCliente,"s",$correo);
mysqli_stmt_execute($stmtCliente);
mysqli_stmt_bind_result($stmtCliente, $idCliente);

$sqlCodigo="INSERT INTO codigos (codigo,fecha_antigua,id_cliente) VALUES (?,?,?)";
$stmtCodigo=mysqli_prepare($conexion,$sqlCodigo);
mysqli_stmt_bind_param($stmtCodigo,"ssi",$codigo,$fecha,$idCliente);
mysqli_stmt_execute($stmtCodigo);

I have used more descriptive variable names. And I have respected the procedural style that you show in your code, although the object-oriented style is more modern and should be learned.

If there is any doubt you can say it in comments.

I hope you find it useful.