[RESOLVED] CSV Uploader using PHP and MySQL

[fnp]

Basically I think I have most of the code written properly with the exception of something. This script executes fine but it only inputs the customer information and nothing else.

I think it is something to do with the switching of databases in the middle, but I am unsure.

Any help would be appreciated.

Here is the main section of the code (sorry for the disorganization, it is a work in progress).

<?php
$EmployeeID=$_SESSION['eid'];
	if(isset($_POST['submit'])){
		$filename=$_POST['filename'];
    		$handle = fopen("$filename", "r");
     		while (($data = fgetcsv($handle, 1000, ",")) !== FALSE){
				$connection = new mysqli($h['DCST'],$u['DCST'],$p['DCST'],$d['DCST']);
				$connection->select_db($d['DCST']);

			$importCust="INSERT into tblCustomers(CustomerID, CustName, CustNotes, AddrAttn, AddrLine1, AddrLine2, AddrCity, AddrStateProv, AddrPostalCode, Country, PhoneNumber1, PhoneNumber2, EmailAddress1, EmailAddress2, LastModifiedBy, LastModifiedTS) values(NULL, '$data[2]', NULL, NULL, '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[3]', NULL, '$data[4]', NULL, NULL, NULL )";
			$connection->query($importCust);

			$IMCustomerID="SELECT CustomerID FROM tblCustomers WHERE CustName='$data[2]'";
			$connection->query($IMCustomerID);


			$connection->close();

			$connection1 = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
			$connection1->select_db($d['DOST']);

			$importOrder="INSERT into tblOrders(OrderID, CustomerID, EmployeeID, ShipperID, ShipmentMethodID, OrderDate, ShipToName, ShipToAttn, ShipToAddrLine1, ShipToAddrLine2, ShipToCity, ShipToStateProvince, ShipToPostalCode, ShipToCountry, ShipToPhoneNumber, ShipNotes, ShippingDiscount, OSPIF, OSSHC, OSFBL, OSCLSD, OSCANCEL, PIFDate, SHCDate, FBLDate, CLSDDate, CANCELDate, LastModifiedBy, LastModifiedTS) values(NULL, '$IMCustomerID', '$EmployeeID', 'LEFT('$data[30]', 3)', NULL, 'curdate()', '$data[2]', NULL, '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[3]', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )";
			$connection1->query($importOrder);

			$IMOrderID="SELECT OrderID FROM tblOrders WHERE CustomerID='$IMCustomerID' ";
			$connection1->query($IMOrderID);

			$IMListingID="SELECT ListingID, SubmissionID FROM tblListings WHERE AuctionNumber='$data[11]' ";
			$connection1->query($IMListingID);

			$importOrderListingDetail="INSERT into tblOrderListingDetails(OrderID, ListingID, WinningBid, FeedbackID, DateFeedbackLeft, LastModifiedBy, LastModifiedTS) values('$IMOrderID', '$IMListingID[0]', '$data[15]', NULL, NULL, NULL, NULL ";
			$connection1->query($importOrderListingDetail);

			$IMItemID="SELECT ItemID FROM tblSubmissionItemDetails WHERE SubmissionID='$IMListingID[1]' ";
			$connection1->query($IMItemID);

			$importOrderItemDetail="INSERT into tblOrderItemDetails(OrderID, ItemID, LastModifiedBy, LastModifiedTS) values('$IMOrderID', '$IMItemID', NULL, NULL ";
			$connection1->query($importOrderItemDetail);

			$query = "UPDATE tblListings set DateEnded=DATE_FORMAT('$data[22]', '%y %b %d') AND BilledShippingHandling='$data[16]' WHERE ListingID='$IMListingID[1]'";
			$connection1->query($query);


			$connection1->close();





 		}

 		fclose($handle);

		 echo "Import done";



}

   else

   {



 print "<form action='csveb.php5' method='post'>";

  print "Type file name to import:<br>";

  print "<input type='text' name='filename' size='20'><br>";

  print "<input type='submit' name='submit' value='submit'></form>";
   }

?>

[Res]

Not overly familiar with using mysqli, but adding the following to each query you run should tell you what, where and how it's failing.

if(!$connection->query($IMCustomerID)){
   mysqli_error();
}

[fnp]

I added the lines in and get four of these messages back:

Warning: mysqli_error() expects exactly 1 parameter, 0 given ....

Not sure what that means. I do know that it starts at section on the code where 'connection1' starts.

Any ideas.

[Res]

Ah, my bad. Looks like that option is only available when you are using mysqli procedurally and have a link identifier available. Not sure how to produce the same effect when using it as an object.

Easy work around though. Instead of doing the mysqli_error, just doing something like:

if(!$connection->query($IMCustomerID)){
   echo "Failed to run query #x<br/>";
   echo $IMCustomerID."<br/>";
}

Not as much information as an error report, but at the very least it will let you know what parts of your script are having problems, and will allow you to examine the SQL for any noticable problems. At that point, if you still can't figure out what is wrong with the SQL statment, run it through phpmyadmin, if you have access, or through mysqli but procedurally so you can get the error output form mysql.

