How to create multiple tables at once?

Question

How to create multiple tables at once?

Navigation

#1 by (3 votes)

1

I would like to know how I can make this code below execute the creation of several tables with mysqli_ at the same time because at the moment this code when executed only creates a single precise table so that it creates in the case all the tables below in bold or even more so and also allows to create the values INSERT INTO also multiple.

Tables: medias_category, banners, stockings, layers, serials, settings

Note: If you need these tables so that my question is better interpreted, please leave it in the comments that I will update the question.

And I put only the first table since it is not running more than one creation at a time.

// Conectar ao banco de dadso MYSQLI
$connect = new mysqli($_POST['db_host'], $_POST['db_user'], $_POST['db_pw'], $_POST['db_name']);
// Checar Conexão
if ($connect->connect_error) {
    die("Erro na Conexão: " . $connect->connect_error);
} 

// sql criar tabelas
$sql = "CREATE TABLE 'medias_categoria' (
  'id' int(255) NOT NULL AUTO_INCREMENT,
  'medias_categoria_url' char(255) COLLATE utf8_unicode_ci NOT NULL,
  'nome' char(255) COLLATE utf8_unicode_ci NOT NULL,
  'modo' enum('UNICO','MULTIPLO') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'UNICO',
  'data' date NOT NULL,
  PRIMARY KEY ('id'),
  UNIQUE KEY 'categoriaUnica' ('animes_categoria_url'),
  UNIQUE KEY 'nomeUnico' ('nome'),
  KEY 'colunasIndexadas' ('id','animes_categoria_url')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;";

if ($connect->query($sql) === TRUE) {
    echo "Table MyGuests created successfully";

} else {
    echo "Error creating table: " . $conn->error;
}

php mysqli

asked by anonymous 07.07.2015 / 10:26

1 answer

Relationship of tables Comparison with compareTo and Sort

score 3 · Accepted Answer

You can simply use the mysqli_multi_query function that allows you to run several query at the same time.

Example:

$sql = "CREATE TABLE 'medias_categoria' (
  'id' int(255) NOT NULL AUTO_INCREMENT,
  'medias_categoria_url' char(255) COLLATE utf8_unicode_ci NOT NULL,
  'nome' char(255) COLLATE utf8_unicode_ci NOT NULL,
  'modo' enum('UNICO','MULTIPLO') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'UNICO',
  'data' date NOT NULL,
  PRIMARY KEY ('id'),
  UNIQUE KEY 'categoriaUnica' ('animes_categoria_url'),
  UNIQUE KEY 'nomeUnico' ('nome'),
  KEY 'colunasIndexadas' ('id','animes_categoria_url')
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; "

$sql .= "CREATE TABLE 'banners' ... ; ";
// depois as restantes

$connect->multi_query( $sql );

// ou mysqli_multi_query ( $connect , $sql );

Although I recommend that you create one table at a time.

$connect->query( $sql_medias_categoria );
$connect->query( $sql_banners          );
// depois as restantes

Note: Do not forget the dependency between tables, otherwise it can go wrong.