Mysql – How to echo data from two tables that are associated to one user in php

MySQLPHP

Say I have tbl_company with these

cid-------   cons_no--------   company_name  -------   phone ------   address 
1----------  q1----------------aple------------------------22222-------newyork
2 ---------- q2----------------samsung----------------11111----------Ohio
3 ---------- q3 -------------- nokia ---------------------333333--------LONDON 



tbl_company_track with these
id-------cid-------cons_no--------   branch_location  -------   date_opened 
1------- 1-----------q1---------------stamford------------------2010
2------- 1-----------q1---------------Kingsway------------------2011
3------- 1-----------q1---------------Cornerstone---------------2014
4------- 2-----------q2---------------Madison-------------------2001
5------- 3-----------q3---------------lewiston------------------2015

I want to be able to display on html 

Cons_no-----company_name------ phone branck_location------date_opened

I want both table to correspond in respect to the cid and cons_no

so I can have record for one company:

 Cons_no          company_name          phone    branch    date opened
    q1                Aple              22222    stamford    2010
                                                 kingsway    2011
                                                 Cornerstone 2014

I HOPE I WAS ABLE TO COMMUNICATE MY PROBLEM WELL ENOUGH?

Best Answer

To answer this, I did the following (you can check out the SQLFiddle here).

CREATE TABLE Company
(
  cid INT,
  cons_no VARCHAR(2),
  company_name VARCHAR(20),
  phone VARCHAR(15),
  address VARCHAR(20)
);

INSERT INTO Company 
VALUES 
(1, 'q1', 'Apple', '222222', 'New York'), 
(2, 'q2', 'Samsung', '11111', 'Ohio'), 
(3, 'q3', 'Nokia', '333333', 'London');



CREATE TABLE Company_Track
(
  id INT,
  cid INT,
  cons_no VARCHAR(2),
  branch_location VARCHAR(20),
  date_opened INT
);

INSERT INTO Company_Track 
VALUES
(1, 1, 'q1', 'Stamford', 2010),
(2, 1, 'q1', 'Kingsway', 2011),
(3, 1, 'q1', 'Cornerstone', 2014),
(4, 2, 'q2', 'Madison', 2001),
(5, 3, 'q3', 'Lewiston', 2015);

And then ran the query

SELECT  c.company_name, c.cons_no, t.branch_location
    FROM  Company c
    INNER JOIN  Company_Track t ON c.cid = t.cid;

Which gives the result you want

company_name    cons_no branch_location
Apple           q1      Stamford
Apple           q1      Kingsway
Apple           q1      Cornerstone
Samsung         q2      Madison
Nokia           q3      Lewiston
Related Question