PHP CSV and Oracle

From CoderLearner
Jump to: navigation, search

Description

In this demonstration, I read the CSV file content and store the CSV content into the Oracle database.

Table

Create TABLE COURSES_ENROLL(
ID NUMBER NOT NULL PRIMARY KEY, 
USID VARCHAR(200), 
NAME VARCHAR(200), 
COURSE VARCHAR(200), 
DESCRIPTION VARCHAR(255), 
SESSIONS VARCHAR(255),
STATUS NUMBER, 
CTIME DATETIME

);

Code

  • Open the CSV file
$my_file = '/home/sophal/enroll_hr/my_enroll.csv';

$file_handler = fopen($my_file, 'r') or exit('can not open the file');

Write SQL statement, and we use Merge to update or Insert based on the record


$sql = "MERGE INTO 
         COURSES_ENROLL P
         USING (SELECT :USID AS USID, :COURSE AS COURSE FROM DUAL)P2
            ON (P2.USID = P.USID AND P2.COURSE = P.COURSE)
        WHEN MATCHED
           THEN
                UPDATE 
                SET P.DESCRIPTION = :DESCRIPTION,
                    P.SESSIONS = :SESSIONS,
                    P.STATUS = :STATUS,
                    P.NAME = :NAME
        WHEN NOT MATCHED
           THEN
               INSERT (ID, USID, NAME, 
                 COURSE, DESCRIPTION, SESSIONS,STATUS, CTIME)
	   VALUES ((SELECT NVL(MAX(ID),0) + 1 FROM COURSES_ENROLL), 
          :USID, 
          :NAME, 
          :COURSE, 
          :DESCRIPTION, 
          :SESSIONS, 
          :STATUS, CURRENT_TIMESTAMP)
";

Database Connection

$conn = oci_connect('user', 'oraclepassw', '//192.161.1.125/csvex'); 

Check whether it is the end of the file.

while (!feof($file_handler)) {

}
  • the fgetcsv() function get the CSV file content and assign the value to the list of variables.
list($usid, $name, $course, $description, $session, $enroll) =
                fgetcsv($file_handler);
  • Check whether the $psid is not equal to "Emplid" and $enroll is not equal to "Enroll Status" and the $psid is not null and the $enroll is null.
if ($psid <> "Emplid" 
          && $enroll <> "Enroll Status" 
          && !empty($psid) && !empty($enroll)) {
  • Save the CSV record to the array for another use
   $bv = array();
        $bv[$count]['USID'] = $usid;
        $bv[$count]['NAME'] = $name;
        $bv[$count]['COURSE'] = $course;
        $bv[$count]['DESCRIPTION'] = $description;
        $bv[$count]['SESSIONS'] = $session;
        $bv[$count]['STATUS'] = $enroll;
  • Binding to variable to the oracle insert statement.
        $stid = oci_parse($conn, $sql);
        oci_bind_by_name($stid, ':USID', $usid);
        oci_bind_by_name($stid, ':NAME', $name);
        oci_bind_by_name($stid, ':COURSE', $course);
        oci_bind_by_name($stid, ':DESCRIPTION', $description);
        oci_bind_by_name($stid, ':SESSIONS', $session);
        oci_bind_by_name($stid, ':STATUS', $enroll);
  • If we can not insert the record or something wrong with the insert statement,

it displays the error message

    if (!$stid) {
            $e = oci_error($conn);
            var_dump($e);
        }
  • If insert statement is failed, print the error and the array record from csv file.
     // display only when fail to insert
        if (!oci_execute($stid)) {
            print_r($bv);
            echo "<br/>";
        }
       
  • Close the connection string and file and free up memory
// need to close the connection
oci_free_statement($stid);
oci_close($conn);
fclose($file_handler);

Completed Code

<?php

$my_file = '/home/sophal/enroll_hr/my_enroll.csv';

$file_handler = fopen($my_file, 'r') or exit('can not open the file');
$count = 0;



$sql = "MERGE INTO 
         COURSES_ENROLL P
         USING (SELECT :USID AS USID, :COURSE AS COURSE FROM DUAL)P2
            ON (P2.USID = P.USID AND P2.COURSE = P.COURSE)
        WHEN MATCHED
           THEN
                UPDATE 
                SET P.DESCRIPTION = :DESCRIPTION,
                    P.SESSIONS = :SESSIONS,
                    P.STATUS = :STATUS,
                    P.NAME = :NAME
        WHEN NOT MATCHED
           THEN
               INSERT (ID, USID, NAME, 
                 COURSE, DESCRIPTION, SESSIONS,STATUS, CTIME)
	   VALUES ((SELECT NVL(MAX(ID),0) + 1 FROM COURSES_ENROLL), 
          :USID, 
          :NAME, 
          :COURSE, 
          :DESCRIPTION, 
          :SESSIONS, 
          :STATUS, CURRENT_TIMESTAMP)
";

$conn = oci_connect('user', 'oraclepassw', '//192.161.1.125/csvex'); 

while (!feof($file_handler)) {
         list($usid, $name, $course, $description, $session, $enroll) =
                fgetcsv($file_handler);
   // this to only insert the csv content, on the csv header
    if ($psid <> "Emplid" 
          && $enroll <> "Enroll Status" 
          && !empty($psid) && !empty($enroll)) {
   // this to save it into array for other use
        $bv = array();
        $bv[$count]['USID'] = $usid;
        $bv[$count]['NAME'] = $name;
        $bv[$count]['COURSE'] = $course;
        $bv[$count]['DESCRIPTION'] = $description;
        $bv[$count]['SESSIONS'] = $session;
        $bv[$count]['STATUS'] = $enroll;
 // insert into the database or update
        $stid = oci_parse($conn, $sql);
        oci_bind_by_name($stid, ':USID', $usid);
        oci_bind_by_name($stid, ':NAME', $name);
        oci_bind_by_name($stid, ':COURSE', $course);
        oci_bind_by_name($stid, ':DESCRIPTION', $description);
        oci_bind_by_name($stid, ':SESSIONS', $session);
        oci_bind_by_name($stid, ':STATUS', $enroll);

        if (!$stid) {
            $e = oci_error($conn);
            var_dump($e);
        }
     // display only when fail to insert
        if (!oci_execute($stid)) {
            print_r($bv);
            echo "<br/>";
        }
       
        $count++;
    }
}
// need to close the connection
oci_free_statement($stid);
oci_close($conn);
fclose($file_handler);



if ($count > 0) {
    echo 'done';
} else {
    echo 'error';
}
?>