I commented to them I have read several times the blog of link
but I can not implement a JOINS in my project, what I have so far works perfectly but I'm 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 practicality with the use of dynamisms with the use of a LIKE '%$busqueda%' as I have done in others of my query 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 makes me a SELECT and with the user data of the ORDER table the name of the USERS table is retrieved and that by means of a mysqli_fetch_array I can recover this data I then leave 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 the help of you I try to do it myself but I can not do it, I'm going to place the complete function that I'm 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 look for, but how it works only if a search of the client number is made and my idea is that if you search for a name, it will do so ..!