How to use $this->subquery->defaultDB() ?

[amyunus]

Pardon for my beginner in object oriented PHP Codeigniter. I am confused about using $this->subquery->defaultDB() ?

$db2 = $this->load->database('db2', TRUE);

$this->load->library('Subquery');
$this->subquery->defaultDB($db2)
$sub = $this->subquery->start_subquery('select');
$sub->select('number')->from('numbers')->where('numberID', 2);
$this->subquery->end_subquery('number');

$query = $db2->get('mytable');

but the subquery still use the default database not db2. Thank you.

[NTICompass]

That code looks correct, it should be working. defaultDB hasn't been tested much. When I get some time later today (or tomorrow), I'll take a look at it and see what's going on.

[NTICompass]

After you call $db2->get('mytable'), what does $db2->last_query() return?

[amyunus]

Based on my code above, here is the query

     SELECT (SELECT `number` FROM (`db2`.`db_numbers`) WHERE `numberID` = 2) AS `number` FROM `db`.`db_mytable`

I expect it to be

     SELECT (SELECT `number` FROM (`db2`.`db2_numbers`) WHERE `numberID` = 2) AS `number` FROM `db`.`db_mytable`

[NTICompass]

AH! I forgot about database prefixes. Might take a few days, but I'll get it fixed :-)

P.S. Don't you mean FROM db2.db2_mytable? :-P

[amyunus]

Oh you are right. Sorry for making you confused to my code.

Nevermind, for a while, I will try to add parameter defaultDB='' to this function.

    function start_subquery($statement, $join_type='', $join_on=1, $defaultDB = ''){
        $db = $this->CI->load->database($defaultDB, true);
        $this->dbStack[] = $db;
        $this->statement[] = $statement;
        if(strtolower($statement) == 'join'){
            $this->join_type[] = $join_type;
            $this->join_on[] = $join_on;
        }
        return $db;
    }

Thanks for giving time to response this issue and fixing the code. :-)

[NTICompass]

I knew what you meant, it's all good.

Your fix seems like it would work just fine. :-)

(If you want to fork my repo and send me a pull request, feel free)

Just remember to do:

$sub = $this->subquery->start_subquery('select', '', 1, 'db2');

I was thinking, I might make a setPrefix (or setDBConfig) method instead of having you pass that as a 4th parameter (or as well as). Which way do you prefer?

[amyunus]

Honestly I do not have idea how to tell it programmatically, but basically I prefer the prefix is configured automatically by retrieving prefix from database config (not configured manually by user when using the function). Anyway the defaultDB function is supposed to do that, isn't it?

[NTICompass]

I found that there is a function, that when called will give you the table name, correctly prefixed. If I use that, then it should work as you expect. Pass an object to defaultDB, and it will figure out the prefix for you.

I'll play with it in the next few days, expect an update soon. :-)

[NTICompass]

Ok, I think I got it working. I modified the code so that the defaultDB function will take care of the prefixes for you. Give it a try :-)

[amyunus]

Cool! It works! Thanks :-)