MySql syntax Error - Columns have numbers for name

Question

I have the below mysql statement:

INSERT INTO rates_try (`Weight`, `length`, `height`, `Min`, `100`, `200`, `300`) VALUES (1000,0.1,2.3,1,2,3,4);

Which gives the error:

No Row created. Check 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 'insertquery' at line 1INSERT INTO rates_try (Weight, length, height, Min, 100, 200, 300) VALUES (1500,2.31,3.5,1,0,0,0);

The table structure is:

rates_try ( `Weight` int(11), 
            `length` double, 
            `height` double, 
            `Min` double, 
            `100` double, 
            `200` double, 
            `300` double )

I am not sure what is wrong with the syntax and I know it most likely have something to do with the columns being numbers, if this is the issue causing it I will have to add something to the names so they are not just numbers, but I wanted to check that this was not the case first.

THe php code that creates the statement

$insertquery = "INSERT INTO rates_{$tablename} (`Weight`, `length`, `height`, `Min`";
                foreach ($titles as $title){
                    if (empty($title) ){

                    }else{
                        $insertquery .= ", `" . $title . "`";
                        $counter++;
                    }
                }
                $insertquery .=  ") VALUES (".$row['weight'].",".$row['cbm_min'].",".$row['cbm_max'].",".$value;
                $a = 0;
                while ($a < $counter){
                    $newvalue = $array[$a][$i];
                    if (empty($newvalue)){
                        $newvalue = 0;
                    }
                    $insertquery .= ",". $newvalue;
                    $a++;
                }
                $insertquery .= ");";

                echo $insertquery. "<br/>";
                $queryResult = mysqli_query($con, insertquery);

This error doesn't seem to be caused by this query. Look up `insertquery` in your code. Maybe some missing quote? — Mchl, Sep 05 '13 at 10:13
Don't think it's anything to do with the [numbers for column names](http://stackoverflow.com/questions/7975417/can-a-number-used-to-name-a-sql-column). Error says `insertquery` which I don't see in your statement, which makes me think that's not the same statement giving the error? — naththedeveloper, Sep 05 '13 at 10:14
Don't use numbers as column names. Apart from the technical problems they are also very bad from a documentation perspective. What does a column named `100` store? A boolean flag indicating that some other value equals to 100? Exclusively the value 100? Something completely different? — , Sep 05 '13 at 10:15

score 5 · Accepted Answer · answered Sep 05 '13 at 10:17

5

$queryResult = mysqli_query($con, insertquery);

Can you see it now?

(there's a missing $ in front of insertquery)

answered Sep 05 '13 at 10:17

Mchl

61,444
9
118
120

bingo thank you very much! should have included the php from the start but the error suggested the query to me – DevWithZachary Sep 05 '13 at 10:19
3

Hint: When the MySQL complains about a word, which is not in your query (it compained about `insertquery` in your case) then it is a clear indication something is wrong with your application code. – Mchl Sep 05 '13 at 10:22

score 0 · Answer 2 · answered Sep 05 '13 at 10:14

This is an interesting one, as you have actually followed the rules about using backticks to surround column names - and according to the document I just checked, your column names ARE indeed valid:

Identifiers may begin with a digit but unless quoted may not consist solely of digits.

On that note, while MySQL identifiers on Windows are not case sensetive, you will find that on Linux, they ARE. Are you sure that you haven't missed an upper or lower case somewhere? Your first column uses an upper case to start, but none of the others do.

I will however say that using column names that must be back-ticked is generally a BAD idea. Use something that doesn't need special care each and every time you refer to it.

I would concur with the advice given in the last paragraph. – Philip Sheard Sep 05 '13 at 10:16 — Philip Sheard, Sep 05 '13 at 10:16

MySql syntax Error - Columns have numbers for name

2 Answers2