CSV Import from webhost to different db server

[SFDonovan]

The MySQL database is NOT on the same host as the website. I had a version of this working when both db and website were in the same host but now they are on different and the db cannot see the tmp file created on the webserver.

What is my best course of action? I'm only talking about 100 records at a time so the import would not be too intensive.

[jkurrle]

Your website cannot access the mysql on the other server? If it could, it would simply be a matter of changing the login credentials for the mysql_connect() statement...

Do you have access to the mysql server? Does it have PHP support? Does it have FTP support?

Just looking for some ideas here...

[SFDonovan]

Yes the website has access to the MySQL server. The one thing it won't do anymore is insert the records from a file I uploaded to the website.

Upload a file using this:

echo "<form action='".$_server['php_self']."' method='post' enctype=\"multipart/form-data\">"

created a tmp file on the /private/var/tmp/ of the web/db server which MySQL would load using:

$sql = "LOAD DATA LOCAL INFILE '".mysql_real_escape_string($file_name)."'
	 INTO TABLE ".$prefix."$table_name 
	 FIELDS TERMINATED BY ','	  	 
	 LINES TERMINATED BY '\r'"; 

But now /private/var/tmp/ is on the webserver and the db is located across a network to another server.

If I physically upload to a directory to the webserver could I then do something like:

$uploadServer = 'http://xxx.xxx.edu/Volumes/Data HD/www.xxx.xxx.edu';

$uploadPath = '/xxxx/modules/$modname/uploadcache/';	

Then do

$sql = "LOAD DATA INFILE '$uploadServer.$uploadPath'".mysql_real_escape_string($file_name)."''  INTO TABLE ".$prefix."$table_name 
	 FIELDS TERMINATED BY ','	  	 
	 LINES TERMINATED BY '\r'"; 

[jkurrle]

It may be possible, I'm not sure. I'm not a guru here.

If I were in this situation, I would modify the code to open the uploaded file and convert it into an array of arrays, via a couple of explode() statements. From there, use a foreach loop to process each array into an insert statement.

Another workaround is if the database server has ftp or sftp access, you could use PHP's ftp functions to simply upload the file to the database server and load it locally there.

I wish I could give you more definitive answers than that, but I'm working within my own limitations here.

[SFDonovan]

I found an example of loading arrays and exploding them into INSERT statements.

I'm not sure I have the concatentation correct.

$csvFile = 	$uploadServer.$uploadFolder.$fileName;

I am also unsure why this example uses two INSERT INTO statements. I have used arrays very little.

$fileName = basename($_FILES['file_source']['name']);		
move_uploaded_file($_FILES['file_source']['tmp_name'], $uploadFolder.$fileName);
$uploadServer = $_SERVER["HTTP_HOST"];
$uploadFolder = $_SERVER['DOCUMENT_ROOT'] . "/atlas/modules/$modname/uploadcache/";
$csvFile = 	$uploadServer.$uploadFolder.$fileName;
$csv = file_get_contents($csvFile);

$breaker = "\n";
$fields = array('ReportNumber', 'Student_ID', 'Name_Last', 'Name_First', 'Total_RS', 'Total_Percent', 'Percentile', 'Subtest_RS', 'Subtest_Percent');

$lines = explode($breaker, $csv);
$sql = "INSERT INTO `$table_name` ('" . implode(", '", $fields) . "') VALUES ";
$db->sql_query($sql);
if (!$sql) {echo("<p>Error performing query: " . mysql_error() . "</p>");} 
$i=0;
foreach($lines as $line)
{
  if($i>0 && ($i%100 == 0))
  {
    $query = substr($query, 0, -1); // Get rid of trailing comma
    $query .= ";
INSERT INTO `$table_name` ('" . implode("', '", $fields) . "') VALUES ";
  }

  $values = explode(',', $line);
  $query .= "
('" . implode("', '", $values) . "'),";
}

$result = $db->sql_query($query);	
if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}
if(mysql_affected_rows() != count($lines))
  die('MySQL Insert failed to insert all entries.');
else
  header("Location: ".$admin_file.".php?op=TLScantronIratInsert&Session_ID=$Session_ID");
  }

?>

[jkurrle]

Well, I am going off of your earlier example where each line ends with "\r". This is a quick and dirty example:

$insert_array=array();
$handle = fopen("c:\\data\\info.txt", "r");

if ($handle) 
  {
  while (!feof($handle)) 
    {
    $buffer = fgets($handle, 4096);
    $insert_explode_array=explode(",",$buffer);
    $insert_item="(\"".$insert_explode_array[0]."\",
      \"".$insert_explode_array[1]."\",
      \"".$insert_explode_array[2]."\")";
    array_push($insert_array,$insert_item);
    }
  fclose($handle);

  $query="insert into `sometable` (field1, field2, field3) values ";
  $item_count=count($insert_array);
  for($i=1;$i<=$item_count-1;$i++)
    {
    $query .= array_pop($item_array) .","; // adds items to the insert statement
    }
  $query .= array_pop($item_array); // finishes the insert statement
  }

