PHP MySQL comparing two tables and getting values

Question

I have two tables, one is called contacts and the other one is called numbers. One stores contact information and looks like this

contacts
-------------------------------------------------------
| id | fname | lname | email | address | uid | uniqid |
-------------------------------------------------------

My second table which stores phone numbers that belong to specific contact look like this

numbers
---------------------
| id | number | cid |
---------------------

The cid is the same as the uniqid on contact table, how can i get the contact row with its numbers which is on the second table through mysql?

Update Correction to the correct answer

SELECT id ,fname ,lname ,email ,address , uid, uniqid,number
FROM contacts a
inner join (SELECT cid, GROUP_CONCAT(DISTINCT number SEPARATOR ',') number FROM numbers) b ON b.cid=a.uniqid

It was missing DISTINCT

Show us sample data for both tables, and also the expected result. All as formatted text, not images! — jarlh
– jarlh, Commented Apr 2, 2019 at 8:15

Fahmi · Accepted Answer · 2019-04-02 08:03:46Z

2

use join

select id ,fname ,lname ,email ,address , uid, uniqid,number
from contacts a
inner join numbers b on b.cid=a.uniqid

answered Apr 2, 2019 at 8:03

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Areg Over a year ago

Hey, thanks for help, but unfortunately it creates another row if theres another number with the same uniqid

Ajan Balakumaran · Accepted Answer · 2019-04-02 08:35:21Z

1

You can use GROUP_CONCAT to get multiple numbers to one row and then when you imply the join you won't get duplicates.

select `id` ,`fname` ,`lname` ,`email` ,`address` , `uid`, `uniqid`,`number`
from `contacts` a
inner join (Select `cid`, GROUP_CONCAT(`number` seperator ',') `number` from `numbers`) b on b.cid=a.uniqid

edited Apr 2, 2019 at 8:35

answered Apr 2, 2019 at 8:23

Ajan Balakumaran

1,6491 gold badge11 silver badges17 bronze badges

2 Comments

Areg Over a year ago

Not working for some reason, i tried to capitalize the syntax. but nothing

Ajan Balakumaran Over a year ago

could you be able to try now, I think it's because number is a keyword so enclose it and execute.

lifeisbeautiful · Accepted Answer · 2019-04-02 08:07:07Z

1

You can map the two id's make sure you have this as table index, for faster retrieval of data.

SELECT id ,fname ,lname ,email ,address , uid, uniqid, number from contacts a, number b WHERE a.uniqid = b.cid;

answered Apr 2, 2019 at 8:07

lifeisbeautiful

8461 gold badge9 silver badges19 bronze badges

1 Comment

jarlh Over a year ago

Tip of today: Switch to modern, explicit JOIN syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed.

M.Hemant · Accepted Answer · 2019-04-02 08:07:36Z

1

Just use inner join with n.cid = c.uniqid

select c.id,c.fname,c.lname,c.email,c.address,c.uid,c.uniqid,n.number 
from contacts c 
inner join numbers n 
on n.cid = c.uniqid

answered Apr 2, 2019 at 8:07

M.Hemant

2,3551 gold badge11 silver badges14 bronze badges

Comments

Shadiqur · Accepted Answer · 2019-04-02 08:19:41Z

1

using join is the right choice here:

  SELECT con.*,num.* from contacts as con inner join numbers as num on con.uniqid = num.cid

answered Apr 2, 2019 at 8:19

Shadiqur

4921 gold badge5 silver badges21 bronze badges

Comments

mooga · Accepted Answer · 2019-04-02 10:25:18Z

1

Here we are using the concept of foreign key . Here cid is foreign key of contact table on number table. we have to match primary key of contact table with the foreign key of number table. if both are match then it's show the result.

Select a.id, a.fname, a.lname, a.email, a.address, 
a.uid, a.uniqid,b.number from contact a, number b where a.id=b.id;

edited Apr 2, 2019 at 10:25

mooga

3,3274 gold badges25 silver badges40 bronze badges

answered Apr 2, 2019 at 8:51

Yogesh Kolhe

112 bronze badges

Collectives™ on Stack Overflow

PHP MySQL comparing two tables and getting values

6 Answers 6

1 Comment

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related