Use Case - To upload CSV data to Oracle and MySQL
Code as below:
<?php
error_reporting(E_ALL ^ E_WARNING);
set_time_limit(500);
function generateTableFromResult($result) {
$html = "<table>";
while($row = mysqli_fetch_array($result)) {
foreach($row as $column => $value) {
$html.="<tr><th>".$column."</th><td>".$value."</td></tr>";
}
}
$html.="</table>";
return $html;
}
echo "
<a href='./MyStock/Latest5Days.php'>Last 5 days data Check:</a>
<br>
<br>
";
if(isset($_POST['submit']))
{
$fname = $_FILES['sel_file']['name'];
echo 'Load File: '.$fname.' ';
$chk_ext = explode(".",$fname);
if(strtolower(end($chk_ext)) == "csv")
{
$filename = $_FILES['sel_file']['tmp_name'];
$handle = fopen($filename, "r");
$handle2 = fopen($filename, "r");
set_time_limit(0);
$conn = oci_connect("repl1", "dilip", "//localhost/ndh");
//$mysql_conn = mysqli_connect('localhost', 'XXXX', 'XXXX', '');
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE)
{
if ( $data[1] =='EQ') {
$sql = "INSERT into stock(symbol,price_on,open,high,low,close,volume,TOTTRDVAL)
values('$data[0]','$data[10]',$data[2],$data[3],$data[4],$data[5],$data[8],$data[9])";
//$obj->db_query($sql);
$result=oci_parse($conn,$sql); oci_execute($result);
}
}
fclose($handle);
//mysql code to upload
$mysql_conn = mysqli_connect('localhost', 'XXXX', 'XXXX', 'stock');
// Check connection
if ($mysql_conn->connect_error) {
die("Connection failed: " . $mysql_conn->connect_error);
}
echo "Connected successfully";
$bug = 0;
/*while (($data = fgetcsv($handle2, 10000, ",")) !== FALSE)
{
$bug++ ;
if($data[0] !=''){
$price_on = "str_to_date(".$data[10].",'%d-%b-%Y')";
$sql = "INSERT into stock(symbol,price_on,open,high,low,close,volume,TOTTRDVAL)
values('$data[0]','$data[10]',$data[2],$data[3],$data[4],$data[5],$data[8],$data[9])";
STR_TO_DATE(Mydata, '%m.%d.%Y %H:%i:%s')
$result = mysqli_query($mysql_conn, $sql);
echo $result;
}
}*/
while (($getData = fgetcsv($handle2, 10000, ",")) !== FALSE)
{
$bug++ ;
if($getData[0] !='' and $getData[1] =='EQ'){
//$sql = "INSERT into customers (card_number,first_name,phone)
// values ('".$getData[0]."','".$getData[1]."','".$getData[2]."')";
$price_on = "str_to_date('".$getData[10]."','%d-%b-%Y')";
$sql = "INSERT into stock (symbol,price_on,open,high,low,close,volume,TOTTRDVAL)
values('".$getData[0]."',".$price_on.",'".$getData[2]."','".$getData[3]."','".$getData[4]."','".$getData[5]."','".$getData[8]."','".$getData[9]."')";
//echo $sql;
$result = mysqli_query($mysql_conn, $sql);
}
}
if($bug > 0){
echo "<h1>CSV file upload successfully in Mysql DB as well.....!</h1>";
}
fclose($handle2);
mysqli_close($mysql_conn);
//}
// //}
//
$sql= "select price_on||' Count Rows # '|| cnt insert_status from (select trunc(price_on) price_on , count(1) cnt from stock group by trunc(price_on) order by 1 desc ) where rownum<=1";
$result = oci_parse ($conn, $sql);
oci_execute($result);
// Fetch results
echo "<!DOCTYPE html>
<html>
<head>
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 60%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
</style>
</head>
<body>
<h2>Insert into Table </h2>";
echo "<table><tr><td> Import Successful! Inserted # of rows: $data </td><td>" ;
while ($data = oci_fetch_assoc($result)) {
print_r($data);
}
echo "</td></tr></table>";
// oci_free_statement($sql);
//oci_close($conn);
oci_close($conn);
}
else
{
echo "Import Failed!";
oci_close($conn);
exit;
}
//$stmt = oci_parse($conn,'BEGIN SP_GET_MY_DATA(:POP, :SEG, :DUR, :VIEW, :PAGE, :OUTPUT_CUR); END;');
/*
oci_bind_by_name($stmt,':POP',$pop);
oci_bind_by_name($stmt,':SEG',$seg);
oci_bind_by_name($stmt,':DUR',$dur);
oci_bind_by_name($stmt,':VIEW',$view);
oci_bind_by_name($stmt,':PAGE',$page);
*/
// Declare your cursor
//$stmt = oci_parse($conn,'BEGIN Build_Return_Table(); END;');
//$OUTPUT_CUR = oci_new_cursor($conn);
//oci_bind_by_name($stmt,":OUTPUT_CUR", $OUTPUT_CUR, -1, OCI_B_CURSOR);
// Execute statement
//oci_execute($stmt);
// Execute the cursor
//oci_execute($OUTPUT_CUR);
// Fetch results
//while ($data = oci_fetch_assoc($OUTPUT_CUR)) {
// print_r($data);
//}
//$stmt = oci_parse($conn,'BEGIN Build_Return_Table(); END;');
//oci_execute($stmt);
}
?>
<!DOCTYPE html>
<html lang="es-US">
<body>
<h1>Import CSV file to Table</h1>
<form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post' enctype="multipart/form-data">
Import Data : <input type='file' name='sel_file' size='20'>
<input type='submit' name='submit' value='Import'>
</form>
</body>
</html>
error_reporting(E_ALL ^ E_WARNING);
set_time_limit(500);
function generateTableFromResult($result) {
$html = "<table>";
while($row = mysqli_fetch_array($result)) {
foreach($row as $column => $value) {
$html.="<tr><th>".$column."</th><td>".$value."</td></tr>";
}
}
$html.="</table>";
return $html;
}
echo "
<a href='./MyStock/Latest5Days.php'>Last 5 days data Check:</a>
<br>
<br>
";
if(isset($_POST['submit']))
{
$fname = $_FILES['sel_file']['name'];
echo 'Load File: '.$fname.' ';
$chk_ext = explode(".",$fname);
if(strtolower(end($chk_ext)) == "csv")
{
$filename = $_FILES['sel_file']['tmp_name'];
$handle = fopen($filename, "r");
$handle2 = fopen($filename, "r");
set_time_limit(0);
$conn = oci_connect("repl1", "dilip", "//localhost/ndh");
//$mysql_conn = mysqli_connect('localhost', 'XXXX', 'XXXX', '');
while (($data = fgetcsv($handle, 10000, ",")) !== FALSE)
{
if ( $data[1] =='EQ') {
$sql = "INSERT into stock(symbol,price_on,open,high,low,close,volume,TOTTRDVAL)
values('$data[0]','$data[10]',$data[2],$data[3],$data[4],$data[5],$data[8],$data[9])";
//$obj->db_query($sql);
$result=oci_parse($conn,$sql); oci_execute($result);
}
}
fclose($handle);
//mysql code to upload
$mysql_conn = mysqli_connect('localhost', 'XXXX', 'XXXX', 'stock');
// Check connection
if ($mysql_conn->connect_error) {
die("Connection failed: " . $mysql_conn->connect_error);
}
echo "Connected successfully";
$bug = 0;
/*while (($data = fgetcsv($handle2, 10000, ",")) !== FALSE)
{
$bug++ ;
if($data[0] !=''){
$price_on = "str_to_date(".$data[10].",'%d-%b-%Y')";
$sql = "INSERT into stock(symbol,price_on,open,high,low,close,volume,TOTTRDVAL)
values('$data[0]','$data[10]',$data[2],$data[3],$data[4],$data[5],$data[8],$data[9])";
STR_TO_DATE(Mydata, '%m.%d.%Y %H:%i:%s')
$result = mysqli_query($mysql_conn, $sql);
echo $result;
}
}*/
while (($getData = fgetcsv($handle2, 10000, ",")) !== FALSE)
{
$bug++ ;
if($getData[0] !='' and $getData[1] =='EQ'){
//$sql = "INSERT into customers (card_number,first_name,phone)
// values ('".$getData[0]."','".$getData[1]."','".$getData[2]."')";
$price_on = "str_to_date('".$getData[10]."','%d-%b-%Y')";
$sql = "INSERT into stock (symbol,price_on,open,high,low,close,volume,TOTTRDVAL)
values('".$getData[0]."',".$price_on.",'".$getData[2]."','".$getData[3]."','".$getData[4]."','".$getData[5]."','".$getData[8]."','".$getData[9]."')";
//echo $sql;
$result = mysqli_query($mysql_conn, $sql);
}
}
if($bug > 0){
echo "<h1>CSV file upload successfully in Mysql DB as well.....!</h1>";
}
fclose($handle2);
mysqli_close($mysql_conn);
//}
// //}
//
$sql= "select price_on||' Count Rows # '|| cnt insert_status from (select trunc(price_on) price_on , count(1) cnt from stock group by trunc(price_on) order by 1 desc ) where rownum<=1";
$result = oci_parse ($conn, $sql);
oci_execute($result);
// Fetch results
echo "<!DOCTYPE html>
<html>
<head>
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 60%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
</style>
</head>
<body>
<h2>Insert into Table </h2>";
echo "<table><tr><td> Import Successful! Inserted # of rows: $data </td><td>" ;
while ($data = oci_fetch_assoc($result)) {
print_r($data);
}
echo "</td></tr></table>";
// oci_free_statement($sql);
//oci_close($conn);
oci_close($conn);
}
else
{
echo "Import Failed!";
oci_close($conn);
exit;
}
//$stmt = oci_parse($conn,'BEGIN SP_GET_MY_DATA(:POP, :SEG, :DUR, :VIEW, :PAGE, :OUTPUT_CUR); END;');
/*
oci_bind_by_name($stmt,':POP',$pop);
oci_bind_by_name($stmt,':SEG',$seg);
oci_bind_by_name($stmt,':DUR',$dur);
oci_bind_by_name($stmt,':VIEW',$view);
oci_bind_by_name($stmt,':PAGE',$page);
*/
// Declare your cursor
//$stmt = oci_parse($conn,'BEGIN Build_Return_Table(); END;');
//$OUTPUT_CUR = oci_new_cursor($conn);
//oci_bind_by_name($stmt,":OUTPUT_CUR", $OUTPUT_CUR, -1, OCI_B_CURSOR);
// Execute statement
//oci_execute($stmt);
// Execute the cursor
//oci_execute($OUTPUT_CUR);
// Fetch results
//while ($data = oci_fetch_assoc($OUTPUT_CUR)) {
// print_r($data);
//}
//$stmt = oci_parse($conn,'BEGIN Build_Return_Table(); END;');
//oci_execute($stmt);
}
?>
<!DOCTYPE html>
<html lang="es-US">
<body>
<h1>Import CSV file to Table</h1>
<form action='<?php echo $_SERVER["PHP_SELF"];?>' method='post' enctype="multipart/form-data">
Import Data : <input type='file' name='sel_file' size='20'>
<input type='submit' name='submit' value='Import'>
</form>
</body>
</html>
