CODEWITHSUNDEEP
mysqlsql
Policeboy109
Policeboy109

Reputation: 13

Multiple CREATE TABLE in one script

I am making a php setup page and looking to run an sql file on click of the submit button. Although, in the SQL file I have 2 CREATE TABLE and it will only work if there is 1 CREATE TABLE. Snippet below:

CREATE TABLE users (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
username varchar(255) NOT NULL,
email varchar(255) NOT NULL,
password varchar(255) NOT NULL
);

CREATE TABLE details (
  name varchar(255),
  server_ip varchar(255),
  store_link varchar(255),
  bans_link varchar(255)
);

php code:

  include("../config.php");
  $slashy = file_get_contents("setup.sql");
  mysqli_query($conn, $slashy);

Upvotes: 1

Views: 1141

Answers (2)

PA.
PA.

Reputation: 29369

Your code assumes that there is only one statement in the file.

You may either (1) parse the statements to execute them sequentially, or (2) execute the file with an external invocation of the mysql command.

1. parsing

Parsing the statements looks like a simple task, something along 

$sqltxt = file_get_contents("setup.sql");
$statements = explode(";", $sqltxt);
foreach($statements as $stmt){
   @mysql_query($stmt)
}

but beware because it is easy to get caught by internal commands, embedded semicolons and other traps.

2. external command

it looks easy, too

$command = 'mysql --user=usr1 --password=secret --database=db --execute="SOURCE setup.sql"';
$res = shell_exec($command);

but again can get caught accomodating to the appropiate command in a cross platform way.

Upvotes: 1

Jim Horn
Jim Horn

Reputation: 889

Not really sure what you're trying to pull off here, but if you are attempting to validate if the table exists before creating it the T-SQL would go like this..

IF NOT EXISTS (SELECT name FROM sys.tables WHERE name='users') 
	CREATE TABLE users (
		'id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		username varchar(255) NOT NULL,
		email varchar(255) NOT NULL,
		password varchar(255) NOT NULL)

IF NOT EXISTS (SELECT name FROM sys.tables WHERE name='details') 
	CREATE TABLE details (
		name varchar(255),
		server_ip varchar(255),
		store_link varchar(255),
		bans_link varchar(255))

Upvotes: 0

Related Questions