[SOLVED] loading a sql file using PHP

[dennismonsewicz]

I started a post yesterday about parsing a CSV file, but I think I am going to scrap that and go down another route...

 

I have read that you can load sql files in PHP and execute them using mysql_query... well here is my code

 

if(mysql_query("SOURCE test.sql")) {
					echo 'success!';
				} else {
					echo 'die! ' . mysql_error();
				}

 

And this is the error I am getting:

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 'SOURCE test.sql' at line 1

[Raphael diSanto]

You can only send single queries to MySQL using mysql_query, I believe. Thus, you will have to split the file up into the individual queries contained within the file and loop through them and send them individually.

[dennismonsewicz]

hmmm.. how would I split the file up?

 

at the moment it contatins a create table command and an insert command

 

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `artist_id` int(11) NOT NULL default '0',
  `firstname` varchar(255) default NULL,
  `lastname` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `password` varchar(255) default NULL,
  `address` varchar(255) default NULL,
  `city` varchar(255) default NULL,
  `state` varchar(50) default NULL,
  `country` varchar(100) default NULL,
  `zipcode` bigint(20) default NULL,
  `phone` varchar(25) default NULL,
  `fax` varchar(25) default NULL,
  `notes` text,
  `file_path` varchar(255) default NULL,
  `registerdate` datetime default NULL,
  `activateddate` datetime default NULL,
  `status` varchar(255) default NULL,
  `vimeo_id` varchar(255) default NULL,
  `vimeo_token` varchar(255) default NULL,
  `hash` varchar(255) default NULL,
  `forgotten` varchar(100) default NULL,
  `created_at` datetime NOT NULL default '1970-01-01 00:00:00',
  `updated_at` datetime NOT NULL default '1970-01-01 00:00:00',
  `is_artist` int(11) default NULL,
  PRIMARY KEY  (`id`,`artist_id`)
);

INSERT INTO `users` VALUES(3, 0, 'Dennis', 'Monsewicz', '[email protected]', '5f4dcc3b5aa765d61d8327deb882cf99', '123 Address Lane', 'Nashville', 'TN', 'US', '37135', '8885551212', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00', NULL);

[Raphael diSanto]

Simplest split is:

 

$queries = explode(");", $fileContents)

 

Then foreach ($queries as $query) .. etc etc

[dennismonsewicz]

it is returning Resource id #4

 

$handle = fopen("test.csv", "r");
				$queries = explode(");", $handle);

				foreach($queries as $query) {
					print_r($query);
					//mysql_query($query)or die(mysql_error());
				}

[knsito]

this:

http://us2.php.net/fread

[dennismonsewicz]

that worked thanks!!!

 

But I am getting something weird...

 

It is picking up a blank entry and returning a mysql error Fatal error: Query was empty

 

Code:

if(!empty($_FILES['csvFile']['name'])) {
				$file = $_FILES['csvFile']['name'];
				$handle = fopen($file, "r");
				$contents = fread($handle, filesize($file));
				$queries = explode("%BREAK%", $contents);

					if(count($queries) > 0) {
						foreach($queries as $query) {
							if(mysql_query($query)) {
								echo '<p>Your CSV processed correctly!</p>';
							} else { 
								trigger_error(mysql_error(), E_USER_ERROR);
							}
						}
					} else {
						echo '<p>There was a problem in trying to execute your CSV file, please <a href="' . LINK . 'add">try again</a>.</p>';
					}
				} else {
					echo '<p>You must select your file before uploading, please <a href="' . LINK . 'add">try again</a>.</p>';
				}

[Garethp]

Because it's splitting by ; and the last query ends in an ;, that means that there will be one part of the array with nothing in it, so after foreach($queries as $query) { put

 

if($query == '')
{
continue;
}

[dennismonsewicz]

SWEET! It works now! this is so AWESOME!!!!

 

updated code:

if(!empty($_FILES['csvFile']['name'])) {
				$file = $_FILES['csvFile']['name'];
				$handle = fopen($file, "r");
				$contents = fread($handle, filesize($file));
				$queries = explode("%BREAK%", $contents);

					if(count($queries) > 0) {
						foreach($queries as $query) {
							if($query == '') {
								continue;
							} else {
								mysql_query($query)or trigger_error(mysql_error(), E_USER_ERROR);
								echo '<p>Your CSV processed correctly!</p>';
							}
						}
					} else {
						echo '<p>There was a problem in trying to execute your CSV file, please <a href="' . LINK . 'add">try again</a>.</p>';
					}
				} else {
					echo '<p>You must select your file before uploading, please <a href="' . LINK . 'add">try again</a>.</p>';
				}

 

One last thing though, how do I get the echo '<p>Your CSV processed correctly!</p>'; to only show up once upon parsing?

[MatthewJ]

Put it outside the loop

[dennismonsewicz]

duh! thanks for everyones help

 

updated code:

if(!empty($_FILES['csvFile']['name'])) {
				$file = $_FILES['csvFile']['name'];
				$handle = fopen($file, "r");
				$contents = fread($handle, filesize($file));
				$queries = explode("%BREAK%", $contents);

					if(count($queries) > 0) {
						foreach($queries as $query) {
							if($query == '') {
								continue;
							} else {
								mysql_query($query)or trigger_error(mysql_error(), E_USER_ERROR);
							}
						}
						echo '<p>Your CSV processed correctly!</p>';
					} else {
						echo '<p>There was a problem in trying to execute your CSV file, please <a href="' . LINK . 'add">try again</a>.</p>';
					}
				} else {
					echo '<p>You must select your file before uploading, please <a href="' . LINK . 'add">try again</a>.</p>';
				}