I make a module where you can filter the records by different fields Control No. id_Control
, Company Type idctg_empresa
and by Shift idctg_turno
. In my DB is designed so that in my table trabajador
have foreign keys of other tables is composed of the following data:
id_Control
nombre
ap_paterno
ap_materno
NSS
CURP
RFC
fecha_nec
id_puesto FK
id_area FK
idctg_turno FK
idctg_empresa FK
id_nomina FK
My problem is the following it shows me the data correctly that are in my BD
?php
require_once ("../config/conexion.php");
?>
<?php
$action='ajax';
if($action == 'ajax'){
$q = mysqli_real_escape_string($con,(strip_tags($_REQUEST['q'], ENT_QUOTES)));
$sTable = "trabajador t";
$sTable1 = "puesto P";
$sTable2 = "area a";
$sTable3 = "ctg_turno tu";
$sTable4 = "empresa e";
$sTable5 = "nomina n";
$sWhere = "";
$sWhere.="WHERE t.id_Control<=1000";
if ( $_GET['q'] != "" )
{
$sWhere.= " and tu.descripcion like '$q' or e.descripcion_empresa like '$q' or t.t.id_Control like '$q' or a.descripcion_area like '$q' ";
}
//$sWhere.=" order by id_Control asc";
include 'pagination.php'; //include pagination file
//pagination variables
$page = (isset($_REQUEST['page']) && !empty($_REQUEST['page']))?$_REQUEST['page']:1;
$per_page = 10; //how much records you want to show
$adjacents = 4; //gap between pages after number of adjacents
$offset = ($page - 1) * $per_page;
//Count the total number of row in your table*/
$count_query = mysqli_query($con, "SELECT count(*) AS numrows FROM $sTable $sWhere");
$row= mysqli_fetch_array($count_query);
$numrows = $row['numrows'];
$total_pages = ceil($numrows/$per_page);
$reload = './buscar.php';
//main query to fetch the data
$sql="SELECT t.id_Control, t.nombre, t.ap_paterno, t.ap_materno, t.NSS, t.CURP, t.RFC, t.fecha_nac, p.descripcion_puesto, a.descripcion_area, tu.descripcion, e.descripcion_empresa, n.descripcion_nomina
FROM $sTable
INNER JOIN $sTable1 ON t.id_puesto = p.id_puesto
INNER JOIN $sTable2 ON t.id_area = a.id_area
INNER JOIN $sTable3 ON t.idctg_turno = tu.idctg_turno
INNER JOIN $sTable4 ON t.idctg_empresa = e.idctg_empresa
INNER JOIN $sTable5 ON t.id_nomina = n.id_nomina $sWhere LIMIT $offset,$per_page";
//$sql="SELECT * FROM $sTable $sWhere LIMIT $offset,$per_page";
$query = mysqli_query($con, $sql);
//loop through fetched data
if ($numrows>0){
echo mysqli_error($con);
?>
<div class="table-responsive ">
<table class="table table-hover" style="border-color: #337ab7;">
<tr style="color: #fff; background-color: #337ab7; border-color: #337ab7;">
<th>No. Control</th>
<th>Nombre</th>
<th>Apellido Paterno</th>
<th>Apellido Materno</th>
<th>NSS</th>
<th>CURP</th>
<th>RFC</th>
<th>Fecha Nacimiento</th>
<th>Puesto</th>
<th>Area</th>
<th>Turno</th>
<th>Empresa</th>
<th>Nomina</th>
</tr>
<?php
while ($row=mysqli_fetch_array($query)){
$id_Control=$row['id_Control'];
$nombre=$row['nombre'];
$ap_paterno=$row['ap_paterno'];
$ap_materno=$row['ap_materno'];
$NSS=$row['NSS'];
$CURP=$row['CURP'];
$RFC=$row['RFC'];
$fecha_nac=$row['fecha_nac'];
$id_puesto=$row['descripcion_puesto'];
$id_area=$row['descripcion_area'];
$idctg_turno=$row['descripcion'];
$idctg_empresa=$row['descripcion_empresa'];
$id_nomina=$row['descripcion_nomina'];
?>
<tr>
<td><?php echo utf8_encode($id_Control); ?></td>
<td><?php echo utf8_encode($nombre);?></td>
<td><?php echo utf8_encode($ap_paterno);?></td>
<td><?php echo utf8_encode($ap_materno);?></td>
<td><?php echo utf8_encode($NSS);?></td>
<td><?php echo utf8_encode($CURP);?></td>
<td><?php echo utf8_encode($RFC);?></td>
<td><?php echo utf8_encode($fecha_nac);?></td>
<td><?php echo utf8_encode($id_puesto);?></td>
<td><?php echo utf8_encode($id_area);?></td>
<td><?php echo utf8_encode($idctg_turno);?></td>
<td><?php echo utf8_encode($idctg_empresa); ?></td>
<td><?php echo utf8_encode($id_nomina); ?></td>
</tr>
<?php
}
?>
<tr>
<td colspan=7><span class="pull-right"><?
echo paginate($reload, $page, $total_pages, $adjacents);
?></span></td>
</tr>
</table>
</div>
<?php
}
}
?>
without doing the inner JOIN
search for me the registers by the parameters that I ask, but when doing the Inner Join it already shows me the descriptions of the tables but it marks me the following error:
Warning: mysqli_fetch_array () expects parameter 1 to be mysqli_result, boolean given in C: \ xampp \ htdocs \ views \ ajax \ user_search.php on line 34