Is it possible to import a sql script and split the queries?

Question

Is it possible to import a sql script and split the queries?

Navigation

#1 by (2 votes)

1

I'm looking to create a database from a one-button event, the problem is that the way I'm doing it at the moment, is to take each query, add it manually and execute it, all right up there. The problem is that every time there is a change in the database (either in one of its tables, or some kind of data that has one of them int , text ... etc) I have to go and make the manual change mind thing that takes me some time depending on the amount of changes that are made, I leave an example:

<?php
$mysql = new MySQL();
$mysql->conectar();
$mysql->ingresoRegistro("CREATE DATABASE MoltranAP_".$idEmpresa."BD");

//Creamos las tablas 

//Table 'Electrificadora'

$query1 = "CREATE TABLE IF NOT EXISTS MoltranAP_".$idEmpresa."BD.Electrificadora (
           'id' INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
           'nombre' VARCHAR(45) NOT NULL COMMENT '',
           'estado' int(50) NOT NULL,
           PRIMARY KEY ('id')  COMMENT '',
           UNIQUE INDEX 'nombre' ('nombre' ASC)  COMMENT '')
           ENGINE = InnoDB
           AUTO_INCREMENT = 1
           DEFAULT CHARACTER SET = latin1";

$mysql->ingresoRegistro($query1);
//Table 'ArchivoElectrificadora'

$query2 = "CREATE TABLE IF NOT EXISTS  MoltranAP_".$idEmpresa."BD.ArchivoElectrificadora (
           'id' int(11) NOT NULL AUTO_INCREMENT,
           'fechaHora' datetime NOT NULL,
           'fechaArchivo' date NOT NULL,
           'nombre' varchar(100) NOT NULL,
           'cantidadRegistros' int(11) NOT NULL,
           'tipo' varchar(45) NOT NULL,
           'zona' tinyint(4) NOT NULL,
           'Electrificadora_id' int(11) NOT NULL,
            PRIMARY KEY ('id'),
              KEY 'fk_ArchivoElectrificadora_Electrificadora1_idx' ('Electrificadora_id')
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";

$mysql->ingresoRegistro($query2);

$idEmpresa is the id of a newly registered company and this will be the "differentiator" to say it in some way from the database that I will create, it would be something like that MoltranAP_1BD .

the number of tables is around 30, my intention is to be able to import the script .sql and extract the queries with this could save time.

Is it possible?

mysql php mysqli

asked by Alan 22.09.2016 в 16:57

source

1 answer

Edit LaTeX snippets in Sublime Text 3 Select dependent on angle

score 2 · Answer 1

It seems that the only dynamic part in those SQL statements is the name of the database. One possibility would then be to use USE . What USE does is tell MySQL which database should be used by default (as of that moment) for the queries / statements that are executed. For example, if $idEmpresa is 1:

USE MoltranAP_1BD;

Then from then on, all the sentences will be executed in the database "MoltranAP_1BD". The next thing would be to create a file where you had all the queries without specifying the database, something like this:

CREATE TABLE IF NOT EXISTS Electrificadora (
           'id' INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
           'nombre' VARCHAR(45) NOT NULL COMMENT '',
           'estado' int(50) NOT NULL,
           PRIMARY KEY ('id')  COMMENT '',
           UNIQUE INDEX 'nombre' ('nombre' ASC)  COMMENT '')
           ENGINE = InnoDB
           AUTO_INCREMENT = 1
           DEFAULT CHARACTER SET = latin1;

CREATE TABLE IF NOT EXISTS ArchivoElectrificadora (
           'id' int(11) NOT NULL AUTO_INCREMENT,
           'fechaHora' datetime NOT NULL,
           'fechaArchivo' date NOT NULL,
           'nombre' varchar(100) NOT NULL,
           'cantidadRegistros' int(11) NOT NULL,
           'tipo' varchar(45) NOT NULL,
           'zona' tinyint(4) NOT NULL,
           'Electrificadora_id' int(11) NOT NULL,
            PRIMARY KEY ('id'),
              KEY 'fk_ArchivoElectrificadora_Electrificadora1_idx' ('Electrificadora_id')
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

....

Those sentences are "generic" and will be executed in the database that is active at that moment (which will be the one you specified with USE : "MoltranAP_1BD" ).

The steps to follow would be:

Save the name of the database you are using at this time

Create the new database

Use USE to change to the new database

Read and execute the file with the 30 SQL statements

Use USE to change to the original database (saved in step 1)

And if you have to do an update the idea would be the same, put all the changes in a SQL file without indicating the database, create a loop that crosses all the databases using USE and executes the SQL file .