mysql error question

[dennismonsewicz]

I am writing a script that allows people to upload a CSV file and then it dumps the contents into a SQL query and executes...

 

$file = file_get_contents('test.csv');
$q = mysql_query($file)or trigger_error('Query Failed! ' . mysql_error(), E_USER_ERROR);

 

When I run the script it is dumping out on one of the values in the script

 

CSV contents:

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`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6;


INSERT INTO `users` VALUES(3, 0, 'Dennis', 'Monsewicz', 'dennismonsewicz@gmail.com', '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);

 

the SQL error

Fatal error: Query Failed! 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 '; INSERT INTO `users` VALUES(3, 0, 'Dennis', 'Monsewicz', 'dennismonsewicz@gma' at line 28 in /Applications/MAMP/htdocs/site/index.php on line 102

 

this is line 28 in the CSV

ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6;

 

ANY help is appreciated!

[ozestretch]

can we see index.php on line 102 (and maybe some other code before / after it?)

[syed]

It appears you have an extra single quote in that line in your csv file. Check that line in your file and make sure there are no single quotes that are not escaped

[PFMaBiSmAd]

mysql_query() does NOT support multiple queries separated by ; because too many people attempting to use php were not validating external data to prevent sql injection.

 

You either need to parse the file into separate queries or you could use mysqli as it has a multi query statement.

[dennismonsewicz]

ok I have restructured a lot of my code:

 

$file = fopen('test.csv', 'r');

				while(($contents = fgetcsv($file, 1000, ";")) !== FALSE) {
					for($i = 0; $i < count($contents); $i++) {
						$data = $contents[$i];
						echo '<pre>';
							echo $data;
						echo '</pre>';
					}
				}

 

the output of the code:

 

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', 'dennismonsewicz@gmail.com', '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)

 

well the problem I am having is that it strips out the semi colon on each statement... is there anyway to add them back in?

[PFMaBiSmAd]

Of course you could always concatenate a ; onto the end of a string or a variable, but why, they are not needed when you execute a query through php.

[dennismonsewicz]

well here is my code now:

 

$file = fopen('test.csv', 'r');
				$data = '';

				while(($contents = fgetcsv($file, 0, ";")) !== FALSE) {
					for($i = 0; $i < count($contents); $i++) {
						$data .= $contents[$i];
					}
				}

				$q = mysql_query($data)or trigger_error('Query Failed! ' . mysql_error(), E_USER_ERROR);

 

Output:

Fatal error: Query Failed! 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 'INSERT INTO `users` VALUES(3, 0, 'Dennis', 'Monsewicz', 'dennismonsewicz@gmail.c' at line 1 in /Applications/MAMP/htdocs/liferay/index.php on line 110

[dennismonsewicz]

bump

[dennismonsewicz]

ok so what my script is doing is stripping out the semi colons from the SQL that is in the CSV... in my CSV I have a create function and the insert function... anyway to add the semicolon(s) back in?

[PFMaBiSmAd]

mysql_query() does NOT support multiple queries...

 

You [either] need to parse the file into separate queries...

Each separate query needs to be executed separately.

[dennismonsewicz]

hmm...

 

Here is what my CSV looks like:

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', 'dennismonsewicz@gmail.com', '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);

 

Here is how I am trying to parse out the data from the csv:

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

	while (($data = fgetcsv($handle, 0, ";")) !== FALSE) {
	$num = count($data);
		for ($c=0; $c < $num; $c++) {
			$q = mysql_query($data[$c])or trigger_error('Query Failed! ' . mysql_error(), E_USER_ERROR);
		}
	}

 

I am obviously doing something wrong...