Solved

import .sql file into mysql with php

Posted on 2010-08-30
15
688 Views
Last Modified: 2013-12-13
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
Comment
Question by:Zado
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 5

Expert Comment

by:str82no1
ID: 33557148
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
 
LVL 9

Expert Comment

by:Shahzad Fateh Ali
ID: 33557149
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
 
LVL 8

Author Comment

by:Zado
ID: 33557297
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
 
LVL 8

Author Comment

by:Zado
ID: 33557316
@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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33557846
Where does the .sql file come from?  Is it your own backup?  Why not just use phpMyAdmin?
0
 
LVL 8

Author Comment

by:Zado
ID: 33558126
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
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 500 total points
ID: 33558263
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 8

Author Comment

by:Zado
ID: 33558938
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
 
LVL 8

Author Comment

by:Zado
ID: 33559660
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
 
LVL 8

Author Comment

by:Zado
ID: 33559685
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33559739
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
 
LVL 108

Expert Comment

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

best, ~Ray
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 33559777
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
 
LVL 8

Author Comment

by:Zado
ID: 33559939
Thanks a lot Ray, helpful as always. New question posted ;-)
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 33562098
what's the id ?
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now