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()); }

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', '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);

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

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

change ur mysql statement to: mysql_query("UPDATE c_listing SET image ='" . "$fileName" . "' WHERE c_listing_id ='" . "$id" . "' ")or trigger_error(mysql_error());

Sweet! that worked!

print_r($fileName) and make sure there is something there.

change mysql_query("UPDATE c_listing SET image ='" . "$fileName" . "' WHERE c_listing_id ='" . "$id" . "' "); to mysql_query("UPDATE c_listing SET image ='$fileName' WHERE c_listing_id ='$id' ");

if that is the case take a look here: http://us2.php.net/manual/en/function.chmod.php

it could be possible that you don't have 777 permissions on that folder you are moving the files into

Apparently I just need to give it up when it comes to loops lol... this is like the third time I have needed help on loops... Anyway, I have this code: while(list($key, $value) = each($_FILES['userfile']['name'])) { if(!empty($value)) { $this->multi_upload->go_upload(); $albumId = $this->input->post('album'); $songTitle = str_replace(" ", "_", $value); $audio->album_id = $albumId; $audio->title = $songTitle; $audio->file_path = '../primary_app/binary/' . $artistName . '/audio/'; $audio->active = 1; $audio->created_at = new Doctrine_Expression('NOW()'); $audio->updated_at = new Doctrine_Expression('NOW()'); $audio->save(); $counter++; } else { $counter = 0; } } The problem is that it is only adding the last submitted file into the DB (ex: if I upload 3 files, the last file in the list gets added to the DB instead of ALL of them). The $counter var is for an error checking if statement I have later in the script

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?

bump

well the reason I say loop is cause the way you have your regex (or at least the way I am reading it) is only accounting for subdomains that 3 domain parts (a.b.com) and not any greater than that.

you may have to do a loop... so like grab the URL and explode it on the . (dot) and count the array and then do a for loop that loops through the results and within the loop do your regex

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

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?

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!

