Active records mixing querries

[eluser]Sein Kraft[/eluser]
I'm needeing to get all the child categories from the given to use in a where_in with active records of codeigniter.

The problem is that the second query get mixed with the main one breaking it completely.

Main Query

Code:

$this->db->select('artworks.*, users.id as owner, users.name as user_name');
$this->db->from('artworks');
$this->db->join('users', 'users.id = artworks.user_id');

$category = $this->get_child_categories($this->get_categories(), $matches[1]);
$this->db->where_in('artworks.category', $this->category['child']);

$this->db->group_by('artworks.id');
$query = $this->db->get();
return $query->result_array();

Second Query: get_categories();

Code:

$this->db->select('*');
$this->db->order_by('parent', 'asc');
$this->db->order_by('name', 'asc');
$query = $this->db->get('categories');
return $query->result_array();

get_child_categories

Code:

function get_child_categories($categories, $parent){
    foreach($categories as $category){
        if($category['parent'] == $parent){
            array_push($this->category['childs'], $category['id']);
            $this->get_child_categories($categories, $category['id']);
        }
    }
}

But i'm getting this error where clearly displays that the second query is quetting inside the main one.

Error Number: 1064

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 '* FROM (`artworks`, `categories`) JOIN `users` ON `users`.`id` = `artworks`.`use' at line 1

SELECT `artworks`.*, `users`.`id` as user_id, `users`.`name` as user_name, * FROM (`artworks`, `categories`) JOIN `users` ON `users`.`id` = `artworks`.`user_id` WHERE `artworks`.`rating` IN ('g', 'm', 'a') ORDER BY `artworks`.`id` desc, `parent` asc, `name` asc

Filename: D:\Server\htdocs\gallery\system\database\DB_driver.php

Line Number: 330

[eluser]Mikhail Menshinskiy[/eluser]
Yes, Active records mixing your queries and it's normally for your code.

Look, you used an one object for SQL query - $this->db .

So, here you start a FIRST sql query:

Code:

$this->db->select('artworks.*, users.id as owner, users.name as user_name');
$this->db->from('artworks');
$this->db->join('users', 'users.id = artworks.user_id');

Then, you call get_categories():

Code:

$category = $this->get_child_categories($this->get_categories(), $matches[1]);

which used a $this->db object also and set a select and order_by operators for the FIRST sql query. Result: you start run a sql query with following statements:

Code:

$this->db->select('artworks.*, users.id as owner, users.name as user_name');
$this->db->from('artworks');
$this->db->join('users', 'users.id = artworks.user_id');
$this->db->select('*');
$this->db->order_by('parent', 'asc');
$this->db->order_by('name', 'asc');
$this->db->get('categories');

And after that you try to continue a FIRST sql query, BUT this query has been already started and following code starts a SECOND sql query:

Code:

$this->db->where_in('artworks.category', $this->category['child']);
$this->db->group_by('artworks.id');
$query = $this->db->get();

To resolve your problem try to make this line the first:

Code:

$category = $this->get_child_categories($this->get_categories(), $matches[1]);

So, your code should be:

Code:

$category = $this->get_child_categories($this->get_categories(), $matches[1]);

$this->db->select('artworks.*, users.id as owner, users.name as user_name');
$this->db->from('artworks');
$this->db->join('users', 'users.id = artworks.user_id');
$this->db->where_in('artworks.category', $this->category['child']);
$this->db->group_by('artworks.id');
$query = $this->db->get();
return $query->result_array();

Sorry for my English.

[eluser]Sein Kraft[/eluser]
Lol just a friend answered me this 9 minutes ago http://stackoverflow.com/questions/60096...ve-records

Anyways...thank you so much! I didnt noticed this until he..and you, pointed this out.