Consultas con MySQLi en PHP

En este post veremos cómo hacer diferentes tipos de consultas con la extensión MySQLi de PHP, la idea de este post es que usted tenga la idea de como realizar un CRUD(create, read, update, delete) o en español llamado comúnmente ABM(altas, bajas, modificación)

Ya en otro Post hemos visto como realizar conexiones a base de datos MySQL, MySQLi es una extensión que nos permite comunicarnos con la base de datos.

Para hacer los ejemplos de las consultas usaremos distintos tipos de formularios. Estas consultas las haremos sobre una tabla de una base de datos products(productos)

• products
  • id
  • product
  • price
  • quantity

Ejecutando una consulta en la base de datos

En PHP hay muchísimas maneras de agregar datos a la base de datos, no solo existe utilizando la extensión MySQLi también podemos utilizar la extesion PDO y esto solo en MySQL además de esto dentro de las misma podemos hacer una consulta.

Particularmente me gusta utilizar prepare para hacer una consulta a la base de datos, me parece que queda prolijo.

Hagamos un formulario para agregar productos:

<style>
form{
    width:420px;
    background-color:lightgrey;
    padding:7px;
}
div.form-section{
    margin:4px;
    text-align:right;
}
label{
    width:100px;
    display:inline-block;
}
.form-section input{
    display:inline-block;
    
    width:300;
}
.form-button{
    margin:4px;
    text-align:right;
}
</style>
<form method="POST" action="insert.php">
    <h2>New Product</h2>
    <div class="form-section">
        <label>product</label>
        <input type="text" name="product">
    </div>
    <div class="form-section">
        <label>Price</label>
        <input type="text" name="price">
    </div>
    <div class="form-section">
        <label>quantity</label>
        <input type="text" name="quantity">
    </div>
    <div class="form-button">
        <input type="submit" value="confirm">
    <div>
</form>

Para poder Agregar nuevos datos deberé conectarme a la base de datos tal como lo hacíamos en Conexión a bases de datos MySQL con PHP luego deber de utilizar el método prepare.

Deberías crear un nuevo archivo por ejemplo insert.php

//debes crear una conexión conn
//Luego de hacer la conexión con Mysqli
$query="INSERT INTO products (product,price,quantity) VALUES (?,?,?)";
   
$pq = $conn->prepare($query);
   $pq->bind_param('sdd',$_POST['product'],$_POST['price'],$_POST['quantity']);
   
   if($pq->execute()){
      echo "su producto ha sido ingresado correctamente";
   }else{
      echo "hubo un error al ingresar el producto en la base de datos";
   }
   $pq->close();

Si has hecho todo correcto, obtendrás ‘su producto ha sido ingresado correctamente’ en otra pagina.

? indica que será un valor agregado con bind_param.

En bind_param podemos ver que el primer parámetro es 'sdd' esto significa (string,decimal,decimal) y son los valores que espera en los siguientes parámetros.

Por último tenemos el método execute(), este ejecutará la consulta que habremos creado con prepare u otro método en nuestra base de datos.

Obteniendo datos de nuestra base

Como ya ha de saber con select podemos obtener datos en nuestra base de datos, ahora veamos como podremos hacer esta consulta con PHP.

En esta ocasión utilizaremos el método query().

Agregaré a mi archivo lo siguiente:

<div class="form-table">
    <div class="form-cels">id</div>
    <div class="form-cels">product</div>
    <div class="form-cels">price</div>
    <div class="form-cels">quantity</div>
    <div class="form-cels">selector</div>
<?php
    $servidor = "localhost";
    $usuario = "root";
    $password = "";
    $database= "ejemplomysqli";

    // Create connection
    $conn = new mysqli($servidor , $usuario , $password, $database );

    // Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }

    if($result = $conn->query("SELECT * FROM products")){
        
        foreach ( $result as $item) { ?>        
            <div class="form-cels"><?php echo $item['id']; ?></div>
            <div class="form-cels"><?php echo $item['product']; ?></div>
            <div class="form-cels"><?php echo $item['price']; ?></div>
            <div class="form-cels"><?php echo $item['quantity']; ?></div>
            <div class="form-cels">
                <input type="checkbox" name="selected" value="<?php echo $item['id'] ; ?>">
                <?php echo $item['id'] ; ?>
                <button data-selected="<?php echo $item['id'] ; ?>" >edit</button>
            </div>
        <?php
        }
    }
    else{
        "could not connect to database";
    }
?>
</div>

y en la etiqueta style agregué:

.form-table{
    width:510px;
    padding:0;
    margin:0;
}
.form-cels{
    width:19.333%;
    display:inline-block;
}

En este caso la consulta query funcionará sin utilizar:

$result = $result->fetch_all(MYSQLI_ASSOC);

Antes del foreach, en caso de que no te funcione la consulta, usa la anterior línea. Aunque te debería funcionar.

¿Pero esto no esta orientado a objetos?

No. Esto son solo ejemplos de como obtener y realizar consultas tampoco he hecho el borrar ni modificar aunque esa era la idea en un principio.

