import sql via php

Question

This may be obvious, but I have exported a database via phpmyadmin to a .sql file. I'm trying to import the file into another database via a php script, but I can't figure out how to do this. I'm sure it's easy to do since i can easily accomplish the same thing by running the query through phpmyadmin. Any suggestions?

This is a duplicate question of http://stackoverflow.com/questions/147821/loading-sql-files-from-within-php — adorablepuppy, Apr 21 '11 at 05:00
possible duplicate of [how to import .sql file in mysql database using php](http://stackoverflow.com/questions/19751354/how-to-import-sql-file-in-mysql-database-using-php) — T.Todua, Nov 27 '14 at 22:01

score 7 · Answer 1 · answered Apr 21 '11 at 05:02

7

You can run mysql command using system() function of php.

You can not do with mysql_query as this function can not run multiple queries

system('mysql -u <user> -p<password> dbname < filename.sql');

answered Apr 21 '11 at 05:02

Shakti Singh

84,385
21
134
153

score 3 · Answer 2 · answered Mar 15 '13 at 00:19

I wrote this code and it worked for me. Hope it helps.

Note: It seems a bit too simple, could anyone, please, warn me if I'm overlooking something? Thanks

$connection = new mysqli(DB_SERVER, DB_USER, DB_PASS, $databaseName);

$query = "";

foreach ($sqlFileContents as $line) {
  $line = trim($line);

  if ($line != "" && substr($line, 0, 2) != '--') {
    $query .= $line;

    if (substr($line, -1) == ';') {
      $connection->query($query);

      echo ($query."\n");

      if ($connection->errno) {
        echo ("\n".$connection->errno . ": ");
        echo ($connection->error."\n");
      }

      $query = "";
    }
  }
}

unset ($line);

if ($connection->errno) {
  echo ($connection->errno . ": ");
  echo ($connection->error);
}

$connection->close();

score 1 · Answer 3 · edited May 23 '17 at 12:31

~~If it's a giant SQL file, all you need to do is~~

$sql = file_get_contents('sqlfile.sql');
//make the connection
mysql_query($sql); //assuming what you have is actually valid SQL

Just curious, why not just do this through a DB utility?

EDIT : As Shakthi Singh points out, you can't run multiple queries in mysql_query in one go (not tested though).

However, Depending on your file this might still cause problems as talked about in this question : PHP: multiple SQL queries in one mysql_query statement (Some statments not being supported by mysql_query)

So depending on how your SQL looks the above solution might no be viable.

All is not lost however, this thread : Loading .sql files from within PHP talks about a few alternatives. User arin sarkissian points out that mysqli_multi_query() is an option

@Shakti Singh good point. I've clarified my answer to take this into account — JohnP, Apr 21 '11 at 05:15

score 1 · Answer 4 · edited May 23 '17 at 11:50

1

A very nice answer:

Best practice: Import mySQL file in PHP; split queries

all you need to add here is an upload function

edited May 23 '17 at 11:50

Community

1
1

answered Apr 21 '11 at 05:00

Adnan

25,882
18
81
110

score 0 · Answer 5 · answered Apr 21 '11 at 04:59

0

Read in the file, then

mysql_query(file_contents)

Beware the timeoutwock, my son.

answered Apr 21 '11 at 04:59

Borealid

95,191
9
106
122

@Shakti Singh: How do SQL injection attacks against PHP using `;` to terminate a query and start a new one work then? [Bobby Tables](http://xkcd.com/327/) would have no effect on a web site using the mighty `mysql_query`... – Borealid Apr 21 '11 at 05:12
1

I'm not talking about SQL injection here. As stated in manual `mysql_query() sends a unique query (multiple queries are not supported)`. OR you could try by yourself if it works. – Shakti Singh Apr 21 '11 at 05:16
@Shakti Singh: just tried it. Worked fine; only got results for the first query, but the later ones executed. I think the documentation is either for a newer version of PHP or full of it. – Borealid Apr 21 '11 at 05:19

import sql via php

5 Answers5