How to create a set of tables in the database via PHP

Asked

Viewed 1,827 times

1

I’m having problems while creating the database automatically via PHP. I am developing a college MVC project and wanted to import the sql file and through a query create the tables.

In the previous part of the code is made the connection with the database and also the creation of the database also via query and both work perfectly; but when creating the tables nothing happens or errors, the code is as follows:

$sql = file_get_contents(BASE_URL."database/arquivo.sql");
echo $sql;
$this->connection->multi_query($sql);

I’ve tried with query() and multi_query() and nothing happens, which could be wrong?

Guys, the sql file link is this: https://www.dropbox.com/s/edp8uhifnl2mw97/lapesa.sql?dl=0

is indicating the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_' at line 12

If I delete this line, the error continues for the following lines, I am unable to resolve.

php mysql mvc mysqli
  • 1

    Could you put part of the.sql file content? see if this returns any errors: $res = $this->connection->multi_query($sql);
if(!$res){
 echo $this->error;
}.

  • 1

    The content of the sql file is generated by phpmyadmin, so there are no syntax errors or anything like that, I just need to generate it automatically because I will have to export the project

  • I had forgotten to comment on that in the question

  • The screen is blank? checked if the error display is not hidden.

  • Error display is enabled and screen has not turned white, is showing all mounted view

  • It could be your connection...

  • 1

    I tested here part of sql file and had the same error, so I removed the header(comments) and table administrador I changed the primary key of id for id_admin

  • ai worked perfectly?

  • perfect, worked just right

Show 4 more comments

1 answer

1


I tested here and all tables were created even with comments/headers in the file. The only problem was that in all tables the primary key is id only that this column does not exist in any table. Then you correct the names.

Obs: I deleted some fields from the table leaving only id_something

CREATE TABLE IF NOT EXISTS `administrador` (
  `id_admin` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Estrutura da tabela `apresentacao`
--

CREATE TABLE IF NOT EXISTS `apresentacao` (
  `id_apresentacao` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

The code of the test was as follows:

$sql = file_get_contents('sql.sql');

if(!$db->multi_query($sql)){
    echo $db->error;
}else{
    echo 'tabelas criadas com sucesso';
}

Utilize multi_query() to process multiple sql statements at once, remembering that they should be separated by a period and a comma.

Browser other questions tagged php mysql mvc mysqli

You are not signed in. Login or sign up in order to post.