Convert PDO to Mysqli

How does PDO work?

PDO: PHP Data Objects.

They are a way of querying the database before preparing the SQL sentence, so that it is saved and executed before in the SQL engine and then we assign the parameters or data that we want to work with the query.

Once the code is executed, it will join the parameters to the SQL code and execute the query.

Preventing them from manipulating your SQL string and data.

Step zero: The operation.

Prepared PDO queries work like this:

1) I create my SQL statement.

2) I prepare it.

3) I assign the parameters that it will have (If not, we continue directly to the execution).

4) I execute the sentence.

5) I return them to use them.

First step: The connection.

This connection code is adapted to the phpdelusion.net guide. Which ensures prepared statements with anti SQL injection protection.

$servidor = "localhost";
$base= "basedatos";
$codificacion= "utf8";
$usuario = "root";
$contraseña = "pass";

try {
    $datosConexion = "mysql:host=$servidor;dbname=$base;charset=$codificacion";
    $atributosConexion= [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                         PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                         PDO::ATTR_EMULATE_PREPARES => false];
    $conexion= new PDO($datosConexion,$usuario,$contrasena,$atributosConexion);


    }
catch(PDOException $e)
    {
    }

Second step : The simple queries:

These are when we want to get simple data (without WHERE conditions) from the database, for that we do NOT need to prepare anything.

Therefore we will not use prepare, but the commandquery()

query() : 

Execute a standardized SQL query to the database, this requires correctly escaping the data to avoid sql injections (see: How to avoid SQL injection in PHP? ), generally used when you will get data without any condition or feature, i.e. without use WHERE.

They would be as follows:

$sql = 'SELECT nombre, color, calorias FROM frutas ORDER BY nombre';
    foreach ($conexion->query($sql) as $valor) {
        echo $valor['nombre'] . "\t";
        echo $valor['color'] . "\t";
        echo $valor['calorias'] . "\n";
    }

Third step: Using WHERE conditions or better said, using parameters.

Now let's see what happens if we need to put parameters to the SQL statements, to obtain something like this:

"SELECT usuario FROM usuarios WHERE usuario = $usuario";

For that we must assign parameters, usingexecute()

execute() : 

Run or execute a prepared statement that allows you to assign parameters that avoid SQL injection since you should not resort to quotes or escape characters, in case you want to execute the query to the database again, just execute execute again to do it.

This also requires that the sentence be prepared beforehand.

Example:

//preparacion
$consulta = $conexion->prepare('SELECT nombre, color, calorias FROM frutas
    WHERE calorias < :calorias AND color = :color');

//Asignacion, ojo, se puede usar bindParam, pero segun he leido es mejor usar bindValue

$consulta->bindValue(':calorias', $calorias);
$consulta->bindValue('color', $color);

//Ejecuto la sentencia.
$consulta->execute();

Where:

 $consulta->bindValue('calorias', $calorias) -> Asignar parametros

We assign $calories to the calories parameter, which corresponds to the calories field of the fruits table.

Fourth Step: Marking positions.

When we are going to assign parameters to the statements, these will occupy a place called Placeholder , there are two ways to use them.

1) Using ":FieldName"

We have the following SQL statement:

"SELECT nombre FROM producto WHERE idProducto = $producto";

Its parameterized version would be:

"SELECT nombre FROM producto WHERE idProducto = :producto";

Where

idProducto es el campo de la tabla
:producto es la cajita que luego recibira el valor asignado

This form allows us to assign the parameters in any possible order, because in the same way as they are ordered they already have their corresponding place.

Example 2:

"SELECT nombre FROM producto WHERE idProducto = :producto AND precio > :precio";

If in this statement we do this:

$consulta->bindValue(':precio', $precio);
$consulta->bindValue(':producto', $producto);

There will be no problems, this way also allows to maintain a better maintainability of the system, by being able to have names for each parameter.

2) Using ?

