CODEWITHSUNDEEP
phpmysqlimport
dvanderb
dvanderb

Reputation: 757

import sql via php

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?

Upvotes: 1

Views: 14801

Answers (5)

Jorge Raimundo
Jorge Raimundo

Reputation: 61

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();

Upvotes: 3

JohnP
JohnP

Reputation: 50029

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

Upvotes: 1

Shakti Singh
Shakti Singh

Reputation: 86476

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');

Upvotes: 7

Adnan
Adnan

Reputation: 26360

A very nice answer:

Best practice: Import mySQL file in PHP; split queries

all you need to add here is an upload function

Upvotes: 1

Borealid
Borealid

Reputation: 98559

Read in the file, then

mysql_query(file_contents)

Beware the timeoutwock, my son.

Upvotes: 0

Related Questions