One SQL query to create multiple tables with same sheet

Question

I'm trying to create multiple tables with same sheet in a single query like this

 $query ="CREATE TABLE users1, users2, users3 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(24) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    )";

then tried to execute it using PDO

$stmt->execute();

but didn't work, Then tried separating them

$query = "CREATE TABLE users1 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(24) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
    );
CREATE TABLE users2 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(24) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
    );
CREATE TABLE users3 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(24) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
    );
";

once with and without the ; but still give an error

`CREATE TABLE LIKE` https://dev.mysql.com/doc/refman/5.7/en/create-table-like.html — kRicha, Jun 07 '17 at 10:15
@jarlh trying to make 3 types of pages with three different users — Calibur Victorious, Jun 07 '17 at 10:15
I agree with jarlh; a table for each user is not good design. The error you get pertains to a superfluous comma: `password VARCHAR(255) NOT NULL,)` — HoneyBadger, Jun 07 '17 at 10:15
if you need different types of users, just add column `type` to your user table — kRicha, Jun 07 '17 at 10:16
@kRicha i'm not making a normal website concept in this part. — Calibur Victorious, Jun 07 '17 at 10:17
Nor a normal database concept... Have one table, add a column for type if needed. — jarlh, Jun 07 '17 at 10:18

Ahmed Ginani · Accepted Answer · 2017-06-07T10:24:36.463

0

Try below code:

$sql = "
CREATE TABLE users1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(24) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL
);
CREATE TABLE users2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(24) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL
);
CREATE TABLE users3 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(24) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL
);
";

try {
    $stmt = $db->prepare($sql);
    $stmt->execute();
}
catch (PDOException $e)
{
    echo $e->getMessage();
    die();
}

You can read something interesting about pdo multi query :PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND)

edited Jun 07 '17 at 10:24

answered Jun 07 '17 at 10:19

Ahmed Ginani

6,522
2
15
33

Can't we replace Primary Key with Unique or Index? or do we have to ALTER it? – Calibur Victorious Jun 07 '17 at 10:27
i think no need to do it. just check different between Unique and Primary key. – Ahmed Ginani Jun 07 '17 at 10:32
i trued username VARCHAR(24) NOT NULL INDEX, BUT gave me a problem, I guss only Primary and Unique are possible in this part. – Calibur Victorious Jun 07 '17 at 10:37
1

https://dba.stackexchange.com/questions/35821/possible-index-on-a-varchar-field-in-mysql – Ahmed Ginani Jun 07 '17 at 10:39

One SQL query to create multiple tables with same sheet

1 Answers1