i have these boards
worked
id(int) | titulo(varchar) | miembros(varchar)
----------------------------------------------
1 | Primera nota | 1,2
Username
id(int) | nombre(varchar)
--------------------------
1 | Pedro
2 | Lucas
form.php
<?php
try {
$conn = new PDO('mysql:host=localhost;dbname=pruebas', 'root', '');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e){
echo "ERROR: " . $e->getMessage();
}
$notas = $conn->query("SELECT * FROM trabajo INNER JOIN usuario ON usuario.id = trabajo.miembros");
while($row = $notas->fetch()){
?>
<p>
→ <?=$row['titulo'];?> - Miembros: <?=$row['nombre'];?>,
<!--
Respuesta: Primera nota - Miembros: Pedro
-->
</p>
<?php
}
?>
What I'm trying to do is list the jobs with their members, but as a result I only get the first id
, a single name and I don't know how to fix it.
Make sure you do two queries instead of one. Try it.
To link the two tables in MySql in this case you can use the
FIND_IN_SET(valor-buscado, lista-valores)
.FIND_IN_SET
is used to search for a value in a comma-separated list of values; if it finds the value it returns its position in the list, otherwise it will return 0:Output (with the example data from the question):
If you want to get a single row per job, you can use this query (grouping by title and concatenating the names of the users):
Output (with the example data from the question):
It is not advisable to store a list of IDs in a column as foreign keys, since it is a many-to-many relationship, you should use a third table to link that has work_id, user_id as columns, which will make queries much easier. Read about normalization in databases.