Page 1 of 1

MySQL: Selecting data from multiple tables in one query...

Posted: Wed May 30, 2007 10:17 pm
by Mr Tech
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?

Posted: Wed May 30, 2007 10:40 pm
by s.dot
Very possible using a join statement, if they are related by some field. However for performance, you might just want to go with multiple queries.

Posted: Wed May 30, 2007 10:49 pm
by Mr Tech
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?

Posted: Wed May 30, 2007 10:54 pm
by s.dot
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?
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.

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.

Posted: Thu May 31, 2007 12:32 am
by Mr Tech
That array idea sounds good and probably the easiest. Why didn't I think of that?

Cheers mate

Posted: Thu May 31, 2007 1:33 am
by John Cartwright
You forgot the bit about how to sort arrays, you'll likely need to use usort() to sorting by dates (unless your using timestamps)

Posted: Thu May 31, 2007 3:18 am
by 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.
You'd have to loop through each result set and store it an array.
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.

djot
-

Posted: Thu May 31, 2007 4:16 am
by volka
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
Can you show us the table definition and explain the relation in more detail. An example might be helpful, too.

Posted: Thu May 31, 2007 5:51 am
by Jenk

Code: Select all

SELECT * FROM `tableA`, `tableB`

Posted: Thu May 31, 2007 8:06 am
by superdezign
Jenk wrote:

Code: Select all

SELECT * FROM `tableA`, `tableB`
Lol, beat me to it.

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.