Problem with creating MySQL table 1

[gizmicek]

Hallo, I have following MySQL query:

CREATE TABLE thread_6 ('id' INT AUTO_INCREMENT PRIMARY KEY , 'user' VARCHAR(25) , 'time' VARCHAR(8) , 'date' VARCHAR(20) , 'text' LONGTEXT )

The thing is that it allways return FALSE when passed to mysql_query() function. I can't find out, what is wrong.

Any help will be appreciated

 

[KempCGDR]

should be

CREATE TABLE thread_6 ('id' INT AUTO_INCREMENT, 'user' VARCHAR(25) , 'time' VARCHAR(8) , 'date' VARCHAR(20) , 'text' LONGTEXT , PRIMARY KEY ('id') )

 

[DRJ478]

You need not single quote the names of the fields that you create.

You can catch errors like this in the future by appending error checking to the mysql_query command:

$result = mysql_query($SQL,$conn) OR die("Query failed: ".mysql_error());

 

[gizmicek]

Well, see my code:

    if(($action=="newthread")&&($threadname!="")) {  // vytvoreni noveho threadu ve foru
      $mysql=mysql_connect("","","");
      mysql_select_db("db",$mysql);
      mysql_query("INSERT INTO threads VALUES(0,'$threadname',0,0,1,3)",$mysql);
      $result=mysql_query("SELECT * FROM threads WHERE threadname='$threadname'",$mysql);
      $thid=mysql_result($result,0,0);
      $query="CREATE TABLE thread_".$thid." ('id' INT AUTO_INCREMENT, 'user' VARCHAR(25), 'time' VARCHAR(8), 'date' VARCHAR(20), 'text' LONGTEXT, PRIMARY KEY('id'))";
      mysql_query($query,$mysql) or die("Chyba pri vytvareni tabulky!!");

      mysql_close($mysql);
    }

The thing is that I get the error message "Chyba pri vytvareni tabulky!!" ('Error while creating the table' in english) everytime and the table is not created. I had several problems with creating table even using PHPMyAdmin so I have not very good experience with creating tables and I need it to make my forum work...

 

[gizmicek]

This message is given by MySQL:

Query failed: 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 ''id' INT AUTO_INCREMENT, 'user' VARCHAR(25), 'time' VARCHAR(8),

I don't know what it realy means since the error in syntax 'near' something can mean a lot of different things..

 

[danielhozac]

You shouldn't used quotes, rather you should use backticks (`).

//Daniel

 

[gizmicek]

Thanx very much, it now works perfectly. I will remember to use backticks next time

 

[DRJ478]

There is one more thing you should consider:

Your columns time and date are reserved words in MySQL.
You can use the backticks to escape them, but it is never a good idea to use reserved words as database, table, or column names.

You might also want to consolidate those two into one column that as datetime as data type.

 

[gizmicek]

>DRJ478
Well I know that MySQL contains data type DATETIME to store date and time values, but I don't use it since the only thing I need this values for is to show the date and time when the reply to the thread was written. I don't need to compare date and time values, so I didn't read anything about this data type in MySQL and the format in which MySQL stores the date&time values. I will force myself to read something about this as soon as I will need some date or time values comparison.

I will try to avoid using reserved words as database, table or column names in the future. Thank you