Run SQL Statement from SQL File through PHP

[bpat1434]

Hey guys & gals.

I'm trying to finish my install script. I'm almost there. The only thing left is the SQL script. I have it so far to open and read the contents of the sql script. That was easy.

So I take the contents (which are fully read), and I use them as so:

$fp = fopen("table.sql", "r");
		$contents = fread($fp, filesize("table.sql"));
		fclose($fp);

	$result = mysql_query($contents);
	if(!$result){
		echo '<p>There is something wrong with the query.  Here is what it tried to run:<hr />'.$contents.'</p>';
	}
	else{
		echo '<p>Database is setup, now let us continue...<br /><a href="'.$self.'?step=6"><button>Continue ==></button></a></p>';
	}

When I bring up this script in my browser, it reads the contents of table.sql fine, but I can't seem to get it to fire off as a query.

The SQL File would look like any normal SQL file without PHP markup. So as an example:

-- 
-- Table structure for table `tickets_categories`
-- 

CREATE TABLE `categories` (
  `categories_id` tinyint(3) unsigned NOT NULL auto_increment,
  `categories_name` varchar(20) NOT NULL default '',
  `categories_order` tinyint(3) unsigned NOT NULL default '1',
  PRIMARY KEY  (`categories_id`),
  UNIQUE KEY `categories_name` (`categories_name`),
  KEY `categories_order` (`categories_order`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;

-- 
-- Dumping data for table `categories`
-- 

INSERT INTO `categories` VALUES (1, 'Department 1', 1);
INSERT INTO `categories` VALUES (2, 'Department 2', 2);

So that is an example of what my SQL file looks like. Works great if I go through phpMyAdmin, but not if I go through PHP...

I have even tried making the contents of the SQL file the code that is output for PHP by phpMyAdmin...

A little guidance here would be great.

Thanks.

~Brett

[RossC0]

Hi,

I the php mysql_query() only allows single queries - you need to run multiple queries.

Check out: mysqli_multi_query() If you are using php5.

Or I found this for php4 mysql_query:

Please note the sql statements are terminated with ;; not a ; this is so when you want to do a multiple query you can tokenise the sql string with a ;; which allows your data to contain a ;

If you want to run the multiple query then use this simple function which i wrote due to not being able to find a decent way of doing it

//$q is the query string ($thesql returned string)
//$link is the link to the database connection
//returns true or false depending on whether a single query is executed allows you to check to see if any queries were ran

function multiple_query($q,$link)
{
$tok = strtok($q, ";;\n");
while ($tok)
{
$results=mysql_query("$tok",$link);
$tok = strtok(";;\n");
}
return $results;
}

This might need tweaking - If the dump only has ; as the end of of the mysql statement - just replace the ;; for ;\n in the function...