1

I am trying to created created_at column but I am always getting the following error.

error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' created_at DATETIME NOT NULL' at line 2

What am I doing wrong? I tested the query without created_at and it works the table I created.

I appreciate any help.

$results = $con->query ( "SHOW TABLES LIKE'" . $route ."'"  ) or die ( mysqli_error () );

if (($results->num_rows) == 1) {
  echo "Table exist";
} else {
  $res = $con->query ( "CREATE TABLE " . $route . "(id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                    latitude FLOAT(10,6) NOT NULL, longitude FLOAT(10,6) NOT NULL), created_at DATETIME NOT NULL" ) or die($con->error);

  echo "table was craeted";
}
Jeff Sloyer
  • 4,899
  • 1
  • 24
  • 48
benz
  • 693
  • 1
  • 9
  • 29
  • 1
    Remove the `)` before `, created_at` ... – DCoder Apr 27 '15 at 15:37
  • I removed it and I am getting `corresponds to your MySQL server version for the right syntax to use near '' at line 2` – benz Apr 27 '15 at 15:42
  • add it back in, AFTER `created_at DATETIME NOT NULL` – pala_ Apr 27 '15 at 15:55
  • I've posted an answer below should you not have seen it. @benz – Funk Forty Niner Apr 27 '15 at 15:58
  • [you see, explaining where your code fails, helps to teach you in where your mistakes were made.](http://stackoverflow.com/a/29900499/1415724). - I don't say "try" in my answers, but pointed out "where" you've gone wrong ;-) I'll bet you didn't bother looking at mine neither. Next time, I'll just let it slide. – Funk Forty Niner Apr 27 '15 at 16:05
  • make up your mind when you choose an answer. You selected mine after then back to the other one. I even told the guy in comments if they tested it before posting; it's a thankless job lol! it's expected. – Funk Forty Niner Apr 27 '15 at 16:14

2 Answers2

0

You can try this way after removing extra ) after your longitude FLOAT(10,6) NOT NULL column. That extra ) causes you SQL syntax error.

if (($results->num_rows) == 1) {
    echo "Table exist";
 }else{
    $res = $con->query ( "CREATE TABLE $route
    (
     id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
     latitude FLOAT(10,6) NOT NULL, 
     longitude FLOAT(10,6) NOT NULL,
     created_at DATETIME NOT NULL)" 
    ) or die($con->error);
  echo "table was craeted";
}
A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103
  • did you put that into an IDE before posting? there's a bracket missing. – Funk Forty Niner Apr 27 '15 at 15:41
  • *"Try this way after removing extra ) after your longitude FLOAT(10,6) NOT NULL"* - all brackets are accounted for, the 2 added brackets either don't belong, or are misplaced. – Funk Forty Niner Apr 27 '15 at 15:45
  • @Being: I tired your syntax and I am getting `corresponds to your MySQL server version for the right syntax to use near '' at line 5` I have already deleted 'the double hat' but I am still getting the error at line 5? – benz Apr 27 '15 at 15:57
  • @benz you can set like this `DEFAULT CURRENT_TIMESTAMP` see more here http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column – A l w a y s S u n n y Apr 27 '15 at 16:26
0

You have a misplaced bracket following NULL in longitude FLOAT(10,6) NOT NULL) <=

 $res = $con->query ( "CREATE TABLE " . $route . "(id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        latitude FLOAT(10,6) NOT NULL, longitude FLOAT(10,6) NOT NULL), created_at DATETIME NOT NULL" ) or die($con->error);
                                                                     ^ right there.

Which belongs after NULL in created_at DATETIME NOT NULL

$res = $con->query ( "CREATE TABLE " . $route . " (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                latitude FLOAT(10,6) NOT NULL, longitude FLOAT(10,6) NOT NULL, created_at DATETIME NOT NULL)") or die($con->error);
  • My successful test constituted in using $route = "table_test_xxx";
Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141