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
– Mchl, Commented Sep 5, 2013 at 10:13
Don't think it's anything to do with the numbers for column names. Error says insertquery which I don't see in your statement, which makes me think that's not the same statement giving the error? — ɴᴀᴛᴇ ᴅᴇᴠ
– ɴᴀᴛᴇ ᴅᴇᴠ, Commented Sep 5, 2013 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? — user330315
– user330315, Commented Sep 5, 2013 at 10:15

Mchl · Accepted Answer · 2013-09-05 10:17:43Z

5

$queryResult = mysqli_query($con, insertquery);

Can you see it now?

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

answered Sep 5, 2013 at 10:17

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

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

2 Comments

DevWithZachary Over a year ago

bingo thank you very much! should have included the php from the start but the error suggested the query to me

Mchl Over a year ago

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.

Fluffeh · Accepted Answer · 2013-09-05 10:14:50Z

0

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.

answered Sep 5, 2013 at 10:14

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

1 Comment

Philip Sheard Over a year ago

I would concur with the advice given in the last paragraph.

Collectives™ on Stack Overflow

MySql syntax Error - Columns have numbers for name

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related