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

Question

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

bipen · Accepted Answer · 2013-01-24 08:15:27Z

2

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

answered Jan 24, 2013 at 8:15

bipen

36.6k9 gold badges51 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vizyourdata Over a year ago

Thanks bipen! I made these changes and am still receiving the same error.

zeddarn · Accepted Answer · 2013-01-24 08:08:18Z

1

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

answered Jan 24, 2013 at 8:08

zeddarn

3103 silver badges14 bronze badges

Comments

Naji Shmly · Accepted Answer · 2013-07-27 12:34:02Z

1

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

answered Jul 27, 2013 at 12:34

Naji Shmly

3111 gold badge4 silver badges10 bronze badges

Comments

user3417274 · Accepted Answer · 2014-03-13 20:23:07Z

0

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.

answered Mar 13, 2014 at 20:23

user3417274

1

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related