I want to display the user's data and at the same time list the jobs for which they have applied. The first part is fulfilled, I can show the user's data, but I have a problem when I want to list the jobs where they have applied.
ERROR: Warning: Illegal string offset 'title' in
This is what I did so far:
Database
DB User
id | nombre
------------
1 | mario
DB Profile
idperfil | idusuaio | email
--------------------------------------
1 | 1 | [email protected]
Jobs
idempleo | titulo | categoria
-----------------------------------
1 | operario | metalurgica
2 | soldado | metalurgica
Applications
idpost | empleoid | usuarioid
------------------------------
1 | 1 | 1
2 | 2 | 1
PHP
<?php
class Sistema extends Db{
public function obtenerUsuario($id){
$sql = "SELECT * FROM usuario
INNER JOIN perfil ON perfil.idusuaio = usuario.id
INNER JOIN postulaciones ON postulaciones.usuarioid = usuario.id
INNER JOIN empleos ON empleo.idempleo = postulaciones.empleoid
WHERE id = ?";
$stmt = $this->connect()->prepare($sql);
$stmt->execute(array($id));
$resultado = $stmt->fetch(PDO::FETCH_ASSOC);
return $resultado;
}
}
?>
Index
<?php
$clase = new Sistema();
$id = 1;
$fila = $clase->obtenerUsuario($id);
echo 'Hola '.$fila['nombre'].', tu email es: '.$fila['email'];
// resultado: Hola mario, tu email es: [email protected]
// BIEN
// LISTAR EMPLEOS POSTULADOS
foreach($fila as $row) :
?>
<h5><?=$row['titulo'];?></h5>
// ERROR: Warning: Illegal string offset 'titulo' in
<?php
endforeach;
?>
I appreciate the help in advance!
The users table contains only one record that matches the searched ID and therefore will only return one application. What you need to do is read the applications table to get all the records for that user:
Now, to display the user data you take the first element:
What you need here is a single row for each user, and group the matches in the other related tables. This can be achieved very easily by combining
GROUP_CONCAT
withGROUP BY
.For example:
This query will bring you a row for each user with this structure:
As you can see, the titles are grouped in the row and separated by
|
(I have preferred this separator to the comma or others, because there may be titles with a comma). The query is thus optimized, because before you obtained rows withnombre
andemail
repeated....Reading is as simple as this:
There is no more story, in
$html
you will have this:And you will have an optimized code.