MySQL: Selecting data from multiple tables in one query...
Moderator: General Moderators
MySQL: Selecting data from multiple tables in one query...
I have created a CRM in PHP & MySQL.
We have a clients and leads section which both allow you to schedule tasks for both. However, the scheduled tasks are in two different tables and I want to extract that info from both those tables using one query.
How do I do that?
Eventually I might have three or four tables to select from. is that also possible?
We have a clients and leads section which both allow you to schedule tasks for both. However, the scheduled tasks are in two different tables and I want to extract that info from both those tables using one query.
How do I do that?
Eventually I might have three or four tables to select from. is that also possible?
If you used multiple queries, and wanted all the data as a whole. You'd have to loop through each result set and store it an array. Then combine the multiple arrays into one large array. You could then sort this array any way you wanted to.Mr Tech wrote:If I were to use multiple queries, can you display them all in the same spot and order them by date?
I would want to order them by date... Would join work if the date field was the same in all the tables and all the rest of the fields were different?
By related, I meant a relational database. Such as a field user_id or similar could be used to link one table to all the other tables. You couldn't do that with a date field.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
-
That depends on how much data you will have. Joining tables should be the right way, because normalized databases cry for that.
Many things like get the data, store the data, sort the data, etc. could be done with just SQL - no need for further (complicated) PHP programming.
djot
-
That depends on how much data you will have. Joining tables should be the right way, because normalized databases cry for that.
Many things like get the data, store the data, sort the data, etc. could be done with just SQL - no need for further (complicated) PHP programming.
Nice, try that with a lot of data, then sort it, and later store the html output in $output ... Guess the default 16MB memory for PHP wouldn't suffice then.You'd have to loop through each result set and store it an array.
djot
-
Can you show us the table definition and explain the relation in more detail. An example might be helpful, too.Mr Tech wrote:We have a clients and leads section which both allow you to schedule tasks for both. However, the scheduled tasks are in two different tables and I want to extract that info from both those tables using one query
Code: Select all
SELECT * FROM `tableA`, `tableB`- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Lol, beat me to it.Jenk wrote:Code: Select all
SELECT * FROM `tableA`, `tableB`
Always keep the MySQL Manual open (it should have come with your installation) or run "help something," and chances are, you'll find what you're looking for. MySQL is very well document, IMO.