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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

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

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post 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?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Post by Mr Tech »

That array idea sounds good and probably the easiest. Why didn't I think of that?

Cheers mate
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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)
djot
Forum Contributor
Posts: 313
Joined: Wed Jan 14, 2004 10:21 am
Location: planet earth
Contact:

Post 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
-
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

SELECT * FROM `tableA`, `tableB`
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
Post Reply