0
CREATE TABLE 'test'.'sensor' (

'id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
'time' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
'value' VARCHAR( 10 ) NOT NULL
 );

This is my code which I entered in phpMyAdmin. And when I pressed go I got the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''test'.'sensor' ( 'id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 'time' TIMES' at line 1

I have tried changing some statements but couldn't get the error.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Maryam
  • 41
  • 1
  • 2
  • 9

3 Answers3

1

MySQL uses backticks to escape identifiers, single and double quotes for strings.

In this case you should do:

CREATE TABLE `test`.`sensor` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`value` VARCHAR( 10 ) NOT NULL
 );

It's worth noting that backticks are only strictly required when your name conflicts with a reserved keyword and even then many are only relevant in a specific context. Terms like ORDER or SELECT always need to be escaped, so using them for columns or table names is best avoided. Likewise, TIME is also a column type, so you may want to pick a different name.

Additionally the TIMESTAMP column type is quite limited, values can only exist in the range of 1970 to 2038, so using it is not recommended. The DATETIME type by comparison has a range of years 1000 to 9999, more than adequate for most needs. There's a few other quirks of TIMESTAMP worth keeping in mind, too, like automatic UTC conversion.

tadman
  • 208,517
  • 23
  • 234
  • 262
0

Check that which version of php and MySQL u are using i have face this problem but solved after research . this query is working on MySQL 5.5 or newer and MariaDB 5.5 or newer

shakir ali
  • 1
-1
CREATE TABLE Ghee(
    country_code char(1) NOT NULL default '',
description varchar(10) NOT NULL default '',
PRIMARY KEY (country_code)
)
Dharman
  • 30,962
  • 25
  • 85
  • 135
shakir ali
  • 1
  • 1
    While this code may solve the question, [including an explanation](https://meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Brian61354270 Apr 11 '20 at 17:13
  • It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Apr 13 '20 at 22:18