Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Austin Klenk
Austin Klenk
4,399 Points
Posted by Austin Klenk
Austin Klenk
4,399 Points

select from 3 tables with all types of various information

I have 1 database name "mtrv" and it has 3 tables- "customer_info", "customer_rv","work_orders". The customer info table has their name, username password and ext. the customer_rv table has the manufacturer, model_name and the same with the work_orders but the work orders table has 18 columns.

I need help figuring out the mysql select statement so i can query the customer rv information into a web page. as if they are logged into their account.

2 Answers

Jeremy Germenis
Jeremy Germenis
29,854 Points
Jeremy Germenis
Jeremy Germenis
29,854 Points
  • Add a column to your customer_info table of 'id' and set it auto increment.
  • Add a column to your customer_rv table of 'customer_id'.
  • Record the customer customer_info.id into the customer_rv.customer_id
  • When a user logs in capture there customer_info.id as a cookie or session variable
  • Query the customer_rv table where customer_rv.customer_id equals the cookie or session variable

NOTE: You could make an in between table recording an id from both tables and do a join but this seems like overkill for the project. If you need information from all three tables at the same time you will need to do a join. Repeat the customer_rv steps to the other table.

Austin Klenk
Austin Klenk
4,399 Points
Austin Klenk
Austin Klenk
4,399 Points

Another thing is how should i write the sql statement in php to get the user details.?

Jeremy Germenis
Jeremy Germenis
29,854 Points
Jeremy Germenis
Jeremy Germenis
29,854 Points
  • $id = $your_session_or_cookie_id
  • SELECT * FROM customer_rv WHERE customer_id = $id
  • JOIN METHOD
  • SELECT * FROM customer_rv LEFT JOIN customer_info ON customer_rv.customer_id = customer_info.id WHERE customer_rv.customer_id = $id