Little help on Join if you have a moment?

[eluser]internut[/eluser]
I found the result # using:

$data['query']->num_rows;

Is that an ok to go about it?

[eluser]internut[/eluser]
actually based on that its only giving me the # its displaying on the page based on limit/offset. Not total # of records actually found.

thoughts anyone?

[eluser]Bogdan Tanase[/eluser]
LE: now I've read through the rest of the thread and I see you already tried the second approach

regarding the initial request in the post. If I understand the requirement correctly, wouldn't some standard SQL work?

Code:

SELECT * FROM `users` WHERE `id` IN
   (SELECT `user_id` FROM `groups_exp` WHERE `group_id`='$var')

or, I think a faster way

Code:

SELECT * FROM `users`
INNER JOIN groups_exp ON users.id=groups_exp.user_id
WHERE group_id='$var'

the second one can easily be translated to Active Record

[eluser]internut[/eluser]
I think you're right i should go with a standard query instead of going nuts with CI's options.

[eluser]internut[/eluser]
Ok I'm now trying for all results:

Code:

$data['query'] = $this->db->query("SELECT * FROM users WHERE status='$user_status' ORDER BY $sort $direction LIMIT $start,$config[per_page]");     // query time

Which works fine

Now I did the JOIN which I have working, its:

Code:

$data['query'] = $this->db->query("SELECT * FROM `users`  INNER JOIN groups_exp ON users.id=groups_exp.user_id WHERE group_id='$_POST[filter_group]'");     // query time

Which works as well.

My issue now is trying to add in the query above:

Code:

WHERE status='$user_status' ORDER BY $sort $direction LIMIT $start,$config[per_page]

I've tried:

Code:

$data['query'] = $this->db->query("SELECT * FROM `users`  INNER JOIN groups_exp ON users.id=groups_exp.user_id WHERE group_id='$_POST[filter_group] AND users.status='$user_status' ");

but get:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'active'' at line 1

Think i'm missing something small on how to do a WHERE and set the SORT / LIMITS on a JOIN query.

[eluser]internut[/eluser]
Further progress:

Code:

$data['query'] = $this->db->query("SELECT * FROM `users` JOIN groups_exp ON users.id=groups_exp.user_id WHERE group_id='$_POST[filter_group]' AND status='$user_status'  LIMIT $start,$config[per_page]");     // query time

Though I can not get the ORDER BY in to save my life.

[eluser]internut[/eluser]
This is driving me nuts. What a learning experience by the time I'm finished.

Code:

$data['query'] = $this->db->query("SELECT * FROM `users` INNER JOIN groups_exp ON groups_exp.user_id = users.id WHERE group_id='$_POST[filter_group]' AND status='$user_status' LIMIT $start,$config[per_page]");     // query time

Why is the "id" # (record number) coming back as the id # in the groups_exp table, and not the "users" table?

[eluser]internut[/eluser]
Ok now I have working perfectly ( i believe):

Code:

$data['query'] = $this->db->query("SELECT * FROM `users` WHERE `id` IN (SELECT `user_id` FROM `groups_exp` WHERE `group_id`='$_POST[filter_group]' AND status='$user_status') ORDER BY $sort $direction LIMIT $start,$config[per_page] ");

but this way I can not get the total # of results.

Code:

$data['search_count'] = $this->db->count_all_results('users');

comes back with all results in users. So I've gotten twice now what I want but can not get the actual record numbers results back.

[eluser]internut[/eluser]
No success yet except doing a double query which I just dont want to do. To get the total # of results I did a query without the LIMIT:

Code:

$data['query'] = $this->db->query("SELECT * FROM `users` WHERE `id` IN (SELECT `user_id` FROM `groups_exp` WHERE `group_id`='$_POST[filter_group]' AND status='$user_status') ORDER BY $sort $direction LIMIT $start,$config[per_page] ");     // query time

$data['total_query'] = $this->db->query("SELECT * FROM `users` WHERE `id` IN (SELECT `user_id` FROM `groups_exp` WHERE `group_id`='$_POST[filter_group]' AND status='$user_status')");     // query time

$data['search_count'] = $data[total_query]->num_rows;

Can not be the best way to do things. Do not want to double a query up.

[eluser]kgill[/eluser]
You're going to have to make two queries if you're using limit, you're asking the database to provide you with two entirely different things. One, X number of users (specified by your per page) and two, the total number your query would return if you didn't apply a limit. Although, for the second query you don't need to to re-run the entire thing again, a couple simple modifications will make your life easier.

Add this to your first query: SQL_CALC_FOUND_ROWS
So you've got: select SQL_CALC_FOUND_ROWS * from ...

For the second query, just replace it with a call the MySQL function: FOUND_ROWS()
So: select FOUND_ROWS();

If found_rows doesn't work then you'll have to use a select count(*), there's no need to do it in a two step process of rerunning the query and then doing a num_rows when you can grab it in one.

- K