CODEWITHSUNDEEP
phpmysqlcodeignitermulti-query
vizyourdata
vizyourdata

Reputation: 1444

Can I execute multiple queries inside CodeIgniter's $this->db->query()?

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);.

Upvotes: 2

Views: 5398

Answers (4)

user3417274
user3417274

Reputation: 1

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.

Upvotes: 0

Naji Shmly
Naji Shmly

Reputation: 311

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;
}

Upvotes: 1

bipen
bipen

Reputation: 36541

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();

Upvotes: 2

zeddarn
zeddarn

Reputation: 310

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

Upvotes: 1

Related Questions