Unsolvable MySQL error?

Question

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 'repeat = 'week', location = 'Patowmack Farm', location_link = 'http://maps.googl' at line 1

I keep getting this message for both my update script (show above), and my insert script. I cannot find why it's doing this! Anyone available to help?

My update code:

foreach($_POST['enabled'] as $key => $value ) {

        $key = mysql_real_escape_string($key);

        if ($_POST['delete'][$key]=='1') {
            mysql_query("DELETE FROM upcoming WHERE id='$key'") or die(mysql_error());
        }
        else {
            $title = mysql_real_escape_string($_POST['title'][$key]);
            $date = mysql_real_escape_string(($_POST['date'][$key]));
            $repeat = mysql_real_escape_string($_POST['repeat'][$key]);
            $group = mysql_real_escape_string($_POST['group'][$key]);
            $group_link = mysql_real_escape_string($_POST['group_link'][$key]);
            $location = mysql_real_escape_string($_POST['location'][$key]);
            $location_link = mysql_real_escape_string($_POST['location_link'][$key]);
            $notes = mysql_real_escape_string($_POST['notes'][$key]);
            $enabled = mysql_real_escape_string($_POST['enabled'][$key]);

            mysql_query("UPDATE upcoming SET title = '$title', date = '$date', repeat = '$repeat', location = '$location', location_link = '$location_link', group = '$group', group_link = '$group_link', notes = '$notes', enabled = '$enabled' WHERE id = '$key' LIMIT 1") or die(mysql_error());
        }
    }

score 6 · Accepted Answer · answered Apr 19 '11 at 03:00

6

Have you tried changing the query to:

mysql_query("UPDATE `upcoming` SET `title` = '$title', `date` = '$date', `repeat` = '$repeat', `location` = '$location', `location_link` = '$location_link', `group` = '$group', `group_link` = '$group_link', `notes` = '$notes', `enabled` = '$enabled' WHERE `id` = '$key' LIMIT 1") or die(mysql_error());

Edit: And as others have stated; you are using reserved words. I recommend always using the ` symbol. (This can be found at the top left for most keyboards: under the escape key, above the tab key, to the left of the number 1 key.)

answered Apr 19 '11 at 03:00

Flipper

2,589
3
24
32

Thanks for the quick response! I would never of known that. Is it still safe to use those as column names? – sman591 Apr 19 '11 at 03:10
It is fine as long as you use the ` symbol. However, people would go both ways with their answer. Here is a related question: http://stackoverflow.com/questions/695578/creating-table-names-that-are-reserved-words-keywords-in-ms-sql-server I personally would say to try and avoid them because when you make up your own then it becomes much more specific and easier for you and others. – Flipper Apr 19 '11 at 03:42

score 3 · Answer 2 · answered Apr 19 '11 at 03:02

3

GROUP and REPEAT are reserved keywords in MySQL so you have to "escape" it with backticks:

`group` = '$group'

`repeat` = '...'

Also I'm making an assumption here, but you shouldn't wrap $key in quotes because it is an integer value. Also make sure you type cast it to an int by doing int($key).

answered Apr 19 '11 at 03:02

Mike Lewis

63,433
20
141
111

Let me +1 for more exact answer. – Devart Apr 19 '11 at 06:37

score 0 · Answer 3 · answered Apr 19 '11 at 03:01

0

repeat is a keyword in MySQL use back ticks repeat to use this.

answered Apr 19 '11 at 03:01

Rasika

1,980
13
19

score 0 · Answer 4 · answered Apr 19 '11 at 03:02

0

Repeat is a mySQL reserved word: http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Try surrounding your column names with backticks.

answered Apr 19 '11 at 03:02

MikeTheReader

4,200
3
24
38

Unsolvable MySQL error?

4 Answers4