CodeIgniter Forums
Returning one data array from multiple MySQL queries - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Returning one data array from multiple MySQL queries (/showthread.php?tid=31364)

Returning one data array from multiple MySQL queries - El Forum - 06-16-2010

[eluser]Funky Larma[/eluser]
After watching most of the CodeIgniter posts over at NetTuts I decided to give it a go and try to create a project management application.
So far I've got two controllers; Projects and Tasks. Each have a model that retrieves data from a MySQL database and matching views to display the results.

The Project model has a primary key/ID column and then all the details including a status enum. The Tasks model also has a primary key/ID but also a project ID column that references the project ID.

From my Projects controller I get all the projects with a status of 'In Progress' and collect in a array. Normally I pass this array through to the view and use a foreach loop to display each Project on a page.
What I'm stuck on now is how to display a count of all tasks under a Project.

I can't seem to get it straight in my head how best to approach this and get it working.
Is it better practice to create a function in the model that will return the task count with the project details to the controller? Or should I loop through the project details in the controller and try to obtain the task count this way?

Any help or ideas greatly received!

Returning one data array from multiple MySQL queries - El Forum - 06-16-2010

[eluser]Ener1[/eluser]
You should check for "join" in the active record in the user guide

Cheers

Returning one data array from multiple MySQL queries - El Forum - 06-16-2010

[eluser]Burak Guzel[/eluser]
I will give a raw SQL example.

So you are doing something like this to get projects in progress:

Code:
SELECT * FROM projects WHERE status = 'in progress'

But you also want to include the number of tasks each of those projects have. Here is how you do it:

Code:
SELECT projects.*, COUNT(*) AS num_tasks FROM projects
LEFT JOIN tasks ON(projects.id = tasks.project_id)
WHERE projects.status = 'in progress'
GROUP BY projects.id

You will get the same array, but this time there will also be a 'num_tasks' value for each project.

Returning one data array from multiple MySQL queries - El Forum - 06-16-2010

[eluser]Ener1[/eluser]
Or using active record samething like this:

Code:
$this->db->select('projects.*');
$this->db->from('projects');
$this->db->join('tasks', 'projects.id = tasks.project_id','left');
$this->db->where('projects.status','in progress');
$this->db->groupby(projects.id);
$query = $this->db->get();
$quantity = $this->db->count_all_results();


Cheers

Returning one data array from multiple MySQL queries - El Forum - 06-17-2010

[eluser]Funky Larma[/eluser]
Thank you for your comments, will try these and see how I get on

Returning one data array from multiple MySQL queries - El Forum - 06-18-2010

[eluser]Funky Larma[/eluser]
Strangely a join is returning results that claim every project has one task. The task table only has one entry, so only one project should be returning this, rest as NULL I guess.

Returning one data array from multiple MySQL queries - El Forum - 06-18-2010

[eluser]Ener1[/eluser]
Try changing this line
$this->db->join('tasks', 'projects.id = tasks.project_id','left');
for this one
$this->db->join('tasks', 'projects.id = tasks.project_id');


Cheers

Returning one data array from multiple MySQL queries - El Forum - 06-19-2010

[eluser]suba[/eluser]
hi ener1,
small change in group by

Code:
$this->db->select('projects.*');
$this->db->from('projects');
$this->db->join('tasks', 'projects.id = tasks.project_id','left');
$this->db->where('projects.status','in progress');
$this->db->group_by(projects.id);// this is correct
$this->db->groupby(projects.id);// this is not correct.
$query = $this->db->get();
$quantity = $this->db->count_all_results();

Join query gives correct solution to your question.

Returning one data array from multiple MySQL queries - El Forum - 06-19-2010

[eluser]Ener1[/eluser]
Hey suba,

Talking about a syntax error thanks a lot!!! I wrote it on the fly my bad ;-)

cheers