Wrong SQL syntax, cannot find it myself

Question

Possible Duplicate:
MySQL - when to use single quotes, double quotes, and backticks?

i have stared myself blind at this. And need some help to locate the error.

html:

        <b>Navn:</b> 
        <input type="text" name="navn" id="navn">

        </br>
        </br>

        <b>K&oslash;n:</b>
        <input type="radio" name="køn" value="mand" id="radio">Mand
        <input type="radio" name="køn" value="kvinde">Kvinde

        </br>
        </br>

        <b>Alder:</b> 
        <select name="alder" id="selecta">
        <option value="blank"></option>
        <option value="10til15">10 til 15 &aring;r</option>
        <option value="15til20">15 til 20 &aring;r</option>
        <option value="20til25">20 til 25 &aring;r</option>
        <option value="25til30">25 til 30 &aring;r</option>     
        </select>

        </br>
        </br>

        <b>V&aelig;gt:</b>  
        <select name="vægt" id="selectv">
        <option value="blank">Vægt</option> 
        <option value="30til40">30 til 40 kg</option>
        <option value="40til50">40 til 50 kg</option>
        <option value="50til60">50 til 60 kg</option>
        <option value="60til70">60 til 70 kg</option>       
        </select>

        </br>
        </br>

        <b>B&aelig;lte:</b>
        <select name="bælte" id="selectb">
        <option value="blank">vægt</option>
        <option value="hvid">Hvid</option>
        <option value="grøn">Gr&oslash;n</option>
        <option value="blå">Bl&aring;</option>
        <option value="sort">Sort</option>      
        </select>

php:

$navn = $_POST['navn'];
$køn = $_POST['køn'];
$alder = $_POST['alder'];
$vægt = $_POST['vægt'];
$bælte = $_POST['bælte'];

$con = mysql_connect("...", "...", "...") or die(mysql_error());
echo "Connected to MySQL<br />";

mysql_select_db("...", $con) or die(mysql_error());
echo "Connected to Database<br />";

mysql_query("INSERT INTO Tilmeldinger 
(column 2, column 3, column 4, column 5, column 6)
VALUES ($navn, $køn, $alder, $vægt, $bælte)") or die(mysql_error());

mysql_query("SELECT * FROM Tilmeldinger ORDER BY column 3, column 4, column 5, column 6") or die(mysql_error());

mysql_close;

echo "Tak for din tilmelding! Du bliver viderstillet om et kort øjeblik";

header("Refresh: 5; URL=http://www.casperwmn.dk/tilmelding.php");
exit; }

Im getting the output:

Connected to MySQL
Connected to Database
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 'column 2, column 3, column 4, column 5, column 6) VALUES (Casper, mand, 20til25,' at line 2

I have been over the valuable &vægt but i can't seem to locate what is wrong?

In advance, thanks!

What is the table structure? Are your columns really named `column 2`, `column 3`? — Taryn, Jan 01 '13 at 20:18
Do your columns really have spaces in them? If so you must backquote them `\`column 1`\`, — Michael Berkowski, Jan 01 '13 at 20:18
You can't have spaces in your column names. You need the actual column names not names like "column 2". Also the `mysql_*` functions are deprecated. Use http://php.net/PDO -- Edit: You can have spaces in column names but you need to surround them with backticks. Still you should name your columns descriptively. — Cfreak, Jan 01 '13 at 20:18
Im embarresed. Rookie mistake, it was indeed the column name that was wrong, thanks! — Evilunclebill, Jan 01 '13 at 20:23

score 1 · Answer 1 · edited Jan 01 '13 at 20:21

1

Instead of column 2 try `column 2`. Add backticks around the column names.

edited Jan 01 '13 at 20:21

johankj

1,765
16
34

answered Jan 01 '13 at 20:18

SQLMenace

132,095
25
206
225

Thanks couldn't figure out how to add the backticks in the formatter – SQLMenace Jan 01 '13 at 20:21

score 1 · Answer 2 · answered Jan 01 '13 at 20:18

1

Probably should be:

mysql_query("INSERT INTO Tilmeldinger 
(column2, column3, column4, column5, column6)
VALUES ($navn, $køn, $alder, $vægt, $bælte)") or die(mysql_error());

answered Jan 01 '13 at 20:18

BellevueBob

9,498
5
29
56

@AshBurlaczenko I removed the blank between "column" and the digit (like **column2** instead of **column 2**. Same basic answer as the other folks. – BellevueBob Jan 01 '13 at 20:57
I could see that, my point is that your answer should include an explanation of what you changed. Code only answer aren't always helpfully to beginners. – Ash Burlaczenko Jan 02 '13 at 09:15

score 1 · Accepted Answer · answered Jan 01 '13 at 20:19

1

In your SQL statements, the column names cannot have spaces unless you quote them. E.g:

INSERT INTO (`column 2`, `column 3`, ...) VALUES ( ... )

Likewise in the SELECT, the column names must be quoted in the ORDER BY clause since they contain spaces.

answered Jan 01 '13 at 20:19

Dave S.

6,349
31
33

Yeah, wrong column name, thanks :-) – Evilunclebill Jan 01 '13 at 20:23

score 0 · Answer 4 · answered Jan 01 '13 at 20:26

as Your error says

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 'column 2, column 3, column 4, column 5, column 6) VALUES (Casper, mand, 20til25,' at line 2

--> near 'column 2, column 3, column 4, column 5, column 6) VALUES (Casper, mand, 20til25,' at line 2

i would guess, that the problem is, that You didn't escape the column names into '-characters, like 'column n'

give it a try...

Wrong SQL syntax, cannot find it myself

4 Answers4