MySQL query error (using php)

Question

I have problem with this query.

 $query = 'CREATE TABLE movie(
    movie_id UNSIGNED INTEGER NOT NULL AUTO_INCREMENT,
    movie_name VARCHAR(255) NOT NULL,
    movie_type TINYINT NOT NULL DEFAULT 0,
    movie_year SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    movie_leadactor INTEGER UNSIGNED NOT NULL DEFAULT 0,
    movie_director INTEGER UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (movie_id),
    KEY movie_type (movie_type, movie_year)
    )
    ENGINE=MyISAM';
 $result = mysql_query($query, $db) or die(mysql_error($db));

it still write this:

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 'UNSIGNED INTEGER NOT NULL AUTO_INCREMENT, movie_name VARCHAR(255) NOT NULL, ' at line 2

score 3 · Accepted Answer · answered Nov 19 '12 at 20:11

Unsigned must come after int. I rewrote your query and verified that it does work now.

CREATE TABLE movie(
movie_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
movie_name VARCHAR(255) NOT NULL,
movie_type TINYINT NOT NULL DEFAULT 0,
movie_year SMALLINT UNSIGNED NOT NULL DEFAULT 0,
movie_leadactor INT UNSIGNED NOT NULL DEFAULT 0,
movie_director INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (movie_id),
KEY movie_type (movie_type, movie_year)
)
ENGINE=MyISAM

score 0 · Answer 2 · answered Nov 19 '12 at 20:09

0

MySQL's integer type is called INT, not INTEGER.

answered Nov 19 '12 at 20:09

ceejayoz

176,543
40
303
368

score 0 · Answer 3 · answered Nov 19 '12 at 20:12

Try this, your INTEGER can be written as INT and you put the UNSIGNED variable before it, it needs to go after -

$query = 'CREATE TABLE movie(
`movie_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`movie_name` VARCHAR(255) NOT NULL,
`movie_type` TINYINT NOT NULL DEFAULT 0,
`movie_year` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
`movie_leadactor` INTEGER UNSIGNED NOT NULL DEFAULT 0,
`movie_director` INTEGER UNSIGNED NOT NULL DEFAULT 0,

PRIMARY KEY (movie_id),
KEY movie_type (movie_type, movie_year)
)
ENGINE=MyISAM';
$result = mysql_query($query, $db) or die(mysql_error($db));

MySQL query error (using php)

3 Answers3