[RESOLVED] query within a query

[Chris_96_WS6]

I have 1 table consisting of email addresses which are subscribed to mailing lists. Each email can be subscribed to one or both lists. Each subscriber also belongs to one one of two organizations.

I need a way to sort the data for the user by either variable so that the user can display:

All members

All members subscribing to list A regardless of organization
All members subscribing to list B regardless of organization

All members of group 1 subscribing to either list
All members of group 1 subscribing to list A
All members of group 1 subscribing to list B

All members of group 2 subscribing to either list
All members of group 2 subscribing to list A
All members of group 2 subscribing to list B

There has to be a simpler way to do this than coding 9 separate queries.

I have a form drop down which captures user choices of which org and which list they want to display results for...three choices for each (wildcard, choice 1, choice 2).

I just can't wrap my head around how to take those two POST variables and plug them into a query and create all the above options.

[bogu]

Can u show us your table structure?

[Chris_96_WS6]

Yes, it looks like this:

SubscrID...FirstName...LastName..Email........................Org..............List1...List2

[e-mail]...Company1......1........1

[e-mail].........Company2.....null......1

and so on....

The values in List 1 and List 2 are the results of a checkbox the user selects. Each field is either null or 1.

[bogu]

U need to create all 9 selection because of the different conditions, but u can use UNION to execute all in one query:

(SELECT COUNT(`SubscrID`) as all_members, 'all' as type FROM `table`)
UNION
(SELECT COUNT(`SubscrID`) as all_members, 'list1_all' as type FROM `table` WHERE `List1` IS NOT NULL)
UNION
(SELECT COUNT(`SubscrID`) as all_members, 'list2_all' as type FROM `table` WHERE `List2` IS NOT NULL)
UNION
(SELECT COUNT(`SubscrID`) as all_members, 'group1_all' as type FROM `table` WHERE `Org` LIKE 'Company1')
UNION
(SELECT COUNT(`SubscrID`) as all_members, 'group1_list1_all' as type FROM `table` WHERE `Org` LIKE 'Company1' AND `List1` IS NOT NULL)
/* and so on ... */

This is not tested but u should get the idea ...

[Chris_96_WS6]

Well, I'm not trying to just count the rows that meet those conditions, I need to pull and display the data in a table as well as output the emails formatted for cut & paste into an MS Outlook address bar.

I have all that stuff done, I just don't have an effective way to create the queries without doing 9 of them. That means if another email list is created later it would at exponentially more queries.

[Roger_Ramjet]

That is just the sort of thing that php is great at: dynamically generating queries.

foreach ($lists as $list) {
   $sql = "SELECT * FROM members m INNER JOIN subscriptions s ON (m.memberid=s.memberid) WHERE s.list=$list ";
}

You do the same for the other queries.

Now you can also do most of this just with more complex sql though how you write the query depends on the table schema so I can't write an example.

[Chris_96_WS6]

Roger Ramjet wrote:

That is just the sort of thing that php is great at: dynamically generating queries.

foreach ($lists as $list) {
   $sql = "SELECT * FROM members m INNER JOIN subscriptions s ON (m.memberid=s.memberid) WHERE s.list=$list ";
}

You do the same for the other queries.

Now you can also do most of this just with more complex sql though how you write the query depends on the table schema so I can't write an example.

Thanks, but that is almost "greek" to me. I have never used "foreach" or "inner join" Can you direct me to an online example that would demonstrate how to write a query like that?

[Roger_Ramjet]

If you have never used 'foreach' then you had better learn - it is one of the most common and useful constructs in php. What it means is

For Each Element in This Array -> Process It With This Code

You can process just the VALUES, foreach($array as $value) { ... }
or the array KEY and VALUE, foreach($array as $key=>$value) { ... }

RTFM

What I was telling you to do was to get all the 'lists' into an array and then iterate through them building the query. It does not matter how many lists there are, or how often you add a new list, you do not have to change the code.