PHP Execute MySQL From Text File

[JustinK101]

Hello,

 

I have a text file full of MySQL commands. Is it possible to have PHP simply open this text file read it and exectue all MySQL commands inside of it?

 

Something along the lines of:

 

mysql_query("/my_sql_schema.sql");

 

Thanks.

[fert]

just open the file and explode the commands and then loop through the array with the commands and run them.

[JustinK101]

How do I explode on this?

 

-- phpMyAdmin SQL Dump
-- version 2.11.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Mar 05, 2008 at 11:50 PM
-- Server version: 5.0.45
-- PHP Version: 5.2.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `timecard_demo`
--

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

--
-- Table structure for table `administrators`
--

CREATE TABLE IF NOT EXISTS `administrators` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `username` varchar(30) NOT NULL,
  `password` blob NOT NULL COMMENT 'Encrypted Value Stored',
  `date_created` datetime NOT NULL,
  `date_last_modified` datetime NOT NULL,
  `user_who_created` varchar(30) NOT NULL,
  `user_who_last_modified` varchar(30) NOT NULL,
  `last_succ_login` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `administrators`
--

INSERT INTO `administrators` VALUES(1, 'demo', 0xecfe05ea39319cf7443d9c3d4b4e169a, '2007-04-09 19:30:40', '2007-04-09 19:30:40', 'phpMyAdmin', 'phpMyAdmin', '2007-10-09 22:44:50');

[JustinK101]

I came across this function, I will test it and respond back with results. Hopefully helps others.

 

function mysql_query_file($url, $ignoreerrors = false) {
		$file_content = file($url);
		$query = "";
		foreach($file_content as $sql_line) {
			$tsl = trim($sql_line);
			if (($sql_line != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {
				$query .= $sql_line;
				if(preg_match("/;\s*$/", $sql_line)) {
					$query = str_replace(";", "", "$query");
					$result = mysql_query($query);
					if (!$result && !$ignoreerrors) die(mysql_error());
						$query = "";
		   		}
		 	}
	   	}
	}

[aschk]

You can run this from the cmd line, and thus in PHP using exec() or system();

 

e.g.

<?php

exec("mysql -u <username> -p <database name> < <text file.sql>");

?>

[JustinK101]

My host prevents running exec or else I would do that. The function I posted above works well mysql_query_file.