I am writing a custom module with several database-tables. When writing content to a new table I get this error message for which I cannot detect what is wrong.
The message is:
user warning: 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 'order (oid, uid, aangetekend, verzekerd, orderdatum, bestelinfo) VALUES (29, 5, ' at line 1 query: INSERT INTO order (oid, uid, aangetekend, verzekerd, orderdatum, bestelinfo) VALUES (29, 5, 0, 0, 1259938004, '') in /var/www/html/includes/database.mysql.inc on line 174.
When I paste: INSERT INTO `order` (`oid`, `uid`, `aangetekend`, `verzekerd`, `orderdatum`, `bestelinfo`) VALUES (29, 5, 0, 0, 1259938004, '')
in the query-SQL-box of MySQL the record is inserted in the database.
What is wrong in my setup/code?
The code is:
db_query("INSERT INTO {order} (oid, uid, aangetekend, verzekerd, orderdatum, bestelinfo) VALUES (%d, %d, %d, %d, %d, '%s')", $ordernr, $user->uid, ($klant['aangetekend_order'] ? 1 : 0), ($edit['verzekerd'] ? 1 : 0), time(), $edit['extra']);
The database dump is:
-- phpMyAdmin SQL Dump
-- version 2.11.9.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generatie Tijd: 04 Dec 2009 om 15:50
-- Server versie: 5.0.77
-- PHP Versie: 5.1.6
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `test_1`
--
-- --------------------------------------------------------
--
-- Tabel structuur voor tabel `order`
--
CREATE TABLE `order` (
`oid` int(10) unsigned NOT NULL default '0',
`uid` int(10) unsigned NOT NULL default '0',
`aangetekend` tinyint(1) NOT NULL default '0',
`verzekerd` tinyint(1) NOT NULL default '0',
`verzendkosten` decimal(10,2) NOT NULL default '0.00',
`ontvangen` decimal(10,2) NOT NULL default '0.00',
`terugbetaald` decimal(10,2) NOT NULL default '0.00',
`orderdatum` int(11) NOT NULL default '0',
`ontvangstdatum` int(11) NOT NULL default '0',
`terugbetaaldatum` int(11) NOT NULL default '0',
`verzenddatum` int(11) NOT NULL default '0',
`terugontvangstdatum` int(11) NOT NULL default '0',
`bestelinfo` longtext NOT NULL,
PRIMARY KEY (`oid`),
KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Comments
'Order is a reserved keyword
'Order is a reserved keyword in mysql. You can use it, but you will need to use backticks around it, so you will write it like this: `{order}`.
But you should really change it as it's bad practice to use reserved words for table or column names for the exact reason that it causes the problem you are having now. Too easy to make mistakes.
Contact me to contract me for D7 -> D10/11 migrations.
Great to have access to
Great to have access to others with much more knowledge. I did rename the table into orders, for me a more logical name and more in line with other drupal table names.
Jay, thanks for your quick reply and good advise.