Get data from 3 tables using sql join query based on a id (cdid)

Question

I'm tried to get results from Mysql 3 tables using sql join query. It's showing me error message when I'm running it. Like :

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in 
D:\software installed\xampp\htdocs\contact-management\getContactDetails.php on line 15

Well, I've 3 table. 1) company 2) contact_details 3) users.

Column on company Table (3):

cid, company_name, created_date

Column on contact_details Table (10):

cdid, family_name, given_name, work_phone, mobile_phone, email, email_private, 
user_id, cid, created_date.

Column on users Table ():

user_id, username, nickname

So what Im trying to do is.....I want to get all contact_details table info with company name, username and nickname from company and users table. So It's should only show one set of result.

So that My sql join query is look like this :

$id  = (int) $_POST['id']; // this is based id which coming from contact_details (cdid)

$innerjoing =  mysql_query("SELECT contact_details.*, company.company_name,   
users.nickname
FROM users
INNER JOIN (contact_details INNER JOIN company ON contact_details.cid =   
company.$id)
ON users.user_id = contact_details.user_id");

But bad luck I'm not getting what i want.

will you share your php code where you wrote mysql_fetch_array($innerjoing);? There may be an issue with variable name that you have passed to the function.. — Shaunak Shukla, Jun 30 '14 at 10:32

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

First of all, there is an error with your code at contact_details INNER JOIN company ON contact_details.cid = company.$id company.$id is wrong syntax for query.

Rajavel D 's query is correct as per your requirement.

You can also try :

$innerjoing =  mysql_query("SELECT contact_details . * , company.company_name, users.nickname
    FROM contact_details
    LEFT JOIN users ON users.user_id = contact_details.user_id
    LEFT JOIN company ON company.cid = contact_details.cid
    WHERE contact_details.cdid = ".$id)

If still you're not getting, Please check your variable what you've passed to the function. It should be mysql_fetch_array($innerjoing);

Refer this as well

edited May 23 '17 at 12:06

Community

1
1

answered Jun 30 '14 at 10:41

Shaunak Shukla

2,347
2
18
29

Most welcome, I have given too many options.. which one is perfect for you?? – Shaunak Shukla Jun 30 '14 at 14:25
Your sql query is just perfect for me now. – Shibbir Jun 30 '14 at 16:41

R D · Answer 2 · 2014-06-30T09:38:49.163

0

The way you have give the $id is wrong and query as well :

Try This :

("SELECT cd.*, c.company_name, u.nickname, u.username  
  FROM contact_details cd INNER JOIN user u 
       ON cd.user_id = u.user_id
          INNER JOIN company c on cd.cid=c.cid 
              where cd.cdid = " . $id )

edited Jun 30 '14 at 09:38

answered Jun 30 '14 at 09:12

R D

1,330
13
30

I'm trying with this. – Shibbir Jun 30 '14 at 09:17
tell me after trying, whether its working or any issue – R D Jun 30 '14 at 09:19
may be your query is wrong. Because this `$id` (cdid) is come from contact_details table. So how do you compare it with `cd.user_id = $id` ? – Shibbir Jun 30 '14 at 09:20
now you check i have changes cdid instead of user_id – R D Jun 30 '14 at 09:22
No, Not based on user id. I've to show details based on `cdid` which is come from `contact_details` table. This `cdid` is a primary key of this table. In `contact_details` table I've `cid`, `user_id` when I'm inserting data into this table. – Shibbir Jun 30 '14 at 09:27
now you check i have changes cdid instead of user_id – R D Jun 30 '14 at 09:27
Still showing same error message. – Shibbir Jun 30 '14 at 09:29
now you check i put the condition check in where – R D Jun 30 '14 at 09:39
You are trying hard. But unfortunately it's not working now. Getting same error message. – Shibbir Jun 30 '14 at 10:29

score 0 · Answer 3 · answered Jun 30 '14 at 09:36

0

Try this:

 $innerjoing =  mysql_query("
 SELECT cd.*, c.company_name, u.nickname
 FROM contact_details cd
 INNER JOIN user u on cd.user_id = u.user_id
 INNER JOIN company c on cd.cid = c.cid
 where c.cid = " + $id);

answered Jun 30 '14 at 09:36

Jay Kazama

3,167
2
19
25

in where it should cd.ccid instead of c.cid as per the Shibbir requirement – R D Jun 30 '14 at 09:50

score 0 · Answer 4 · edited Jun 30 '14 at 10:44

0

select cd.*,company_name,username,nickname 
from 
    contact_details as cd 
left join 
    company as c
on 
    cd.cid = c.cid
left join 
    users as u
on 
    cd.user_id = u.user_id `

edited Jun 30 '14 at 10:44

Cerbrus

70,800
18
132
147

answered Jun 30 '14 at 09:45

StackCoder

87
1
11

Get data from 3 tables using sql join query based on a id (cdid)

4 Answers4