Bien, hagamos el borrar porque considero que es más difícil que el modificar. La consulta de modificar es prácticamente igual a la de insertar.

Diferencias entre los métodos prepare() y query()

Ya en las 2 secciones anteriores hablamos de prepare y query, veamos en que se diferencian estos 2.

prepare

Prepare nos permite ejecutar un prepapre statment, estos nos permiten ejecutar las consultas con gran eficiencia y protegiéndonos de Inyecciones SQL. De hecho podemos ver esto al ver los parámetros que le pasamos en la sección 1

Cuando ejecutamos:

$pq->bind_param('sdd',$_POST['product'],$_POST['price'],$_POST['quantity']);

en esta le estamos diciendo

• $_POST[‘product’] es un string.
• $_POST[‘price’] es un decimal.
• $_POST[‘quantity’] es un decimal

Esto se debe de cumplir.

query

Por otro lado tenemos a query, el cual es un metodo muy util al hacer consultas. Pero en mi opinión deberías utilizarlo en consultas que el usuario no ponga informacion en ella.

Por ejemplo, un SELECT de una categoría. SELECT de un id o cosas como esa, siempre teniendo en cuenta funciones para evitar inyecciones de SQL como pueden ser mysqli_real_escape_string.

<?php
// Datos de conexión a la base de datos
$host = "localhost";
$user = "usuario";
$password = "contraseña";
$dbname = "basededatos";

// Conexión a la base de datos
$conn = new mysqli($host, $user, $password, $dbname);

// Verificar conexión
if ($conn->connect_error) {
    die("Error de conexión: " . $conn->connect_error);
}

// Consulta con valor obtenido de un formulario (puede ser un valor malicioso)
$valor = $_POST['valor'];
$sql = "SELECT * FROM tabla WHERE columna='" . mysqli_real_escape_string($conn, $valor) . "'";

// Ejecutar consulta
$resultado = $conn->query($sql);

// Verificar resultado
if ($resultado->num_rows > 0) {
    // Procesar resultados
} else {
    echo "No se encontraron resultados.";
}

// Cerrar conexión
$conn->close();
?>

Borrar múltiples filas de nuestra tabla

Para este ejemplo variaremos un poco las cosas, en vez de hacer un form de HTML utilizaremos jQuery ajax. En esta ocasión si es que lo quieres probar dejo en tus manos tanto la creación del botón.

Para probar esto puede utilizar el siguiente código Javascript a index.php

<script src="https://code.jquery.com/jquery-3.6.0.min.js"
			  integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4="
			  crossorigin="anonymous"></script>
<script>
    
function borrar(){
    var sarray=new Array();
    let selected = document.querySelectorAll("input[name='selected']:checked");
    
    for (let x of selected){
        alert(x.value);
        sarray.push(x.value);
    }
    $.ajax({
        method: "POST",
        url: "borrarmysqli.php",
        data: { "s": sarray}
    })
    .done(function( msg ) {
        alert(msg);
        alert("los productos fueron borrados con exito");
    });
}
</script>

Para el proceso en PHP podemos crear un archivo como el siguiente:

<?php
if(isset($_POST['s'])){
    $conn = baseconnection();
    foreach ($_POST['s'] as $item){        
        $prepare=$conn->prepare("DELETE FROM products WHERE id=?");
        $prepare->bind_param("i",$item);
        $prepare->execute();
    }
    $conn->close();
}
else{
    echo "variable s not setted";
}

function baseconnection(){
    $servidor = "localhost";
    $usuario = "root";
    $password = "";
    $database= "ejemplomysqli";

    // Create connection
    $conn = new mysqli($servidor , $usuario , $password, $database );

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    return $conn;
}

¿Como puedo hacer para cambiar los datos en la tabla sin refrescar la pantalla?

De echo lo puedes hacer en esta sección del código $.ajax, aunque primero tendrás que hacer una consulta a tu base de datos antes de devolver el msg y devolver un json. Luego en done tendrás que hacer que limpie la tabla y que recorra ese json agregando los nuevos datos(data).

.done(function( msg ) {
        alert(msg);//Este alert esta puesto a propósito
        alert("los productos fueron borrados con exito");
    });

Paquetes de terceros

Por otro lado también puede utilizar query builders como por ejemplo podría ser

Pixie:

https://packagist.org/packages/usmanhalalit/pixie

Otra buena opción seria usar doctrine dbal y ORM que soporta muchos mas tipos de base de datos y que es usado por Symphony y Laravel

https://packagist.org/packages/doctrine/dbal

Clase DB o Database

las anteriores son opciones muy factibles si vas a trabajar para una empresa o puede que la empresa tenga su propio query builder echo a partir de una clase llamada DB o database.

También puedes hacer uso de autoload de composer para llamar con use quedando algo como:

use database_namespace\DB;

//en caso de querer usar la base
//suponga que aqui tiene las variables $product etc
$db= new DB();
$db->insert("table",compact("product","price","quantity"))

esto es solo un ejemplo no lo tome literal.