CODEWITHSUNDEEP
mysqlsqldatabase
Petro Popelyshko
Petro Popelyshko

Reputation: 1379

how to create few tables in a single query. MySQL

I need to create 5-10 tables in mysql using php, but i think there is better way than the create 10 queries, so my question is, how to do this with a single query? is it psosible? Every table is different.

$sql = "CREATE TABLE IF NOT EXISTS `db_countries` (
           `countrykey` int(11) NOT NULL,
           `countrynamelat` varchar(500) NOT NULL default '',
            PRIMARY KEY  (`countrykey`)
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8";

mysql_query($sql);

  $sql2 = "CREATE TABLE IF NOT EXISTS `db_city`(
              `city_key` int(11) NOT NULL,
              `city_name` varchar(500) NOT NULL,
              `city_code` int(11) NOT NULL,
              `city_country_key` int(11) NOT NULL,
            PRIMARY KEY(`city_key`)
          ) ENGINE=MyISAM DEFAULT CHARSET=utf8";
    mysql_query($sql2) or die(mysql_error());

Upvotes: 2

Views: 4287

Answers (1)

papaiatis
papaiatis

Reputation: 4291

You can create a Stored Procedure that creates all your tables:

DELIMITER $$

CREATE PROCEDURE `localhost`.`sp_CreateTables` ()
BEGIN
  CREATE TABLE IF NOT EXISTS `db_countries` (
     `countrykey` int(11) NOT NULL,
     `countrynamelat` varchar(500) NOT NULL default '',
      PRIMARY KEY  (`countrykey`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

  CREATE TABLE IF NOT EXISTS `db_city`(
    `city_key` int(11) NOT NULL,
     `city_name` varchar(500) NOT NULL,
     `city_code` int(11) NOT NULL,
     `city_country_key` int(11) NOT NULL,
     PRIMARY KEY(`city_key`)
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
END

Then just call the stored procedure from PHP:

$sql = "call sp_CreateTables()";
mysql_query($sql) or die(mysql_error());

Upvotes: 2

Related Questions