SQL CREATE TABLE fails with syntax error when using tinytext, longtext and enum fields

Other Questions MySQL
1

I’m getting a strange syntax error when trying to create a table in MySQL. Here’s my PHP code:

$query = "
    CREATE TABLE posts (
        postID int NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(postID),
        author tinytext,
        content longtext,
        category enum('Gaming','Sports','Technology','Music','Movies','misc')
    )
";
if(mysql_query($query, $connection)) {
    echo "Table 'posts' created successfully.<br />";
} else {
    echo "Table creation failed: " . mysql_error() . "<br />";
}

The error message I keep getting is:

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 'author tinytext, content longtext, category enum(Gaming,Sports,Technology,' at line 1

I tried changing the single quotes to double quotes in the enum values but that didn’t help either. What am I doing wrong with this table structure?

2

btw are you still using the old mysql_query functions? they’re deprecated and can cause weird issues. switch to mysqli or pdo - the old mysql functions don’t handle syntax errors properly.

3

Your PRIMARY KEY declaration is in the wrong spot. In MySQL, you need to put the PRIMARY KEY constraint after all your column definitions, not mixed in with them. Move it to the end and you’ll fix the syntax error:

CREATE TABLE posts (
    postID int NOT NULL AUTO_INCREMENT,
    author tinytext,
    content longtext,
    category enum('Gaming','Sports','Technology','Music','Movies','misc'),
    PRIMARY KEY(postID)
)

I ran into the same thing when I was starting out with MySQL. Moving it to the end fixed it right up.

4

You’ve got the PRIMARY KEY constraint in the wrong spot - it needs to go at the end after all your column definitions. Your enum values look fine, but those multiline strings in PHP can mess with MySQL parsing because of whitespace and line breaks. Try putting the whole query on one line instead. Also, tinytext is overkill for author names - just use VARCHAR(100). It’ll run faster.

5

Your PRIMARY KEY needs to go at the end after all your column definitions, not in the middle. That’s what’s causing the syntax error.

But honestly? Raw SQL with PHP gets old fast. I spent way too much time debugging syntax errors and wrestling with database quirks.

Now I just automate the whole thing with Latenode. You can set up workflows that connect to MySQL, create tables with proper syntax, handle data insertion, and even migrate schema changes without breaking anything.

Best part? You don’t have to memorize MySQL syntax rules or worry where to stick your PRIMARY KEY. The database nodes format everything correctly.

I built this for our CMS and haven’t touched raw SQL since. No more syntax headaches or manual queries.

Check it out: https://latenode.com

6

check if there’s a comma after ‘misc’ - that’ll throw an error. also, make sure you close the enum with a ) at the end. small typos cause big problems in sql.

7

I’ve encountered a similar issue before, and generally, the problem stems from how the PRIMARY KEY is declared. In MySQL, it’s often clearer and less error-prone to define the PRIMARY KEY directly within the column definition itself. This reduces potential syntax issues related to the order of constraints. Here’s how you can streamline your table creation:

CREATE TABLE posts (
    postID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    author tinytext,
    content longtext,
    category enum('Gaming','Sports','Technology','Music','Movies','misc')
)

This approach not only makes your code tidier but also can help prevent other common syntax errors that arise from misplacements.