Table structure
<?php
//the grupo_usuarios table contains the database user groups
CREATE TABLE `grupo_usuarios` (
`id` int(11) NOT NULL AUTOINCREMENT,
`nombre` varchar(20) COLLATE utf8_spanish_ci NOT NULL,
`descripcion` varchar(150) COLLATE utf8_spanish_ci NOT NULL
);
//the permisos table contains the permissions
CREATE TABLE `permisos` (
`id` int(11) PRIMARY KEY NOT NULL AUTOINCREMENT,
`nombre` varchar(20) COLLATE utf8_spanish_ci NOT NULL
);
// the table tiene_asignado is the result of the many-to-many relationship between the grupo_usuarios table and permisos
CREATE TABLE `tiene_asignado` (
`id` int(11) PRIMARY KEY NOT NULL AUTOINCREMENT,
`id_grupo` int(11) NOT NULL,
`id_permisos` int(11) NOT NULL,
FOREIGN KEY(id_grupo) REFERENCES grupo_usuarios(id),
FOREIGN KEY(id_permisos) REFERENCES permisos(id),
ON DELETE CASCADE ON UPDATE CASCADE
);
?>
<?php include('php/verificarSesion.php');?>
<?php
//Storing the id of the user group received through GET from the groups.php page
if(isset($_GET['id_grupo_usuarios']) && (!empty($_GET['id_grupo_usuarios'])) && is_numeric($_GET['id_grupo_usuarios']) == 1){
$id_grupo_usuarios = $_GET['id_grupo_usuarios'];
}else{
echo "<script>
window.location.href='verGrupo.php';
</script>";}
?>
<?php include("plantillas/header.php"); ?>
<!-- PAGE CONTENT (permisosChex.php)-->
<section >
<div >
<div >
<div >
<h2>Assign permissions to the group <?php echo $id_grupo_usuarios;?></h2>
<p>Here you can check the permissions belonging to this group</p>
</div>
<div id="tabla">
<table id="tablaPacientes" style="width:100%">
<thead>
<tr>
<th>Permissions</th>
</tr>
</thead>
<tbody>
<!--START OF THE DATA SUBMISSION FORM-->
<form action="php/asignarPermisos.php" method="post" id="FormularioActualizarPaciente">
<input type="text" value="<?php echo $id_grupo_usuarios; ?>" name="id_grupo_usuarios">
<?php include("php/conexion.php")?>
<?php
//query to display all permissions
$sql = "SELECT * FROM permisos ORDER BY id DESC";
$resultado = $conexion->query($sql);
$listadoPermisos = array('data' => array());
if($resultado->num_rows > 0) {
//we will show a numbering in the table>>>>numbering: 1, 2, 3....
$numeracion = 0;
while($fila = $resultado->fetch_array()) {
$numeracion = $numeracion 1;
//the permit id is stored here
$id_permiso = $fila[0];
$nombre = $fila['nombre'];
In this second query I get the permissions assigned to a group of users
$consulta2 = "SELECT id_permisos FROM tiene_asignado where id_grupo = $id_grupo_usuarios";
$resultados = $conexion->query($consulta2);
while( $asignados = $resultados->fetch_array()){
//here I store the permissions of the selected checkboxes
$datos=array();
if (is_array($asignados) == true) {
//I assign a variable for each selected checkbox in the array
foreach($asignados as $asignado)
{
I relate the permissions selected from the checkbox with the id_permisos field of the table (tiene_asignado)
$datos[$asignados['id_permisos']] = true;
}
}else{//END OF is_array($asignados)
//this is just a test
echo "nothing";
}
?>
<tr>
<td>
<?php echo $numeracion;?>
<input title='create sheet of <?php echo $id_grupo_usuarios?>' type="checkbox" name="permisos[]" value="<?php echo $id_permiso;?>" <?php echo isset($datos[$fila[0]]) ? 'checked' : '';?> >
</tr
I can only see the permissions assigned to the group when I include the input checkbox inside the second WHILE if I don't do it like that, the problem is that the inputs are also repeated, that is, if a group has 3 permissions assigned, each one of the inputs are repeated three times but it shows me the 3 permissions assigned to that group
<?php } //END OF THE SECOND WHILE?>
<?php } //END OF THE FIRST WHILE?>
<?php
} //END OF IF?>
<?php ?>
<div >
<button type="submit"id="ActualizarPaciente" >Asignar Permisos</button>
</div>
</form>
<?php
?>
</div>
</div>
</div>
</tbody>
</table>
</div>
</div>
</div>
</section>
<!-- END OF PAGE CONTENT -->
<!-- Including the footer of the page -->
<?php include('plantillas/footer.php'); ?>
<!-- script js which contains the functionalities of the permission list page -->
<!--<script src="js/verpermisosChex.js"></script>-->
CodePudding user response：
You are selecting all records from permisos and for each record you select all records from tiene_asignado which are in the id_grupo that's matching a value. However, it is clear that your permisos table is logically related to tiene_asignado via the id_permisos field.
So, let's take a look at your second query: it is only loading id_permisos from tiene_asignado and it is safe to assume that your permisos table has an id, maybe called id_permisos, maybe having a different name and you have already loaded the id_permisos value using your first query.
As a result, your second query seems to be unnecessary, so, you will need to remove the second query and the second while (of course, you need the content of the second while, but it does not need to be a loop). If for some reason you need to load from tiene_asignado anyway, then you can modify your first query to be a join. However, if you need help with modifying your first query, you will need to provide information about your tables, at least their fields.
EDIT
based on subsequent information found out since this answer was originally written, I recommend the usage of the following query:
$sql = "SELECT * FROM permisos p LEFT JOIN tiene_asignado ta ON p.id = ta.id_permisos AND id_grupo='".$id_grupo_usuarios."' ";)