sawy sawy - 1 month ago 6
MySQL Question

php get data from one to many tables

I have 2 tables A & B

Table A
ID NAME
1 John
2 Jack
3 Mark

Table B
ID phone UserID
s1 4586 1
s2 6996 1
s3 9654 2


they are one to many relation (John has 2 phone on Table 2)

my sql query

$sql = 'SELECT *
FROM
A
Join
B
ON
B.USER_ID = A.ID
WHERE
A.ID=:ID';


my PHP

foreach($vars['GROUPS'] as $row) {


<tr><th>Name</th><td><?=$row['Name']?></td></tr>
<tr><th>phone</th><td><?=$row['phone']?></td></tr>


}


I want to show the phones number for this user John name then show all his details from table 2 . as it now loop for me

Answer

You have 2 options:

  1. Use group_concat() function in sql to concatenate all telephone numbers a user has into a single string and use pretty much the same loop in php as you use now.

    select a.id, a.name, group_concat(b.phone) as phone from a inner join b on a.id=b.user_id group by a.id, a.name

  2. Leave your current sql query intact, but change the php loop displaying the table. In this case only print out a name with all the corresponding phone numbers after looping through all the records returned from the query. Just concatenate all phone numbers in the loop.

Comments