php not creating mysql column correctly

Question

I'm trying to dynamically create tables in MySQL, using CodeIgniter PHP framework:

if($colname != ''){
            $str = "CREATE TABLE IF NOT EXISTS $colname (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), site_id INT NOT NULL, name BLOB NOT NULL)ENGINE=INNOBD;";
            $query = $this->db->query($str);

When I execute that from the MySQL command prompt, it created the table perfectly. However, if it's done through CodeIgniter, I'm getting the following error:

<body>
    <div id="container">
        <h1>A Database Error Occurred</h1>
        <p>Error Number: 1064</p><p>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 ''tc 11' (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), site_id INT NOT NULL, ' at line 1</p><p>CREATE TABLE IF NOT EXISTS 'tc 11' (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), site_id INT NOT NULL, name BLOB NOT NULL)ENGINE=INNOBD;</p>

What's going on here? Thanks

try to print the value of `$colname` – codingbiz Jul 23 '12 at 22:47 — codingbiz, Jul 23 '12 at 22:47

score 2 · Accepted Answer · edited May 23 '17 at 12:03

You are using quotes ' and not backticks `` ` to surround your table name. MySQL uses backticks to escape names.

You should change the value of $colname from

'tc 11'

To this:

`tc 11`

You can do that with:

$colname = str_replace("'", "`", $colname);

Other resources that confirm this:

Blank spaces in column names with MySQL

(Sorry about the code blocks; SO has an issue with backticks inside inline code)

score 0 · Answer 2 · answered Jul 23 '12 at 22:47

0

that is because you are passing an incorrect value in $colname

First i assume $colname is supposed to be $tablename, because this is a query to create a table.

and it seems like there is a space in your table name, please show what value you have in your $colname variable.

there should be no space, TC_11

answered Jul 23 '12 at 22:47

Ibu

42,752
13
76
103

then I need a regular expression to replace ' ' by '_' if in the middle of the string – cybertextron Jul 23 '12 at 22:50
@philippe MySQL will accept it with a space if you escape it properly with backticks – Bailey Parker Jul 23 '12 at 22:50
@philippe are you trying to create a table or a column? – Ibu Jul 23 '12 at 22:52
@PhpMyCoder Do you know a regular Expression that replaces ' ' by '_' if it the middle of the program, and trims it if it is in the beginning ... – cybertextron Jul 23 '12 at 22:53
I'm trying to create a table to represent a column in a `JavaScript` table. – cybertextron Jul 23 '12 at 22:53
@philippe You can use `str_replace()`. You don't need a regex. But you shouldn't have to worry about that. If you surround your table name in backticks, you won't have to change the spaces to underscores. – Bailey Parker Jul 23 '12 at 22:55

score 0 · Answer 3 · answered Jul 23 '12 at 22:48

0

try:

"CREATE TABLE IF NOT EXISTS `".str_replace(' ','',$colname)."` (id...

answered Jul 23 '12 at 22:48

mgraph

15,238
4
41
75

score 0 · Answer 4 · answered Jul 23 '12 at 22:49

There is an error in your sql query. The following code should do the job and also make it safer.

if(!empty($colname))
{
    $sql = "CREATE TABLE IF NOT EXISTS ? (id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), site_id INT NOT NULL, name BLOB NOT NULL)ENGINE=INNOBD;";
    $query = $this->db->query($sql,array($colname));
}

php not creating mysql column correctly

4 Answers4