Multiple Database Queries

[eluser]joshvermaire[/eluser]
I'm doing the following queries and can't figure out how to get the second query to work. How do I get an array of just the values from my first query and use that in my second?

Code:

function application()
    {
        $user_id = $this->tank_auth->get_user_id();
        $this->db->select('app_id')->from('using')->where('user_id' , $user_id);
        $query = $this->db->get();

        $row = $query->result_array();

        if ($query->num_rows() > 0) :
        $this->db->select('app_name')->from('applications')->where('id' , $row['app_id']);
        $body['query'] = $this->db->get();
        endif;

        $this->load->view('apps_view', $body);

If I comment out the second query and

Code:

var_dump($row);

it gives me:
array(2) { [0]=> array(1) { ["app_id"]=> string(1) "2" } [1]=> array(1) { ["app_id"]=> string(1) "3" } }

I decided to do multiple queries instead of a join because I will be adding additional columns to select from the second query.

[eluser]Jaketoolson[/eluser]
Personally, I'd rather have a robust SQL query than excessive code with multiple database queries.

Maybe try setting $query to NULL after the first query?

Code:

$user_id = $this->tank_auth->get_user_id();
$this->db->select('app_id')->from('using')->where('user_id' , $user_id);
$query = $this->db->get();

$row = $query->result_array()
$rows = $query->num_rows();

$query = NULL;

if ($rows > 0) :
    $this->db->select('app_name')->from('applications')->where('id' , $row['app_id']);
    $body['query'] = $this->db->get();
endif;

$this->load->view('apps_view', $body);

[eluser]joshvermaire[/eluser]
Jaketoolson, thanks for getting back to me on this.

The problem is that in the second query,

Code:

$row['app_id']

gives me an error. If I did

Code:

$row[0]['app_id']

it would return a value, but then that only lets me query for one app_id where I want to query for all of them.

Maybe a join is better, like you said. Could you help me with the code?

Would I add the following to the first query?

Code:

$this->db->join('applications', 'applications.id = using.app_id');

Can I use method chaining to just select app_name from the join?

Thanks.

[eluser]Jaketoolson[/eluser]

Code:

$sql = "SELECT app_id a, app_name a FROM applications a
INNER JOIN using u ON u.app_id = a.id
WHERE u.user_id = ? ";

$query = $this->db->query($sql, array($user_id));
foreach ($query->result() as $row)
{
    $datas[] = $row->app_id;
    $datas[] = $row->app_name;
}


echo '<pre>';
print_r($datas);
echo '</pre>';

Also, the reason you had to add the [key] to $row in the beginning, is because $row was returning multiple arrays of results (2).
See ->

Code:

array {
    [0]=> array(1) {
        [“app_id”]=> string(1) “2”
    }
    [1]=> array(1) {
        [“app_id”]=> string(1) “3”
    }
}

So your original query returned more than one app_id which is why the 2nd query bombs

[eluser]Jaketoolson[/eluser]
Also if you expect to return more than one app_id, you can rewrite the 2nd query to use a 'WHERE IN' statement.

Code:

foreach ($query->result() as $row)
{
    $app_ids[] = $row->app_id;
}


if (count($app_ids) > 0) :
        $apps = implode(",", $app_ids);

        $this->db->select('app_name')->from('applications')->where_in('id' , $apps);
        $body['query'] = $this->db->get();
endif;

Which should read "SELECT app_name FROM `applications` WHERE id IN('1','2','3', etc).

[eluser]joshvermaire[/eluser]
When I use the 'WHERE IN' statement it only returns one value even though there is more than one app_id.
I did this query and it works well:

Code:

SELECT  `app_name`
FROM (
`applications`
)
WHERE  `id` =  '2'
OR  `id` =  '3'

If there are a hundred different app_id values that I'm looking for, I'm guessing there's a more efficient way to process the query than a hundred OR statements. I'm assuming that's where you want somehting like a 'WHERE IN' statement, but unfortunately it wasn't working.

Thoughts?

[eluser]Jaketoolson[/eluser]
WHERE x IN (1,2,3,4) should return the same results as WHERE x = 1 OR x = 2 OR x = 3 OR x = 4....
jaketoolson = msn
PSJakeT = aim
jaketoolson = yahoo

[eluser]Jaketoolson[/eluser]
FYI the following was occuring:

Example:

Code:

$values = array( 1,2,3);
$this->db->select('app_name')->from('applications')->where_in('id' , $values);

CI returns the query as written:
SELECT app_name FROM `applications` WHERE id IN('1,2,3');

Ok, we need to add apostrophes.

Code:

$values = implode(" ' , ' " ,$values);
$this->db->select('app_name')->from('applications')->where_in('id' , $values);

This then returns:
SELECT app_name FROM `applications` WHERE id IN ('1\', '2\', '3\')

Turns out this was reported as a bug, only to be rejected as not a bug but user error.

Same problem here.

And here with a weird workaround, assuming you wish the keys to be the 'searched' values.

Is there something I'm missing as it pertains to preparing the array to be used in the 'where_in()' Active record class?