convoluted query question

[eluser]fidlet[/eluser]
I'm new at Codeigniter and PHP, and this is a bit of a convoluted issue, so please bear with me. I'm trying to set up a notification system so that when a user completes a task, the owners of the next processes are notified via email. So I'm trying to build a model that, when a step # is passed to it, it will pass back a series of email addresses belonging to the owners of the next process. I have a series of related tables:

* process_flow: with the following fields: process_id, and notify_process (it identifies the next processes that are to be notified when a certain process is completed)
* process_assignments (shows what processes are assigned to what role. There can be more than one role associated with a process, and more than one process associated with a role)
* role_assignments (indicates what username is assigned what role. More than one user can be assigned to a role, and a user can have more than one role)
* users (holds user data including username and email address)

So, when the step is passed to the model, the model has to
1. pick out the row(s) in process_flow that match the step number, then
2. pick out the row(s) in process_assignments in which the process matches the notify_process fields of the rows picked out in (1), then
3. pick out the rows(s) in role_assignments in which the role matches the roles of the rows picked out in (2), then
4. pick out the row(s) in users in which the user matches the users of the rows picked out in (3), then
5. adds the email address of these users to a variable "$emails" that is then passed back to the controller

I feel WAY out of my element here. So far, here is what's in my controller:

Code:

$step = 1;
$this->load->model('admin/queue/MNotification');
$email = $this-> MNotification ->determineEmails($step);

And, here is what's in my model:

Code:

class MNotification extends CI_Model {

function determineEmails($step) {
  $email = "";
  
  $flow_query = $this->db->get_where('process_flow', array('process_id =' => '$step')); //returns all the rows where process id = step #
  if ($flow_query->num_rows() > 0)
  {
   foreach ($flow_query->result() as $row)
   {
     $data = $flow_query->row_array();  
     $next_query = $this->db->get_where('process_assignments', array('process =' => $data['notify_process'])); // returns all of the roles associated with the next step
     if ($next_query->num_rows() > 0)
     {
      foreach ($flow_query->result() as $row)
      {
       $data = $next_query->row_array();
       $user_query = $this->db->get_where('role_assignments', array('role =' => $data['role'])); // returns all of the users associated with the role
       if ($user_query->num_rows() > 0)
       {
        foreach ($user_query->result() as $row)
        {
         $data = $user_query->row_array();
         $email_query = $this->db->get_where('username', array('users =' => $data['username'])); // returns the row from the user table associated with the username
         $user_data = $email_query->row_array();
         $email = $email.";".$user_data['email'];
        
        }
       }
      
      }
     }    
   }
  }
  
  return $email;
  
}

}

Sufficed to say, it's not really working. I've been researching the documentation and the forums here to figure out how to construct this, but I don't know if I'm even on the right track. Can anyone give me some guidance?

[eluser]aquary[/eluser]
I think you could could merge all the 4 steps into 1 using joins...

Code:

select email from process_flow
join process_assignments on process=notify_process
join role_assignments on roles=role
join username on users=role_assignments.username
where step=$step

The query was not tested, and I'm not quite sure about the relationship between role_assignments and username, but shouldn't be far from this. Try running it in the mysql/phpmyadmin and check if the result is godd.

One point is, the query, which based on your code above, would return a good result if the code's logic is already good. If not, you might have to remove parts of the query, start from the last join, and check which part is wrong.

[eluser]fidlet[/eluser]
Okay! A modified version of that worked. The query I used:

Code:

select email from process_flow
join process_assignments on process=notify_process
join role_assignments on process_assignments.role=role_assignments.role
join users on role_assignments.username=users.username
where process_id=1 (I just used "1" here instead of the variable for simplification for now)

My results:

Code:

+---------------+
| email         |
+---------------+
| ****@me.com |
| ****@me.com |
| ****@me.com |
| ****@me.com |
| ****@me.com |
+---------------+
5 rows in set (1.54 sec)

So, the next step would be to incorporate this query into my model. Would I use something like this? (Taken from the CI user manual):

Code:

$query = $this->db->query("YOUR QUERY");

if ($query->num_rows() > 0)
{
   foreach ($query->result() as $row)
   {
      EMAIL CODE HERE
   }
}

[eluser]fidlet[/eluser]
I got this to work! Here is my resulting code, if anyone is interested:

Model:

Code:

function determineEmails($step) {
  $query = $this->db->query(
  'select email from process_flow
  join process_assignments on process=notify_process
  join role_assignments on process_assignments.role=role_assignments.role
  join users on role_assignments.username=users.username
  where process_id ='.$step
   );
  
  
  return $query;
}

Controller:

Code:

function accounting_process {
[...]


// Notification process
      
      // Determine who to notify
      $step = 1;
      $this->load->model('admin/queue/MNotification');
      $email = $this-> MNotification ->determineEmails($step);
      
      // Email notification messages
      $data['email'] = $email;
      $data['process'] = 1;
      $this->email_notification($data);
}

Code:

private function email_notification($data)
// This emails notifications to the owners of the next process step.
{
extract($data);

$this->load->library('email');

if ($process == 1) {
         $message = "New equipment has been added to My App";
} elseif ($process == 2) {
  $message = "Some message";
}
  else {
  $message = "Some message";
}
  
if ($email->num_rows() > 0)
{
    foreach ($email->result() as $row)
    {
     $this->email->from('*****@me.com', 'My App');  
     $this->email->subject('You have a new task in your queue');
  $this->email->to($row->email);
     $this->email->message($message.', and you have been assigned a new task. Login to your queue for more details.');
     $this->email->send();
     echo $this->email->print_debugger();
    }
}
}

Thank you for your help! You saved me hours of headache!