Category: Database

<!--p 
 
/** ~~~~~~~ CSV IMPORT TO DATABASE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  *
  * This script reads in a comma delimited text file (.csv) and creates
  * a table in the database from it.
  *
  * The table will be emptied to start with if it is already in the database.
  *
  * Have not yet tried this with a .csv file that did -not- have column
  * headers as the first row
  *
  * Requirements: the csv file needs to be the same name as the table,
  *               comma separated with the columns in the same order as the table,
  *               and in the same dir as this script
  *
  * It may be necessary to run this script repeatedly to correct minor data errors in the CSV.
  *
  * TO FIX: Doesn't carry on as intended when it hits a data error, perhaps because
  *    of 'or die' code below. Maybe doesn't need to be fixed.
  *
*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  */
 
include("../includes/connect-string.inc.php");  
 
$table          = array( 'districtmembers'); // assign the tables that you want to import to to the table array
$columnheadings = 1; // 1 if first row of files is column headings, 0 if not and first line should be read as a record
$emailaddress   = "test@nosuchdomaincom"; // contains the email address you want the results sent to
$emailfrom      = "test@nosuchdomaincom"; // contains the email address that will show in the from line
$localtime      = gmdate('F j, Y, g:i:s a', time() + (-18000));  // current local time
$runtime        = (date("d M Y H:i:s Z ")); // set to the date and time the script was run 
 
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
// perform the required operations for every table listed in the table array
foreach ($table as $tablename) { 
 
  $deleterecords = "TRUNCATE TABLE `$tablename`"; # empty the table of its current records
  mysql_query($deleterecords) or die("Query failed: $deleterecords = " . mysql_error());  
 
  $pass = 0; # intialize counters for successful record imports
  $fail = 0; # intialize counters for failed record imports 

  $file_handle = fopen ("$tablename.csv","r");
  while (($row = fgetcsv($file_handle, 1000, ",")) !== false) {
 
    if ($row[0] == "CLUBNAME") {continue;}  // skip the first line which is just the column names
    // ~~~~ clear the last element in the array if it is a null value,      ~~~~~//
    // ~~~~ such null values will be caused by a trailing comma on the line ~~~~~//
    if ($row[count($row) - 1] == "") {
      unset($row[count($row) - 1]);
    } // close if
 
    $insertrecord = "Insert Into `$tablename` Values (\"".implode('", "', $row)."\")";   
 
    mysql_query($insertrecord) or die("
 
Query failed: $insertrecord = " . mysql_error());
      if(mysql_error()) {
           $fail += 1;     # increments if there was an error importing the record
      } else {
          $pass += 1;     # increments if the record was successfully imported
      }
  } // end while
 
  # adds a line to the email message we will send stating how many records were imported
  # and how many records failed for each table
  $message .= "Table $tablename was loaded with data from the .csv file.\n\n   Success=$pass\n   Failure=$fail \n";
} // close foreach
 
echo "
 
Table $table[0] was loaded with data from the file <span style="color:maroon;" mce_style="color: maroon--><strong>$tablename.csv</strong>.";
echo "
 
$localtime.";
echo "
<ul>
	<li>Success: $pass
 
";
echo "</li>
	<li>Failure: $fail</li>
</ul>
";
 
print "
 
".gmdate('F j, Y, g:ia', time() + (-18000))." EST";
 
/**
  * Confirmation Email
  *
  * Some email code ideas from
  * http://www.phpnoise.com/tutorials/12/4
  *
  *
  */
$to = "D7040 Webmaster ";
$from = "D7040 Webmaster ";
$subject = "D7070 DB Update: Table $tablename with $pass records";
$message = "
 
The <span style="color: #0a0000;"><strong>$tablename</strong></span> table in the district database was updated $localtime.
<ul>
	<li>Success: $pass</li>
	<li>Failure: $fail</li>
</ul>
<em>This is an automated message.</em>
 
 ";
$headers  = "From: $from\r\n";
$headers .= "Content-type: text/html\r\n"; 
 
/**
  * Notification in browser of success/failure if notification email.
  *
  */
$success = mail($to, $subject, $message, $headers);
if ($success)
    echo "
 
The confirmation email to <strong>$to</strong> from <strong>$from</strong> was successfully sent";
else
    echo "
 
An error occurred when sending the confirmation email to $to from $from";
 
?&gt;