Multiple SQL queries and FOREACH - Can be JOINED to one?

Question

I am trying to pull a list of Events, also seeing which members have paid for the Events. I then want to see if they are on the committee, to see if they have admin permissions.

I have successfully done this, using three SQL queries, then using three foreach loops to build the Array.

I am SURE this can be done with one SQL query and one foreach loop, however I have not yet mastered the JOIN technique.

I am using Expression Engine, Codeigniter Active Record, I will display to you the SQL output and also what my current EE functions look like.

THANKS FOR THE HELP! :D

SQL to select ALL events which are active

SELECT `id` as event_ID, `name` as event_name, `description` as event_description
FROM (`events`)
WHERE `events_category_id` =  '1'
AND `active` =  1
ORDER BY `name` asc

EE CODE to achieve this:

$query = $this->theDb->select('id as event_ID, name as event_name, description as event_description')
            ->order_by("name", "asc")
            ->get_where('events', array('event_category_id'=>$event_type,'active'=>1));

**

SQL to find what EVENT IDs the user has paid for

**

SELECT DISTINCT `products`.`event_ID` as joinedID
FROM (`transactions_items`)
JOIN `transactions` ON `transactions`.`id` = `transactions_items`.`id`
JOIN `products` ON `products`.`id` = `transactions_items`.`product_id`
JOIN `events` ON `events`.`id` = `products`.`event_ID`
WHERE `transactions`.`member_id` =  27500
AND `events`.`active` =  1
AND `event_category_id` =  '1'
ORDER BY `events`.`name` asc

EE CODE to achieve this

$query = $this->theDb->select('products.event_ID as joinedID')
        ->distinct()
        ->order_by("events.name", "asc")
        ->join('transactions', 'transactions.id = transactions_items.id')
        ->join('products', 'products.id = transactions_items.product_id')
        ->join('events', 'events.id = products.event_ID')
        ->get_where('transactions_items', array('transactions.member_id' => $memberID, 'events.active' => 1,'activity_category_id'=>$activity_type));

SQL to find ADMIN rights

SELECT `events`.`id` as event_ID, `admins`.`admin_role_id` as role_id, `admins_roles`.`name` as role_description
FROM (`admins`)
JOIN `admins_roles` ON `admins`.`admin_role_id` = `admins_roles`.`id`
JOIN `events` ON `events`.`id` = `admins`.`event_ID`
WHERE `admins`.`member_id` =  27500
AND `events`.`active` =  1

EE CODE to achieve this

$query = $this->theDb->select('events.id as event_ID, admins.admin_role_id as role_id, admins_roles.name as role_description')
        ->join('admins_roles', 'admins.admin_role_id = admins_roles.id')    
        ->join('events', 'events.id = admins.event_ID') 
        ->get_where('admins', array('admins.member_id' => $memberID, 'events.active' => 1));

FOR EACH LOOPS

// Create list of Events setting defaults
foreach($events_list as $row)
{
    $combinedEvents[$row->event_ID] = array(
        'eventID' => $row->event_ID,
        'eventName' => $row->event_name,
        'eventDescription' => $row->event_description,
        'isJoined' => 0,
        'roleID' => 0,
        'roleDescription' => "",
    );
}
// Add Committee roles
foreach($admin_list as $row)
{
    $combinedEvents[$row->event_ID]['roleID'] = $row->role_id;
    $combinedEvents[$row->event_ID]['roleDescription'] = $row->role_description;
}   
// Add Transactions
foreach($transaction_list as $row)
{
    $combinedEvents[$row->joinedID]['isJoined'] = 1;
}

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

I don't quite understand the FOREACH part because I've never touched PHP - but you should be able to solve the multiple SQL queires using the ;with clause. I have created an example in response to another question here and here. Is this what you're looking for?

edited May 23 '17 at 12:21

Community

1
1

answered Jul 17 '13 at 14:20

Scott Allen

513
2
13

Thanks, however I am more looking at the best way to 'JOIN' this query, so that it returns ONLY the information I need, within one query. The other problem is that this has to be done with EE's Active Record syntax – BigDistance Jul 18 '13 at 09:20

Multiple SQL queries and FOREACH - Can be JOINED to one?

SQL to select ALL events which are active

SQL to find what EVENT IDs the user has paid for

SQL to find ADMIN rights

FOR EACH LOOPS

1 Answers1