CODEWITHSUNDEEP
phpsqlcodeigniter
user1011713
user1011713

Reputation: 279

Multiple/Sub queries with codeigniter

I just started with Codeigniter and this is driving me nuts. I have a query that determines whether a user has bought any programs. I then have to use that program's type category to run and determine how many times he or she has recorded a query in another table. Sorry for the confusion but the code hopefully makes sense.

I'm having problem returning the two arrays from my Model to my Controller to onto the view obviously. 

function specificPrograms() {
$specific_sql = $this->db->query("SELECT program,created FROM  `assessment`  WHERE uid = $this->uid");
if($specific_sql->num_rows() > 0) {
foreach ($specific_sql->result() as $specific) {        
$data[] = $specific;
$this->type = $specific->program;   
}
return $data;
}   

$sub_sql = $this->db->query("SELECT id FROM othertable WHERE user_id_fk = $this->uid and type = '$this->type'");
if($sub_sql->num_rows() > 0) {
foreach ($sub_sql->result() as $otherp) {       
$data[] = $otherp;
}
return $data;
}

}

Then in my Controller I have,

$data['specific'] = $this->user_model->specificPrograms();
$data['otherp'] = $this->user_model->specificPrograms();

Thanks for any help.

Upvotes: 0

Views: 485

Answers (1)

Chris Trahey
Chris Trahey

Reputation: 18290

Based on your comments above, I suggest one query which returns the results you need. The result set of this will look like this: 

+----------+------------+------+
| program  | created    | uses |
+----------+------------+------+
| football | 2001-01-01 | 12   |
+----------+------------+------+
| baseball | 2007-01-01 | 21   |
+----------+------------+------+


SELECT 
  assessment.program, 
  assessment.created, 
  count(othertable.user_id) as uses
FROM  assessment
JOIN othertable 
  ON othertable.user_id_fk = assessment.uid
  AND othertable.type = assessment.program
WHERE assessment.uid = $this->uid
GROUP BY assessment.program

... model ...

function specificPrograms() {
  $results = $this->db->query($sql_from_above);
  if($specific_sql->num_rows() > 0) {
  foreach ($specific_sql->result() as $program_data) {        
    $data[] = $program_data;
  }
  return $data;
}

... meanwhile in controller ...


$programs_used = $this->user_model->specificPrograms();
foreach($programs_used as $program_use_data) {
  // On first iteration, this is true:
  // $program_use_data['program'] == "football"
  // $program_use_date['uses'] == 12
}

Upvotes: 1

Related Questions