Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 704
  • Last Modified:

import .sql file into mysql with php

Hello Experts!

I need a script importing .sql file into mysql.
Basically I want script doing same job as 'import' function in phpMyAdmin, but I need php code.

Thanks
0
Zado
Asked:
Zado
  • 7
  • 4
  • 2
  • +2
1 Solution
 
str82no1Commented:
Check these links:
http://www.sean-barton.co.uk/2009/03/sql-import-from-a-file-using-php/
http://bytes.com/topic/php/answers/12447-import-sql-file-via-php
http://www.webdeveloper.com/forum/showthread.php?t=192179

And take a look at the given code ;).
// A METHOD
$db = mysql_connect(....);
mysql_select_db(....);

$fp = fopen('somefile.sql', 'r');
while($fp != feof())
{
$line = fread($fp, 2048);
$line = mysql_real_escape_string($db, $line);
mysql_query($line);
}
fclose($fp);

//METHOD 2

Open in new window

0
 
Shahzad Fateh AliWeb Solutions Architect - VentureDive (Pvt) LtdCommented:
See the attached code.

Where USER,PASSWORD and DBNAME are your details for the database in which you want to import the dumped file, and dump.sql is the file you want to import. Make sure you give the correct path to the dump.sql file.
<?php
passthru("nohup mysql -u USERNAME -pPASSWORD DBNAME < dump.sql");
 ?>

Open in new window

0
 
ZadoAuthor Commented:
I tried what str82no1 suggested, but I have error:

Warning: feof() expects exactly 1 parameter, 0 given in C:\wamp\www\db2.php on line 9
Warning: mysql_real_escape_string() expects parameter 1 to be string, resource given in C:\wamp\www\db2.php on line 12

repeated many times.

I attached my .sql code below.
-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 30, 2010 at 10:48 AM
-- Server version: 5.1.37
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `helpdesk`
--

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

--
-- Table structure for table `address_book`
--

DROP TABLE IF EXISTS `address_book`;
CREATE TABLE IF NOT EXISTS `address_book` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  `email` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=2 ;

--
-- Dumping data for table `address_book`
--

INSERT INTO `address_book` (`id`, `name`, `email`) VALUES
(1, 'Admin', 'helpdesk@e-ship.co.uk');

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

--
-- Table structure for table `data_answers`
--

