2
votes

I followed the CI user guide here http://ellislab.com/codeigniter/user-guide/database/queries.html and am still having trouble getting this query to work. I can paste this exact code without the '' into workbench and it runs fine so I'm not sure where I have the CI syntax wrong. Here it is:

    public function test(){
        ini_set('memory_limit','-1')
        $my_sql = $this->db->query( 

            'DROP TABLE temp1;
            DROP TABLE temp2;
            CREATE TEMPORARY TABLE temp1 (id varchar(150),user varchar(150),item varchar(150),city varchar(150));
            INSERT INTO  temp1 SELECT id, user, item, city FROM add WHERE user = 1;
            CREATE TEMPORARY TABLE temp2 (id varchar(50),vendor varchar(50)) ;
            INSERT INTO temp2 SELECT id, vendor FROM selection;
            SELECT a.user, a.id, a.item, a.city, b.vendor
            FROM temp1 a 
            RIGHT JOIN temp2 b ON a.id=b.id;'
        );

        $query = $this->db->get($my_sql);
        return $query->result_array();

    }

My error is 1064 error in SQL syntax. When I run it locally it references the DB_driver.php file line 330. When run in the cloud it references this line $query = $this->db->get($my_sql);. Thanks for the help!

4

4 Answers

2
votes

no need to get() since you have already called query()..

get Runs the selection query and returns the result. Can be used by itself to retrieve all records from a table

try this

 $my_sql = $this->db->query( 

        'DROP TABLE temp1;
        DROP TABLE temp2;
        CREATE TEMPORARY TABLE temp1 (id varchar(150),user varchar(150),item varchar(150),city varchar(150));
        INSERT INTO  temp1 SELECT id, user, item, city FROM add WHERE user = 1;
        CREATE TEMPORARY TABLE temp2 (id varchar(50),vendor varchar(50)) ;
        INSERT INTO temp2 SELECT id, vendor FROM selection;
        SELECT a.user, a.id, a.item, a.city, b.vendor
        FROM temp1 a 
        RIGHT JOIN temp2 b ON a.id=b.id;'
    );

    return $my_sql->result_array();
1
votes

Please use Database Forge Class for DCL and DDL Operation found here http://ellislab.com/codeigniter/user-guide/database/forge.html.

1
votes

you need to return the result directly from $my_sql, just use this: return $my_sql->result_array(); or this for more :

if ($my_sql-> num_rows() > 0) {
    $result = $my_sql-> result();
    return $result;
} else {
    return FALSE;
}
0
votes

The problem is that CI uses the mysql PHP API by default, and running ->query ultimately runs the mysql_query function, which does not allow multiple SQL statements (this is for security reasons).
Looks like the mysqli API has an multi_query function with will allow multiple query execution. Your other option is to split your string containing multiple queries up into sub queries using a REGEX.