Question:
I can tell you that I have read the blog at https://www.vichaunter.org/desarrollo-web/joins-mysql-bien-explicado-lo-necesitas-saber several times
but I cannot implement a JOINS in my project, what I have so far works perfect for me but I am doing 2 or 3 queries and the idea is to optimize the queries to my database for logical reasons.
WHAT I HAVE
Query 1
SELECT * FROM pagos WHERE user = '$usua' ORDER BY id DESC LIMIT $init, $limit_end
Query 2
SELECT * FROM users WHERE username = '$rowUser'
Using these 2 Query I retrieve the information contained by means of a mysqli_fetch_assoc and I have made my project work, but I would like to learn how to implement a JOINS in my query to give it practicality with the use of dynamics with the use of a LIKE '%$busqueda%' as I have done in other of my queries that I have implemented a search like this:
SELECT * FROM users
WHERE (idusuario LIKE '%$busqueda%' OR
nombre LIKE '%$busqueda%' OR
email LIKE '%$busqueda%')
AND user_type = '$user_tipo'
ORDER BY id
DESC LIMIT $init, $limit_end
What I Need That the query make me a SELECT and with the user data from the ORDER table the name of the USERS table is recovered and that by means of a mysqli_fetch_array can recover said data, I leave below the structure of my 2 tables
TABLA USER
-id
-idusuario
-nombre
-usernameÍndice
-email
-tlf
-cel
-direccion
TABLA PEDIDO
-id
-user
-monto
-concepto
-mes_de_pago
-afiliacion
-banco_origen
-banco_destino
En la tabla PEDIDO user = idusuario de la tabla USERS
With your help I tried to do it myself but not succeeded, I am going to put the full function that I am working on
<?php
function lista_pagos_mes(){
global $db, $usua, $mes, $limit_end;
$url = basename($_SERVER ["PHP_SELF"]);
if (isset($_REQUEST['busqueda'])) {
$busqueda = strtolower($_REQUEST['busqueda']);
} else {
$busqueda = "";
}
if (isset($_GET['mes']))
$ini=$_GET['mes'];
else
$ini=1;
$init = ($ini-1) * $limit_end;
if (isAdmin()) {
if (empty($busqueda)) {
$busqueda = "";
$countmes="SELECT COUNT(*) FROM pagos WHERE status_pago = 'PENDIENTE'";
$querymes = "SELECT * FROM pagos WHERE status_pago = 'PENDIENTE' ORDER BY id DESC LIMIT $init, $limit_end";
$resultmes = mysqli_query($db, $querymes);
$rowmes = mysqli_num_rows($resultmes);
$mensaje = 'No hay datos que Mostrar';
} else {
$countmes="SELECT COUNT(*) FROM pagos WHERE status_pago = 'PENDIENTE' AND (user LIKE '%$busqueda%')";
$querymes = "SELECT * FROM pagos WHERE status_pago = 'PENDIENTE' AND (user LIKE '%$busqueda%') ORDER BY id DESC LIMIT $init, $limit_end";
$resultmes = mysqli_query($db, $querymes);
$rowmes = mysqli_num_rows($resultmes);
$mensaje = 'No hay datos que Mostrar';
}
} else {
$countmes="SELECT COUNT(*) FROM pagos WHERE user = '$usua'";
$querymes = "SELECT * FROM pagos WHERE user = '$usua' ORDER BY id DESC LIMIT $init, $limit_end";
$resultmes = mysqli_query($db, $querymes);
$rowmes = mysqli_num_rows($resultmes);
$mensaje = 'No hay datos que Mostrar del usuario ' .$_SESSION['user']['username'];
}
/* querys */
if (!$rowmes){
echo '<div class="alert alert-danger" role="alert" >';
echo '<h3>';
echo $mensaje;
//unset($_SESSION['successmes']);
echo '</h3>';
echo '</div>';
} else {
$num = $db->query($countmes);
$x = $num->fetch_array();
$total = ceil($x[0]/$limit_end);
if (isAdmin()){
echo '<div class="table-responsive">';
echo '<table id="tabla1" class="table table-bordered table-hover stacktable">
<thead>
<tr>
<th>ID</th>
<th>Usuario</th>
<th>Nombre</th>
<th>Fecha de Pago </th>
<th>Monto / Mes Pagado</th>
<th>Nro Transf / CI</th>
<th>Desde / Hasta</th>
<th>Accion</th>
</tr>
</thead>
<tbody>';
$c = $db->query($querymes);
while($rowmes = $c->fetch_array(MYSQLI_ASSOC))
{
$date = date_create($rowmes['fecha_pago']);
$fecha = date_format($date, 'd-m-Y');
$fecha_pago = $fecha;
$rowUser = $rowmes['user'];
$rowid = $rowmes['id'];
$sql_user = "SELECT * FROM users WHERE username = '$rowUser' OR nombre LIKE '%$busqueda%'";
$resultado_user = mysqli_query($db,$sql_user);
$row_user = mysqli_fetch_assoc($resultado_user);
$rowNombre = $row_user['nombre'];
$link_aprobar_mes = '<form autocomplete="off" class="was-validated" method="post" action= "mensualidades.php?id='.$rowid.'&user='.$rowUser.'"><button type="submit" class="btn btn-primary" name="aprobar_pago_btn">Aprobar</button> </form>';
echo '<tr>';
echo '<td>'.$rowid.'</td>
<td>'.$rowUser.'</td>
<td>'.$rowNombre.'</td>
<td>'.$fecha_pago .'</td>
<td>'.$rowmes['monto'].' BsS / '.$rowmes['mes_de_pago']. '</td>
<td>'.$rowmes['nro_transf'] . ' / '.$rowmes['ci_nro_cuenta'].'</td>
<td>'.$rowmes['banco_origen'].' / '.$rowmes['banco_destino'] .'</td>
<td>'.$link_aprobar_mes .'</td>
</tr>';
}
echo '</tbody></table>';
}
else
// SI NO ES ADMIN
?>
Just as I have it, it only allows me to filter the results according to what you want to search for, but like this it works only if you do a search for the customer number and my idea is that if you search for a name, do it too ..!
Answer:
So now I understand the use of JOIN in MYSQL I was somewhat lost, but I have solved using the following query
based on the following example:
SELECT nombre_columna
FROM tableA
INNER JOIN tableB
ON tableA.nombre_columna=tableB.nombre_columna
I have rewritten my query like this:
$querymes = "SELECT * FROM pagos
INNER JOIN users
ON pagos.user=users.idusuario
WHERE status_pago = 'PENDIENTE' AND (user LIKE '%$busqueda%' OR nombre LIKE '%$busqueda%') ORDER BY fecha_pago ASC LIMIT $init, $limit_end";
When combining and / or joining the records of more than one tables (it can be two or more tables). The ON clause is used to match records in two or more tables through a column with a value in common between the tables to be joined, based on the value of the column id. Using INNER JOIN joins an inner join allows rows from any of the tables to appear in the result if and only if both tables meet the conditions specified in the ON clause, to get results related to each other in a php and mysql query we need in our web projects.