Query running in phpmyadmin but not in codeigniter model

Question

I am running the following query :

SELECT @newNo := MAX( category_code ) FROM category_master;
INSERT INTO category_master VALUES (@newNo +1,  'Test')

The query runs flawlessly in phpmyadmin but it shows a database error when run using codeigniter :

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 'INSERT INTO category_master VALUES(@newNo+1, 'Test')' at line 2

what could be the reason ?? In codeigniter model i use the following code :

$query = 'SELECT @newNo := MAX(category_code) FROM category_master;
                    INSERT INTO category_master VALUES(@newNo+1, 
                    \''.$category_name.'\')';
$result = $this -> db -> query($query);

Yan Berk · Accepted Answer · 2012-08-13 10:12:36Z

4

You cannot run two queries at once. Seperate them:

$query = 'SELECT @newNo := MAX(category_code) FROM category_master';
$result = $this->db->query($query);

$query = 'INSERT INTO category_master VALUES(@newNo+1, \''.$category_name.'\')';
$result = $this->db->query($query);

EDIT: On your second query it is recommended to use query bindings:

$query = 'INSERT INTO category_master VALUES(@newNo+1, ?)';
$result = $this->db->query($query, $category_name);

edited Aug 13, 2012 at 10:12

answered Aug 13, 2012 at 10:06

Yan Berk

14.5k9 gold badges58 silver badges52 bronze badges

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

Comments

Yan Berk · Accepted Answer · 2012-08-13 12:49:55Z

0

Make sure the query does not contains any special characters. The browser will convert the special characters, so the query is running in phpmyadmin. To know the special characters, echo the query and copy it and paste in an editor like dreamweaver, it will show you the special characters. Hope this helps.

edited Aug 13, 2012 at 12:49

Yan Berk

14.5k9 gold badges58 silver badges52 bronze badges

answered Aug 13, 2012 at 10:12

user1594523

261 bronze badge

Collectives™ on Stack Overflow

Query running in phpmyadmin but not in codeigniter model

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related