echo "Query is $query";

I wrote this "blind", so there might be some coding errors here or there. It should be pretty close...

[SFDonovan]

jkurrle wrote:

Well, I am going off of your earlier example where each line ends with "\r". This is a quick and dirty example:

$insert_array=array();
$handle = fopen("c:\\data\\info.txt", "r");

if ($handle) 
  {
  while (!feof($handle)) 
    {
    $buffer = fgets($handle, 4096);
    $insert_explode_array=explode(",",$buffer);
    $insert_item="(\"".$insert_explode_array[0]."\",
      \"".$insert_explode_array[1]."\",
      \"".$insert_explode_array[2]."\")";
    array_push($insert_array,$insert_item);
    }
  fclose($handle);

  $query="insert into `sometable` (field1, field2, field3) values ";
  $item_count=count($insert_array);
  for($i=1;$i<=$item_count-1;$i++)
    {
    $query .= array_pop($item_array) .","; // adds items to the insert statement
    }
  $query .= array_pop($item_array); // finishes the insert statement
  }

echo "Query is $query";

I wrote this "blind", so there might be some coding errors here or there. It should be pretty close...

You have:

$handle = fopen("c:\\data\\info.txt", "r");

and I have:

$uploadFolder = $_SERVER['DOCUMENT_ROOT'] . "/atlas/modules/$modname/uploadcache/";
$fileName = basename($_FILES['file_source']['name']);

How do I create the path with this?

$handle = fopen("$uploadFolder\$fileName", "r");

[SFDonovan]

The error checking here tells me the file exist since no error is displayed. The table is created as well so I know this part of the code is executing.

$handle = fopen("$uploadFolder\$fileName", "r");
if (!$handle) {echo("<p>Error opening file:</p>");}

$table_name = "_tl_session_grade_import";

$sql	= "CREATE TABLE IF NOT EXISTS ".$prefix."$table_name (
  `ReportNumber` int(3) NULL,
  `Student_ID` varchar(9) NULL,
  `Name_Last` varchar(50) NULL, 
  `Name_First` varchar(30) NULL,  

  `Total_RS` tinyint(4) NULL,
  `Total_Percent` tinyint(3) NULL,
  `Percentile` tinyint(3) NULL,
  `Subtest_RS` tinyint(3) NULL,
  `Subtest_Percent` tinyint(3) NULL
  )";    

$result = $db->sql_query($sql);	
if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}

Thanks for you help troubleshooting.

Here is the script with your suggestions in it's entirety:

<?php
/************************************************************************/
/* Academic Team Learning & Assessment System                           */
/* ==========================================                           */
/* Author: Steven Donovan                                               */
/* Web Developer                                                        */
/* Boonshoft School of Medicine                                         */
/* Project Start Date 10/25/2007                                        */
/*                                                                      */
/************************************************************************/
global $admin_file, $db, $prefix;
if(!defined('TL_ADMIN')) { die("Illegal Access Detected!!!"); }
$modname = basename(str_replace("/admin", "", dirname(__FILE__)));
require_once("mainfile.php");
include("header.php");
TLAdminMenu();
Opentable();
echo" <h2 align=\"center\">IRAT CSV import</h2>";
echo " <table width=\"100%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\">";
echo "<tr><b>Steps to import</b></tr>"
  . "<tr><ul>"
  . "<tr><li>Open the Excel File</tr>" 
  . "<tr><li>Remove the first row of header information containing ReportNumber, UID, StudentName, etc.</tr>" 
  . "<tr><li>Save file as a csv file. File/Save As/Choose csv format (Comma Delimited) in dropdown box.</tr>"
  . "</ul>"; 
echo "<form action='".$_server['php_self']."' method='post' enctype=\"multipart/form-data\">"
  . "   <table border=\"1\" align=\"center\">"
  . "     <tr>"
  . "     	<td>Source CSV file to import:</td>"
  . "     	<td rowspan=\"30\" width=\"10px\">&nbsp;</td>"
  . "     	<td><input type=\"file\" name=\"file_source\" id=\"file_source\" class=\"edt\" value=\"$file_source\"></td>"
  . "     </tr>      "
  . "     <tr>"
  . "     	<td colspan=\"3\" align=\"center\"><input type=\"Submit\" name=\"Go\" value=\"Next Step\" onClick=\" var s = document.getElementById('file_source'); if(null != s && '' == s.value) {alert('Define file name'); s.focus(); return false;}\"></td>"
  ."<input type='hidden' name='op' value='TL_CSV_Import'>\n"
  . "<input type='hidden' name='Session_ID' value='$Session_ID'>\n"
  . "     </tr>"
  . "   </table>"
  . " </form>"
 ."";
 Closetable();
