PHP One-to Many Query and display.

mrbnet

Hello this is kind of a basic question but I was having trouble finding the answer on the net. I have 2 tables in a mysql database one is a customer information table and one is a notes table that stores notes on each of the customers. I would like to know the proper way to query the 2 tables and display the customer information along with all of the notes that are associated with that customer.

Tables:

customers:
companyName
firstName
lastName
email
phone

notes:
note_id
customer_id
date
note

I currently have 2 select queries but would like to know how to do it in one. It looks like if I use a join it selects all the customer info for each note.

Thanks, Ryan

NogDog

Assuming you still want the customer data even if there are currently no notes associated with it, I would use a left join:

SELECT customers.*, notes.* FROM customers
LEFT JOIN notes ON notes.customer_id = customers.customer_id

Note: I'm assuming that there is a customer_id column in the customers table? If not, you'll need some kind of primary key field that can be stored in the notes table in order to establish the relationship between them.

mrbnet

Hello NateDog,

Thanks for responding so quick.

There is a customer_id in the customer table. When I did a left join it returned customer information along with every note. IS this the way that it is supposed to work? Is that more efficient then making 2 queries. How would I only loop through the notes and not all of the data?

Thanks,
Ryan

AsaSchool

To pull notes from only the customers who have columns in the notes table, use the customer table first. If you want to pull all the notes and the customer data that is associated with that data use the notes table first.

you can always limit the records by using your WHERE clause.

ie:
SELECT "specific data from the tables"
FROM customers LEFT JOIN notes USING(customer_id)
WHERE customer_id=$customerId

AsaSchool

when you put customers first, it will pull all the customers and the notes that relate to those customers if any.

NogDog

mrbnet wrote:
Hello NateDog,

Thanks for responding so quick.

There is a customer_id in the customer table. When I did a left join it returned customer information along with every note. IS this the way that it is supposed to work? Is that more efficient then making 2 queries. How would I only loop through the notes and not all of the data?

Thanks,
Ryan

Yes, that's how it's supposed to work. What you do with it in your code depends on how you want to output it. If you only want to get the data for only one customer, then just do two queries, one for each table. But if you want to get all the customers and their notes, it will probably be much more efficient to do a single query whose data you can then break down and organize as needed within the PHP code.