Sorry for screwing you up with the first post ><

[bradgrafelman]

if(!$connection->query($IMCustomerID)){
   echo "SQL error: " . $connection->error . "<br>\n";
}

[Res]

bradgrafelman;10950292 wrote:

if(!$connection->query($IMCustomerID)){
   echo "SQL error: " . $connection->error . "<br>\n";
}

It looked like from the manual that the above example would work. That is not the case from my testing.

if(!$connection->query('select * from users where useridd = 2')){
	echo "fail<br/>";
	$connection->error;
}

Produces just the text "fail" instead of including an error message about an unknown column.

Looked like it too was a feature of using mysqli procedurally.

[bradgrafelman]

In the code you posted, you don't do anything with $connection->error ... try echo'ing it instead.

[fnp]

Ok finally got logged back in, here is what I receive when I added that code in:

Warning: main() [function.main]: Couldn't fetch mysqli in /~/csveb.php5 on line 79
SQL error:

Line 79 is the line that I added the new code into.

Thanks for the help.

[fnp]

After doing some tweaking I now get and actual error message. It says there is something wrong with my SQL statement.

SQL error: 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 'NAME'', '59', LEFT('UPS Ground', 3), RIGHT('UPS Ground', 6), CURDATE(),' at line 1
Import done

I think the SQL error is due to the second ' after the NAME, I have rechecked the code as well as the csv file and neither have an extra '.

The problem could also be in the way I am using the LEFT and RIGHT, or CURDATE() functions.

Anyway I will continue to troubleshoot this problem.

[bradgrafelman]

Okay, now we're getting somewhere. Can you also echo out the SQL query string itself that caused that error?

Also, I count at least 3 SELECT queries (at quick glance - could be less, could be more) that you could omit entirely and instead use the mysqli->insert_id property (manual page: [man]mysqli.insert-id[/man]) to retrieve the ID value (read: "value of the AUTO_INCREMENT column") of the preceding INSERT query.

[fnp]

Thanks for the response.

I have the code listing to the tblOrders but with a few problems, I don't however get any errors on it any more.

<?php
$EmployeeID=$_SESSION['eid'];
	if(isset($_POST['submit'])){
		$filename=$_POST['filename'];
    		$handle = fopen("$filename", "r");
     		while (($data = fgetcsv($handle, 1000, ",")) !== FALSE){
				$connection = new mysqli($h['DCST'],$u['DCST'],$p['DCST'],$d['DCST']);
				$connection->select_db($d['DCST']);

			$importCust="INSERT into tblCustomers(CustomerID, CustName, CustNotes, AddrAttn, AddrLine1, AddrLine2, AddrCity, AddrStateProv, AddrPostalCode, Country, PhoneNumber1, PhoneNumber2, EmailAddress1, EmailAddress2, LastModifiedBy, LastModifiedTS) values(NULL, '$data[2]', NULL, NULL, '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[3]', NULL, '$data[4]', NULL, NULL, NULL )";
			$connection->query($importCust);

			//$IMCustomerID="SELECT CustomerID FROM tblCustomers WHERE CustName='$data[2]'";
			//$connection->query($IMCustomerID);


			$connection->close();

			$connection1 = new mysqli($h['DOST'],$u['DOST'],$p['DOST'],$d['DOST']);
			$connection1->select_db($d['DOST']);

			$importOrder="INSERT into tblOrders(CustomerID, EmployeeID, ShipperID, ShipmentMethodID, OrderDate, ShipToName, ShipToAddrLine1, ShipToAddrLine2, ShipToCity, ShipToStateProvince, ShipToPostalCode, ShipToCountry, ShipToPhoneNumber, LastModifiedBy, LastModifiedTS) values('mysqli_insert_id($importcust)', '$EmployeeID', LEFT('$data[30]', 3), RIGHT('$data[30]', 6), CURDATE(), '$data[2]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[3]', '$EmployeeID', UNIX_TIMESTAMP() )";
			$result = $connection1->query($importOrder);

			if (!$result) {
					$message  = 'SQL query: ' . $connection1->error . "<br ><br >\n";
					$message .= 'Whole query: ' . $importOrder;
						die($message);
			}

			//$IMOrderID="SELECT OrderID FROM tblOrders WHERE CustomerID='$IMCustomerID' ";
			//$connection1->query($IMOrderID);

			$IMListingID="SELECT ListingID, SubmissionID FROM tblListings WHERE AuctionNumber='$data[11]' ";
			$connection1->query($IMListingID);

			$importOrderListingDetail="INSERT into tblOrderListingDetails(OrderID, ListingID, WinningBid, FeedbackID, DateFeedbackLeft, LastModifiedBy, LastModifiedTS) values('mysqli_insert_id($importOrderID)', '$IMListingID[0]', '$data[15]', NULL, NULL, NULL, NULL ";
			$connection1->query($importOrderListingDetail);

			$IMItemID="SELECT ItemID FROM tblSubmissionItemDetails WHERE SubmissionID='$IMListingID[1]' ";
			$connection1->query($IMItemID);

			$importOrderItemDetail="INSERT into tblOrderItemDetails(OrderID, ItemID, LastModifiedBy, LastModifiedTS) values('$IMOrderID', '$IMItemID', NULL, NULL ";
			$connection1->query($importOrderItemDetail);

			$query = "UPDATE tblListings set DateEnded=DATE_FORMAT('$data[22]', '%y %b %d') AND BilledShippingHandling='$data[16]' WHERE ListingID='$IMListingID[1]'";
			$connection1->query($query);


			$connection1->close();





 		}

 		fclose($handle);

		 echo "Import done";



}

   else

   {



 print "<form action='csveb.php5' method='post'>";

  print "Type file name to import:<br>";

  print "<input type='text' name='filename' size='20'><br>";

  print "<input type='submit' name='submit' value='submit'></form>";
   }

