Searching related table
Posted: Thu Mar 01, 2007 1:01 pm
Hi everyone
I'm toying with the idea of converting our company database from FileMaker over to an intranet application with PHP and MySQL.
I've been doing PHP for a number of years but I'm a relative newcomer to MySQL.
I can create, edit and delete records, and I'm familiar with phpMyAdmin, but I'm not too great on the different joins and relationships and how to use them.
My database structure will roughly be like this...
One table storing information about companies - company name, address etc, with an ID auto-increment field.
Then I would have another table called 'classifications' which would hold a list of various company classifications.
Manufacturer, Press, Distributor, Web Design, Transport etc.
This table would have a Classification ID auto-increment field.
Then there would be a final table which stores the relationship between the 2.
This would have just 2 fields - Classification ID and Company ID.
And it would basically store the information about which companies are selected for which classifications.
Companies can be selected for multiple classifications.
My question though is this...
1) What would my MySQL query be to return a list of companies that are ticked for say Manufacturer - a classification ID of 1.
2) What would my query be to return a list of companies ticked for Manufacturer (ID: 1) and Press (ID: 2)
Thanks in advance
Ben
I'm toying with the idea of converting our company database from FileMaker over to an intranet application with PHP and MySQL.
I've been doing PHP for a number of years but I'm a relative newcomer to MySQL.
I can create, edit and delete records, and I'm familiar with phpMyAdmin, but I'm not too great on the different joins and relationships and how to use them.
My database structure will roughly be like this...
One table storing information about companies - company name, address etc, with an ID auto-increment field.
Then I would have another table called 'classifications' which would hold a list of various company classifications.
Manufacturer, Press, Distributor, Web Design, Transport etc.
This table would have a Classification ID auto-increment field.
Then there would be a final table which stores the relationship between the 2.
This would have just 2 fields - Classification ID and Company ID.
And it would basically store the information about which companies are selected for which classifications.
Companies can be selected for multiple classifications.
My question though is this...
1) What would my MySQL query be to return a list of companies that are ticked for say Manufacturer - a classification ID of 1.
2) What would my query be to return a list of companies ticked for Manufacturer (ID: 1) and Press (ID: 2)
Thanks in advance
Ben