DROP TABLE IF EXISTS `data_answers`;
CREATE TABLE IF NOT EXISTS `data_answers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(11) NOT NULL,
  `title` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  `subtitle` varchar(100) COLLATE latin1_german2_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `data_answers`
--

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ZadoAuthor Commented:
@shahzadfatehali
your code doesn't work for me, I tried many path combinations, but with no result.

@str82no1
your last two links doesn't help, not sure about first one, also have problems with first link, doesn't work too.
0
 
Ray PaseurCommented:
Where does the .sql file come from?  Is it your own backup?  Why not just use phpMyAdmin?
0
 
ZadoAuthor Commented:
I installed virtual server 'WampServer' on Windows,
Path to mySql: C:\wamp\bin\mysql\mysql5.1.36
Path to file: C:\wamp\www\test.sql

I build program to run on virtual server for regular users (who don't have much knowledge about IT, computer stuff), and I managed to make everything as easy as possible, except import database. I don't want to use phpMyAdmin by users, I'd like to use some php script doing same job (or similar) as 'import' function in phpMyAdmin, if possible.

Thanks.
0
 
ZadoAuthor Commented:
Yaaaaay! :-)
How did you find it??? I though I searched whole google, I wasted 2 days for this!
I tested the script and it works almost perfectly.

Almost, because if I have semicolon (;) somewhere in data, I got error, so basically I have to replace semicolons with some other chart and problem resolved, but would be great to fix it if possible. Example below (error for Ship;33)

I got this 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 ''Ship' at line 1"
INSERT INTO `USERTABLE` (`UserID`, `ShipName`, `IMONumber`) VALUES
(1, 'Ship;33', '199'),
...

Open in new window

0
 
ZadoAuthor Commented:
Usesful tip: if you use PHP5.3.0, replace
"if(!ereg('^--', $line))" with this: "if(!preg_match('/^--/', $line))"
otherwise you'll get
"Deprecated: Function ereg() is deprecated in C:\wamp\www\db.php on line 31"
probably hundred times :-/
0
 
ZadoAuthor Commented:
Code provided by Roads_Roads attached below:
<?php
/* Accepts a filename and imports the SQL script in it.

   Returns: true if all is well
            false if something is wrong
            (error message is embedded in $errmsg)

   One can also use mysql_error() if this function
   returns an error.

*/

function mysql_import_file($filename, &$errmsg)
{
   /* Read the file */
   $lines = file($filename);

   if(!$lines)
   {
      $errmsg = "cannot open file $filename";
      return false;
   }

   $scriptfile = false;

   /* Get rid of the comments and form one jumbo line */
   foreach($lines as $line)
   {
      $line = trim($line);

      if(!ereg('^--', $line))
      {
         $scriptfile.=" ".$line;
      }
   }

   if(!$scriptfile)
   {
      $errmsg = "no text found in $filename";
      return false;
   }

   /* Split the jumbo line into smaller lines */

   $queries = explode(';', $scriptfile);

   /* Run each line as a query */

   foreach($queries as $query)
   {
      $query = trim($query);
      if($query == "") { continue; }
      if(!mysql_query($query.';'))
      {
         $errmsg = "query ".$query." failed";
         return false;
      }
   }

   /* All is well */
   return true;
}

/* Installs a DB with a given name with the help of a given
   .sql file

   Returns: true if all is well
       false if something is wrong
            (error message is embedded in $errmsg)

   One can also use mysql_error() if this function
   returns an error.

*/

function mysql_install_db($dbname, $dbsqlfile, &$errmsg)
{
   $result = true;

   if(!mysql_select_db($dbname))
   {
     $result = mysql_query("CREATE DATABASE $dbname");
     if(!$result)
     {
        $errmsg = "could not create [$dbname] db in mysql";
        return false;
     }
     $result = mysql_select_db($dbname);
   }

   if(!$result)
   {
      $errmsg = "could not select [$dbname] database in mysql";
      return false;
   }

   $result = mysql_import_file($dbsqlfile, $errmsg);

   return $result;
}
?>


USAGE EXAMPLE:
<?php 

$link = mysql_connect ( "127.0.0.1", "root", ""); 

if(mysql_install_db("testdb", "testdb.sql", $errmsg)) 
{ 
   echo "Success!!"; 
}else 
{ 
  echo "failure: ".$errmsg."<br/>".mysql_error(); 
} 
?>

Open in new window

0
 
Ray PaseurCommented:
The mysql_import_file() function seems to be a little confused about how to handle semi-colons.  This might make more sense.
function mysql_import_file($filename, &$errmsg)
{
   /* Read the file */
   $lines = file($filename);

   if(!$lines)
   {
      $errmsg = "cannot open file $filename";
      return false;
   }

   $scriptfile = false;

   /* Get rid of the comments */
   $queries = array();
   foreach($lines as $line)
   {
      $line = trim($line);

      if(!ereg('^--', $line))
      {
         $queries[] = $line;
      }
   }

   if(empty($queries))
   {
      $errmsg = "no text found in $filename";
      return false;
   }

   /* Run each line as a query */
   foreach($queries as $query)
   {
      if($query == "") { continue; }
      if(!mysql_query($query))
      {
         $errmsg = "query ".$query." failed";
         return false;
      }
   }

   /* All is well */
   return true;
}

Open in new window

0
 
Ray PaseurCommented:
Lose line 12 from above - not that it really matters to the output, it's just a useless part.

best, ~Ray
0
 
Ray PaseurCommented:
In case you want a backup / restore combination script (not just this restore) please post a new question and I'll be glad to give you one that I've used in the past.  It writes a file that you can copy, email, etc.  Over and out, ~Ray
0
 
ZadoAuthor Commented:
Thanks a lot Ray, helpful as always. New question posted ;-)
0
 
Lukasz ChmielewskiCommented:
what's the id ?
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now