?>

The " 'mysqli_insert_id($importcust)' " is not inserting the customerID like I had hoped it would. Also the fields that I have now omitted from the statement come through as NULL where I need them to not come through at all, and last but not least the 'UNIX_TIMESTAMP()' inputs all zeros.

Any ideas would be helpful.
And again sorry for the messy code.

[bradgrafelman]

fnp wrote:

The " 'mysqli_insert_id($importcust)' " is not inserting the customerID like I had hoped it would.

That's because it's a PHP function, not a MySQL function (hence why I linked you to the PHP manual, not the MySQL manual 🙂).

Also, you're using OOP-style MySQLi commands, so you'd want to keep things uniform and use the OOP-style of retrieving the insert ID (as I hinted at in my post above). Example:

                $connection->query($importCust); 

            $IMCustomerID = $connection->insert_id;

Note that no error checking is done to ensure that the INSERT query succeeded... might want to consider adding such checks at some point (I can understand if you're of the mindset where you'd rather get things working before you add things like extensive error checking/handling).

[fnp]

Ok so I have got them all to upload except for a couple of the fields of the csv file.

I am not sure how to change a date to the proper format and how to change currency.

For instance my dates in the csv file come through like 'Mar-21-10' where as I need them to come through as '2010-03-21'.

The currency in the csv are like '$21.88' and I need to eliminate the '$' from the front.

Otherwise everything is inserting and updating properly.

As always any help is appreciated.

[bradgrafelman]

fnp;10950538 wrote:

For instance my dates in the csv file come through like 'Mar-21-10' where as I need them to come through as '2010-03-21'.

[man]strtotime/man and then [man]date/man.

fnp;10950538 wrote:

The currency in the csv are like '$21.88' and I need to eliminate the '$' from the front.

[man]ltrim/man or [man]str_replace/man or something similar

[fnp]

I got the currency to work but the date conversions are rough. I will post back on Sunday as I will be gone for a couple of days.

I appreciate all the help to this point.

[bradgrafelman]

fnp wrote:

I got the currency to work but the date conversions are rough.

Rough? Unless you've got varying date formats, it's a simple one-liner:

$sql_date = date('Y-m-d', strtotime($date_from_CSV));

[fnp]

Finally everything works as it should. Thanks for the help with the date thing bradgrafelman and all the help you have given me.

Now I just have to figure out how to make it search to find if the customer is already in our db and bypass the adding of a new customer section of the code. I am going to mark this thread as solved as essentially it is.

Thanks again

[bradgrafelman]

fnp;10950805 wrote:

Now I just have to figure out how to make it search to find if the customer is already in our db and bypass the adding of a new customer section of the code.

What criteria would you be using to search? Sounds like it'd be easier to just create either a PRIMARY or UNIQUE key/index on the columns you define that make an entry unique (e.g. "CustName"). That way, you'd simply do the INSERT query as normal and check for error code 1062 ("duplicate data for key...") that indicates the given information already matched a row in the table.

[fnp]

Thanks for the tip, but after posting yesterday I just whipped it out, I went with an if and else statement. I needed the flexibility of this because I will be applying this csv uploader to several different types of csv's.

				$CheckCustomer="SELECT EmailAddress1, CustomerID FROM tblCustomers WHERE CustName='$data[2]' ";
				$result = $connection->query($CheckCustomer);
				$row2 = mysqli_fetch_array($result);


			if($row2[0] == $data[4]) {
				$IMCustomerID=$row2[1];
			}
			else {

				$importCust="INSERT into tblCustomers(CustomerID, CustName, CustNotes, AddrAttn, AddrLine1, AddrLine2, AddrCity, AddrStateProv, AddrPostalCode, Country, PhoneNumber1, PhoneNumber2, EmailAddress1, EmailAddress2, LastModifiedBy, LastModifiedTS) values(NULL, '$data[2]', NULL, NULL, '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[3]', NULL, '$data[4]', NULL, '$EmployeeID', NOW() )";
				$connection->query($importCust);

				$IMCustomerID=$connection->insert_id;
			}

Again sorry about the messy code and thanks for the help.