SQL error when creating multiple tables

Question

I'm trying to create multiple tables with a query, but it doesn't seem to work. I'm using PHP code to execute the query. Below is my PHP code, my query and my error.

My PHP code:

<?php
$conn=mysql_connect("localhost","secret","secret") or die("Kan geen verbinding maken met de DB server"); 
mysql_select_db("secret",$conn) or die("Kan database niet selecteren"); 

$query_file = 'tables.txt';

$fp    = fopen($query_file, 'r');
$sql = fread($fp, filesize($query_file));
fclose($fp); 

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not create table: ' . mysql_error());
}
mysql_close($conn);
?>

My query:

CREATE TABLE pages(
     id INT NOT NULL AUTO_INCREMENT,
     pagid VARCHAR(32) NOT NULL,
     title  VARCHAR(32) NOT NULL,
     content  TEXT NOT NULL,
     image   VARCHAR(65) NOT NULL,
     youtube   VARCHAR(32) NOT NULL,
     primary key ( id ));

CREATE TABLE members(
     id INT NOT NULL AUTO_INCREMENT,
     username  VARCHAR(65) NOT NULL,
     password  VARCHAR(65) NOT NULL,
     primary key ( id ));

CREATE TABLE news(
     id INT NOT NULL AUTO_INCREMENT,
     pagid VARCHAR(32) NOT NULL,
     title  VARCHAR(32) NOT NULL,
     content  TEXT NOT NULL,
     image   VARCHAR(150) NOT NULL,
     youtube   VARCHAR(32) NOT NULL,
     date   VARCHAR(32) NOT NULL,
     primary key ( id ));

CREATE TABLE gallery(
     id INT NOT NULL AUTO_INCREMENT,
     image VARCHAR(65) NOT NULL,
     title  VARCHAR(65) NOT NULL,
     description  TEXT NOT NULL,
     url   VARCHAR(200) NOT NULL,
     category   VARCHAR(65) NOT NULL,
     primary key ( id ));

My error:

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 'CREATE TABLE members( id INT NOT NULL AUTO_INCREMENT, username VARCHA' at line 10

Does anyone have any idea what I'm doing wrong here?

How are you trying to create them? Command line? PHP Script? PhpMyAdmin? If its a PHP script are you doing all the CREATE queries in one method call? — Phil Cross
– Phil Cross, Commented Jun 1, 2013 at 17:11
I'll add the PHP I'm using. The might problem might be there. — Frank Kluytmans
– Frank Kluytmans, Commented Jun 1, 2013 at 17:12

Phil Cross · Accepted Answer · 2013-06-01 17:15:28Z

1

Try this:

$q1 = "CREATE TABLE pages(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    pagid VARCHAR(32) NOT NULL,
    title  VARCHAR(32) NOT NULL,
    content  TEXT NOT NULL,
    image   VARCHAR(65) NOT NULL,
    youtube   VARCHAR(32) NOT NULL)";

 $q2 = "CREATE TABLE members(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username  VARCHAR(65) NOT NULL,
    password  VARCHAR(65) NOT NULL)";

 mysql_query($q1);
 mysql_query($q2);

If you're using mysql_query, it only supports one query at a time.

However, I would recommend you avoid using mysql_* functionality as its depreciated. Try mysqli or PDO

answered Jun 1, 2013 at 17:15

Phil Cross

9,33212 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hauke P. · Accepted Answer · 2013-06-01 17:12:54Z

1

You're propably using mysql_query. The documentation explicitly states that multiple queries are not supported. As each of your create statements is an own query, you'll have to split up your query string.

answered Jun 1, 2013 at 17:12

Hauke P.

2,8531 gold badge23 silver badges48 bronze badges

1 Comment

Frank Kluytmans Over a year ago

How will I be able to execute these multiple queries? Could you give me an example?

Collectives™ on Stack Overflow

SQL error when creating multiple tables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related