get data from query and use it in the next query

[eluser]mvn1990[/eluser]
hi, I've got an issue with my query's. first i wanne get id's and to do that i use this query:

$email = $this->session->userdata('email');

$data['id_project'] = $this->db->select('id_project')
->from('tbl_invited')
->join('tbl_user', 'tbl_user.email = tbl_invited.email')
->where('email', $email);

then i wanne use the id's (some times more than one) to get data by using an other query for example:

$data['id_project'] = $this->db->select('name')
->from('tbl_project')
->where('id_project', 'the id's I got from my first query' );

thanks in advance

[eluser]LinkFox[/eluser]
[quote author="mvn1990" date="1305342621"]hi, I've got an issue with my query's. first i wanne get id's and to do that i use this query:

$email = $this->session->userdata('email');

$data['id_project'] = $this->db->select('id_project')
->from('tbl_invited')
->join('tbl_user', 'tbl_user.email = tbl_invited.email')
->where('email', $email);

then i wanne use the id's (some times more than one) to get data by using an other query for example:

$data['id_project'] = $this->db->select('name')
->from('tbl_project')
->where('id_project', 'the id's I got from my first query' );

thanks in advance[/quote]

Hi,

You'll need to loop through the results and put them into an array then use the where_in function.

Code:

$this->db->select('id_project')
$this->db->join('tbl_user', 'tbl_user.email = tbl_invited.email')
$this->db->where('email', $email);

$result = $this->db->get('tbl_invited');

// Then loop through the results adding to an array

$projIds = array();

foreach($result as $row) {
     array_push($projIds, $row->id_project);
}

// Then finally use the where_in function in your next query


$this->db->select('name');
$this->db->where_in('id_project',$projIds);
$data['id_project'] = $this->db->get('tbl_project');

Although I would guess you could use a join to perform what you need in one query in most cases.

[eluser]pickupman[/eluser]
That's one of the things that needs to be added to the active record syntax, is [url="http://ellislab.com/forums/viewthread/126017/"]subqueries[/url]. In regular sql syntax you can make a where statement be another query. By using the code posted in the link, you can still use the active record syntax and build a subquery.

In any application, the database is often the slowest, so you want to access it as few as times as possible. Rather than two queries, you will have one which will be faster.

Code:

$this->db->select('id_project')
$this->db->join('tbl_user', 'tbl_user.email = tbl_invited.email')
$this->db->where('email', $email);

$subquery = $this->db->_compile_select();
$this->db->_reset_select();
$subquery = str_replace("\n"," ",$subquery);

$this->db->select('name');
$this->db->where_in('id_project', $subquery, false);
$data['id_project'] = $this->db->get('tbl_project');

The downside that the database class cannot be extended yet, so this requires editing a system file.

[eluser]mvn1990[/eluser]
i still have problems with my first query

Code:

$this->db->select('id_project');
$this->db->join('tbl_user', 'tbl_user.email = tbl_invited.email');
$this->db->where('tbl_invited.email', $email);

$result = $this->db->get('tbl_invited');

$projIds = array();

foreach($result as $row) {
     array_push($projIds, $row->id_project);
}

I get an error:

Trying to get property of non-object

and it's because there is no id_project id in the array... how do i solve this problem

[eluser]pickupman[/eluser]
Should be:

Code:

$this->db->select('id_project');
$this->db->join('tbl_user', 'tbl_user.email = tbl_invited.email');
$this->db->where('tbl_invited.email', $email);

$result = $this->db->get('tbl_invited')->result(); //return result object instead of query object

$projIds = array();
if(count($result) > 0){
  foreach($result as $row) {
       array_push($projIds, $row->id_project);
  }
}