Another way is to use the closed question mark (?), the advantage is that you don't write as much hehehe, the disadvantage is that the code must assign the parameters in the same order as they are placed in the SQL statement.

Example:

We have the following SQL statement:

"SELECT nombre FROM producto WHERE idProducto = $producto";

Its parameterized version would be:

"SELECT nombre FROM producto WHERE idProducto = ?";

Where ? will receive the assignment to the parameter.

Example 2:

"SELECT nombre FROM producto WHERE idProducto = ? AND precio > ?";

If in this statement we do this:

$consulta->bindValue(1, $precio);
$consulta->bindValue(2, $producto);

It causes an error, because we are assigning the price to the ID and the ID to the price.

therefore, for this form, the order must be followed.

Being correct:

$consulta->bindValue(1, $producto);
$consulta->bindValue(2, $precio);

We use 1,2,3,4,5 for the placeholder number, because the function bindValueexpects two values.

Step Five: Additional Notes

As you can see in the SQL statement, when we match idProducto = :idProducto , a colon is displayed, this means that :idProducto is a little box where the value of the assigned variable will be inserted later.

This is because prepared statements first send MySQL the statement:

SELECT nombre, color, calorias FROM frutas
        WHERE calorias < :calorias AND color = :color
Y luego le mandan los valores

:color, $color

So that when you give it execute, both are combined something like this:

SELECT nombre, color, calorias FROM frutas
        WHERE calorias < $calorias AND color=$color

But everything happens within MySQL itself, not in PHP.

NOTE: Placing the colon in the assignment is optional, it also works like this:

$consulta->bindValue("producto", $producto);

Sixth step: Ways to assign parameters

To assign parameters to PDO, we have different equally valid ways, I will show you two for now:

1) Using array:

$_SESSION['CodUsua']

function usuario_por_codigo($CodUsua)
    {
        $con = conexion("root", "");
        $consulta = $con->prepare("select * from usuarios where CodUsua = :CodUsua");
        $consulta->execute(array(':CodUsua' => $CodUsua));
        $resultado = $consulta->fetchAll();
        return $resultado;
    }

2) Using bindValue:

 $_SESSION['CodUsua']

    function usuario_por_codigo($CodUsua)
        {
            $con = conexion("root", "");
            $consulta = $con->prepare("select * from usuarios where CodUsua = :CodUsua");
            $consulta->bindValue(':CodUsua',$CodUsua);
            $consulta->execute;
            $resultado = $consulta->fetchAll();
            return $resultado;
        }

Eighth step: Is there more to go?, yes. Get the results.

Once we have prepared, assigned and executed, we need to recover the data, for that we usefetch

We have different possibilities to recover the data, but I will leave you the one that I use:

FOR MORE KNOWLEDGE GO TO:

http://php.net/manual/en/pdostatement.fetch.php

Simple queries :

foreach ($conexion->query($sql) as $valor) {
        echo $valor['nombre'] . "\t";
        echo $valor['color'] . "\t";
        echo $valor['calorias'] . "\n";
    }

Prepared queries: fetchAll: Return the next row as an array indexed by column name

    // 1 //
    $consulta->setFetchMode(PDO::FETCH_ASSOC);
    foreach ($consulta->fetchAll() as $key => $value) {
        return $value;
    }

    //2//
    $consulta = $conexion->query('SELECT name FROM users');
    while ($resultados = $consulta->fetch())
    {
     echo $resultados['campoBaseDatos'] . "\n";
    }

    //3//
    $sql = 'SELECT primerNombre FROM  usuarios';
    foreach ($conexion->query($sql) as $valor) {
        echo $valor['primerNombre'] . "\t";
    }

    //4//
    $resultados=$consulta->setFetchMode(PDO::FETCH_ASSOC);
    foreach ($consulta->fetchAll() as $key => $value) {
        $primerNombre= $value["campodelabasededatosqueconsultamos"];
        $primerApellido= $value["primerApellido"];
    }