CODEWITHSUNDEEP
phpmysqlsql
Frank Kluytmans
Frank Kluytmans

Reputation: 543

SQL error when creating multiple tables

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?

Upvotes: 0

Views: 671

Answers (2)

Phil Cross
Phil Cross

Reputation: 9302

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

Upvotes: 1

Hauke P.
Hauke P.

Reputation: 2823

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.

Upvotes: 1

Related Questions