Solved

import .sql file into mysql with php

Posted on 2010-08-30
15
695 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 110

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
 
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 110

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 110

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 110

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

623 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