CodeIgniter INSERT query with column called "index" gives database error 1064

Question

I can't figure out what's wrong here:

class Model_form extends CI_Model
{
    function __construct()
    {
        // Call the Model constructor
        parent::__construct();
    }

    function add_tree()
    {
        $v_treename = $this->input->post('f_treename');
        $v_treedesc = $this->input->post('f_treedesc');
        $v_treeid = $v_treename;
        $this->db->query("INSERT INTO trees (index, tree_name, tree_desc, tree_id) VALUES (NULL, '$v_treename', '$v_treedesc', '$v_treeid') "); //PROBLEM OCCURS HERE
    }

Get this error:

A Database Error Occurred

Error Number: 1064

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 'index, tree_name, tree_desc, tree_id) VALUES (NULL, 'TEST', 'TEST', 'TEST')' at line 1

I've used similar code on another project and it worked fine. Running on local server with MAMP.

cOle2 · Accepted Answer · 2011-05-27 15:51:51Z

5

Index is a reserved word in mysql. You need to put backticks around the column names ie `index`, `tree_name` etc.

answered May 27, 2011 at 15:51

cOle2

4,7741 gold badge26 silver badges27 bronze badges

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

Comments

Dormouse · Accepted Answer · 2011-05-27 15:51:28Z

2

Index is a protected word in mysql. Use `index` with backticks.

Protected words are those which must be escaped inside a query when referring to them as a field. A full list is available here

answered May 27, 2011 at 15:51

Dormouse

5,1781 gold badge28 silver badges42 bronze badges

Comments

Kristoffer Bohmann · Accepted Answer · 2011-05-27 16:03:32Z

1

This is a MySQL error and it happens because your 'trees' table is using a reserved word for the 'index' column.

You can add `index` backticks around the column name - or even better: change the 'index' column name to 'id' or similar.

For a list of reserved words in MySQL, please see: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

edited May 27, 2011 at 16:03

answered May 27, 2011 at 15:55

Kristoffer Bohmann

4,1243 gold badges31 silver badges35 bronze badges

Collectives™ on Stack Overflow

CodeIgniter INSERT query with column called "index" gives database error 1064

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related