if(isset($_POST["Go"]) && ""!=$_POST["Go"]) {

$insert_array=array();
$uploadFolder = $_SERVER['DOCUMENT_ROOT'] . "/atlas/modules/$modname/uploadcache/";
$fileName = basename($_FILES['file_source']['name']);
move_uploaded_file($_FILES['file_source']['tmp_name'], $uploadFolder.$fileName);
$handle = fopen("$uploadFolder\$fileName", "r");
if (!$handle) {echo("<p>Error opening file:</p>");}

$table_name = "_tl_session_grade_import";

$sql	= "CREATE TABLE IF NOT EXISTS ".$prefix."$table_name (
  `ReportNumber` int(3) NULL,
  `Student_ID` varchar(9) NULL,
  `Name_Last` varchar(50) NULL, 
  `Name_First` varchar(30) NULL,  

  `Total_RS` tinyint(4) NULL,
  `Total_Percent` tinyint(3) NULL,
  `Percentile` tinyint(3) NULL,
  `Subtest_RS` tinyint(3) NULL,
  `Subtest_Percent` tinyint(3) NULL
  )";    

$result = $db->sql_query($sql);	
if (!$result) {echo("<p>Error performing query: " . mysql_error() . "</p>");}	

$checktable = $db->sql_query("SELECT * FROM ".$prefix."$table_name");
$table_total = $db->sql_numrows($checktable);
//We already have records in table
if ($table_total > 0) {
echo "<form action='".$_server['php_self']."' method='post'";
OpenTable();
echo"<tr><td>There are records already loaded from a previous file (TL Session).</td></tr>";
echo"<tr><td>You can view these IRAT grades <a href='".$admin_file.".php?op=TLImportIratView&Session_ID=$Session_ID'>Here!</a></td></tr>";
echo"<tr><td>Or click Continue to empty this table and start over.</td></tr>";
echo"<tr>"
  . "<td align=\"center\"><input type=\"Submit\" name=\"TruncateTable\" value=\"Continue\"></td>"  

  . "</tr>";
Closetable();
echo"</form>";
die();
}

if ($handle) {
  while (!feof($handle))  {
    $buffer = fgets($handle, 4096);
    $insert_explode_array=explode(",",$buffer);
    $insert_item="(\"".$insert_explode_array[0]."\",
      \"".$insert_explode_array[1]."\",
      \"".$insert_explode_array[2]."\")";
    array_push($insert_array,$insert_item);
    }
  fclose($handle);  

  $query="INSERT INTO ".$prefix."$table_name ('ReportNumber', 'Student_ID', 'Name_Last', 'Name_First', 'Total_RS', 'Total_Percent', 'Percentile', 'Subtest_RS', 'Subtest_Percent') VALUES ";
  $result = $db->sql_query($query);	
  $item_count=count($insert_array);
  for($i=1;$i<=$item_count-1;$i++)
    {
    $query .= array_pop($item_array) .","; // adds items to the insert statement
    }
  $query .= array_pop($item_array); // finishes the insert statement
  }

  if(mysql_affected_rows() != count($item_count)) {
  die('MySQL Insert failed to insert all entries.');
}else{
  header("Location: ".$admin_file.".php?op=TLScantronIratInsert&Session_ID=$Session_ID");
  }
  }

?>

[jkurrle]

So is everything working now, or are you still getting errors?

[SFDonovan]

It is not importing anything. I can browse the import table but I get...

MySQL returned an empty result set (i.e. zero rows).

[jkurrle]

I looked at the code and it seemed to have potential problems. I made a few changes below that might help. Take a look and see if it's useful...

if ($handle) 
  {
  while (!feof($handle))  

    {
    $buffer = fgets($handle, 4096);
    $insert_explode_array=explode(",",$buffer);
    $insert_item="(\"".$insert_explode_array[0]."\",
      \"".$insert_explode_array[1]."\",
      \"".$insert_explode_array[2]."\",
      \"".$insert_explode_array[3]."\",
      \"".$insert_explode_array[4]."\",
      \"".$insert_explode_array[5]."\",
      \"".$insert_explode_array[6]."\",
      \"".$insert_explode_array[7]."\",
      \"".$insert_explode_array[8]."\")";
    array_push($insert_array,$insert_item);
    } //end while
  fclose($handle);  

  $query="INSERT INTO ".$prefix."$table_name ('ReportNumber', 'Student_ID', 'Name_Last', 'Name_First', 'Total_RS', 'Total_Percent', 'Percentile', 'Subtest_RS', 'Subtest_Percent') VALUES ";
  $item_count=count($insert_array);
  for($i=1;$i<=$item_count-1;$i++)
    {
    $query .= array_pop($item_array) .","; // adds items to the insert statement
    }
  $query .= array_pop($item_array); // finishes the insert statement
  } // end if($handle)
  $result = $db->sql_query($query);    

if (mysql_affected_rows() != count($item_count))
  {
  die('MySQL Insert failed to insert all entries.');
  }
else
  {
  header("Location: ".$admin_file.".php?op=TLScantronIratInsert&Session_ID=$Session_ID");
  }

[jkurrle]

slight code change to above code to add in extra field.

[jkurrle]

It looks like the code will need to be reworked some more. I'll take a look at it later tonight.