print_r on $data[$c] prints the data out, but for some reason the function does nothing for me code: function removeLast($string, $char){ $pos = strrpos($string, $char); $newString = substr_replace($string, '', $pos, strlen($string)); return $newString; } $row = 1; $handle = fopen("test.csv", "r"); while (($data = fgetcsv($handle, ";")) !== FALSE) { $num = count($data); echo "<p> $num fields in line $row: <br /></p>\n"; $row++; for ($c=0; $c < $num; $c++) { //print_r(removeLast($data[$c], ',')); //print_r($data[$c]); } } fclose($handle);

@Mark Baker: The CSV file contains the insert into sql script sample of the csv: INSERT INTO `tblName` VALUES(1, 'att', 'AT&T ', '5700', '50', ' ', ' ', ' '); INSERT INTO `tblName` VALUES(2, 'att', 'AT&T ', '8525', '125', ' ', ' ', ' '); INSERT INTO `tblName` VALUES(3, 'att', 'AT&T ', 'TILT (8900) ', '50', ' ', ' ', ' '); INSERT INTO `tblName` VALUES(4, 'att', 'AT&T ', 'TILT (8925) ', '125', ' ', ' ', ' '); INSERT INTO `tblName` VALUES(5, 'att', 'AT&T ', 'USBCONNECT 881 ', '50', ' ', ' ', ' '); INSERT INTO `tblName` VALUES(6, 'att', 'AT&T ', 'USBCONNECT MERCURY ', '50', ' ', ' ', ' ');

here is my code function removeLast($string, $char){ $pos = strrpos($string, $char); $newString = substr_replace($string, '', $pos, strlen($string)); return $newString; } $row = 1; $handle = fopen("test.csv", "r"); while (($data = fgetcsv($handle, ";")) !== FALSE) { $num = count($data); echo "<p> $num fields in line $row: <br /></p>\n"; $row++; for ($c=0; $c < $num; $c++) { echo removeLast($data[$c], ','); } } fclose($handle); nothing echoes out on the screen now

INSERT INTO `tblName` VALUES(1,'att','AT&T ','5700','50',' ',' ',' '); so just like the normal output from above, without the trailing comma

$row = 1; $handle = fopen("test.csv", "r"); while (($data = fgetcsv($handle, ";")) !== FALSE) { $num = count($data); echo "<p> $num fields in line $row: <br /></p>\n"; $row++; for ($c=0; $c < $num; $c++) { echo substr_replace($data[$c], ",", -1); } } fclose($handle); The code above takes a csv file and dumps the results to the screen... I am wanting to strip the final comma off of the result: normal output: INSERT INTO `tblName` VALUES(1,'att','AT&T ','5700','50',' ',' ',' ');, what my code outputs: INSERT INTO `portal_deductible` VALUES(,'att,'AT&T ,'5700,'50,' ,' ,' '),

@zanus: My apologies for not being thorough in my explanation of everything involved with my code... I will try to explain everything here $this->load->database(); $affiliateId = $this->session->userdata('affiliateId'); $hashQuery = $this->db->get('paypal_payment_info'); foreach($hashQuery->result() as $hash) { $code[] = $hash->custom; } for($i = 0; $i < $hashQuery->num_rows(); $i++) { $count = explode("|", $code[$i]); $partOne = $count[0]; if($partOne == $affiliateId) { $partTwo = $count[1]; $partThree = $count[2]; $hash = $partOne . "|" . $partTwo . "|" . $partThree; $q = $this->db->query("SELECT custom, mc_gross, mc_fee, txn_id FROM paypal_payment_info WHERE custom = '$hash'"); $num = $q->num_rows(); if(strstr($hash,'Choose_Me_(Album)')) { $album_sales = ($q->num_rows() * 10) * .05; } else { $album_sales = '0'; } } else { $data['no_earnings_total'] = "You currently have no sales."; $album_sales = 'You have no Album Sales'; $num = 0; } } $data['album_sales'] = $album_sales; if($num > 0) { foreach($q->result() as $row) { $mc_gross = $row->mc_gross; $mc_fee = $row->mc_fee; $txn_id[] = $row->txn_id; } $total_earnings = ($mc_gross * $q->num_rows()) - ($mc_fee * $q->num_rows()); if($total_earnings != 0) { $data['total_earnings'] = $total_earnings; } else { $data['total_earnings'] = '0.00'; } } The PHP framework I am using is CodeIgniter. $this->load->database(); $affiliateId = $this->session->userdata('affiliateId'); $hashQuery = $this->db->get('paypal_payment_info'); foreach($hashQuery->result() as $hash) { $code[] = $hash->custom; } for($i = 0; $i < $hashQuery->num_rows(); $i++) { $count = explode("|", $code[$i]); $partOne = $count[0]; if($partOne == $affiliateId) { $partTwo = $count[1]; $partThree = $count[2]; $hash = $partOne . "|" . $partTwo . "|" . $partThree; Explanation: $affiliateId is a variable that is grabbing the session of affiliateId... this is set when a user logs into the system. $hashQuery basically just gets ALL records from the paypal_payment_info The foreach statement loops through all of the records and writes the column of `custom` into an array called $code[]. I then loop through each of the values in the $code[] array and explode (split apart) each value into separate parts... now the `custom` column in the table is where the original guy on this project saved different values into a piped value... example: 5|Choose_Me_(Album)|8803632a5d117faf7f50a8ae769a0eb8 breakdown of example above: 5 = affiliateId Choose_Me_(Album) = The purchased item 8803632a5d117faf7f50a8ae769a0eb8 = This is a hash value that I am not 100% sure on why is needed (I was not given direction on this item) but I am not overly concerned with this item at the moment. Ok... so the variable of $partOne is the first part of this piped value that is in the `custom` column... I am trying to match $partOne with the sessioned $affiliateId Zanus, again my apologies for not being as thorough as I should have been... does my above breakdown help any? One more thing... print_r($code) prints this out: Array ( [0] => 2|Choose_Me_(Single)|9787d7a24b2af9da8bc026acd1e38f0a [1] => 2|Choose_Me_(Album)|8803632a5d117faf7f50a8ae769a0eb8 [2] => 5|Choose_Me_(Album)|8803632a5d117faf7f50a8ae769a0eb8 ) and print_r($partOne) prints this out: 2 2 5 Each value in the $partOne var holds the first part of the exploded $code var and these again are the affiliate Ids Hope this clears this up bud

If I print_r($code) I get all of the items in the array as they should... if I do a print_r($partOne) I get all of the items in the array as they should... But for some reason I can't get $partOne and $AffiliateId to match... it disregards the items in the if and then does the else on this if/else statement... That is the first part... I am having some other problems, but I need to get this one addressed before I can really move on to another section.