PHP / MYSQL Attach Multiple Tables

Question

I have never done such PHP / MYSQL tricks to join multi-headings. Please, who has experience in this area. Help: Fields from the TICKETS table:

ID TICKETID CUSTOMER 234 29 9798797 235 76 7887878 

Fields from the RECIPES table:

 ID DATENEW TOTAL 234 2012-12-03 22.57 235 2012-12-03 33.98 

Fields from the table PAYMENTS :

 RECEIPT PAYMENT 234 cash 235 debt 

Fields from the CLIENTS table:

 ID NAME 9798797 John 7887878 Helen 

The relationship between the tables is very easy to understand: TICKETS.CUSTOMER=CUSTOMERS.ID; PAYMENTS.RECEIPT=RECEIPTS.ID=TICKETS.ID

Final result I would like to achieve:

 TICKETID DATENEW NAME PAYMENT TOTAL 29 2012-12-03 John cash 22.57 76 2012-12-03 Helen debt 33.98 

I tried to do something similar, but this is not the case somewhere:

 $qry = mysql_query("Select TICKETS.TICKETID, RECEIPTS.DATENEW, PAYMENTS.TOTAL, CUSTOMERS.NAME, PAYMENTS.PAYMENT FROM PEOPLE, RECEIPTS INNER JOIN TICKETS ON RECEIPTS.ID = TICKETS.ID INNER JOIN CUSTOMERS ON TICKETS.CUSTOMER = CUSTOMERS.ID ORDER BY RECEIPTS.DATENEW"); 

Answer 1

To get the result, you can use the following:

 select t.ticketid, date_format(r.datenew, '%Y-%m-%d') datenew, c.name, p.payment, r.total from tickets t left join RECEPTS r on t.id = r.id left join CUSTOMERS c on t.customer = c.id left join payments p on t.id = p.RECEIPT and r.id = p.RECEIPT 

See SQL Fiddle with Demo

Result:

 | TICKETID | DATENEW | NAME | PAYMENT | TOTAL | --------------------------------------------------- | 29 | 2012-12-03 | John | cash | 22.57 | | 76 | 2012-12-03 | Helen | debt | 33.98 | 

Answer 2

This will give the result you want:

 SELECT p.RECEIPT AS TICKETID, r.DATENEW, c.NAME, p.PAYMENT, r.TOTAL FROM PAYMENTS p LEFT JOIN RECEIPTS r ON r.ID = p.RECEIPT LEFT JOIN TICKETS t ON t.ID = p.RECEIPT LEFT JOIN CUSTOMERS c ON c.ID = t.CUSTOMER ORDER BY r.DATENEW DESC