AAVIK KUMAR PRODUCTION
Php+MySql Extract Transform Load System
step by step tutorial on extract transform load system with full code
-------------
index.php
<html>
<style>
body {background-image:url("bg.jpg");}
.button-secondary {
background: rgb(66, 184, 221);
border-radius: 4px;
text-shadow: 0 1px 1px rgba(0, 0, 0, 0.2);
}
</style>
<script>
function val()
{
window.location.href="clear.php";
}
</script>
<body>
<center><h1><font color="red">Welcone to ETL System</font></h1></center>
<div style='float:right'><input type='button' value='Clear' onclick='val()'></div>
<div style="border :1px solid black ;height:auto;width:500px;margin-left:400px">
<center><h2>This part is of extraction</h2><br></center>
<form action="extract.php" method="post"
enctype="multipart/form-data">
<table><tr><td><label for="file"><h2>Filename:</h2></label></td>
<td><input type="file" name="file" id="file"></td></tr></table>
<center><input type="submit" name="submit" class='button-secondary' value="Submit"></center>
</form>
</div>
</body>
</html>
-----------------------------------------------------------------------
extract.php
<style>
body {background-image:url("bg.jpg");}
.button-secondary {
background: rgb(66, 184, 221);
border-radius: 4px;
text-shadow: 0 1px 1px rgba(0, 0, 0, 0.2);
}
</style>
<script>
function val()
{
window.location.href="clear.php";
}
</script>
<body>
<form action='transfer.php' method='post'>
<div align='center'>
<font size='6'color='red'>Information regarding type, size,storage of file:</font><br>
<div style='float:right'><input type='button' value='Clear' onclick='val()'></div>
<?php
if ($_FILES["file"]["error"] > 0)
{
echo "Error: " . $_FILES["file"]["error"] . "<br>";
}
else
{
echo "<table><tr><td>";
echo "Upload: </td><td>" . $_FILES["file"]["name"] . "</td></tr>";
echo "<tr><td>Type: </td><td>" . $_FILES["file"]["type"] . "</td></tr>";
echo "<tr><td>Size: </td><td>" . ($_FILES["file"]["size"] / 1024) . " kB</td></tr>";
echo "<tr><td>Stored in: </td><td>" . $_FILES["file"]["tmp_name"]. "</td></tr></table>";
}
if(file_exists("upload/".$_FILES["file"]["name"]))
{
echo $_FILES["file"]["name"]."already exists.</br>";
}
else
{
move_uploaded_file($_FILES["file"]["tmp_name"],$_FILES["file"]["name"]);
}
echo "</div><hr>";
$h=@fopen($_FILES["file"]["name"],"r");
$con=mysql_connect('localhost','root','');
mysql_select_db('ajay',$con);
echo "<div align='center'>";
if(!$con)
{
die("could not found");
}
else
{
echo "<font size='4' color='red'>CONNECTED TO DATABASE.</font></br>";
}
while (!feof($h))
{
$str=fgets($h,4096);//read a line
list($a,$b,$c)=(explode("-",$str));//seperate a string by means of |
echo $a."-".$b."-".$c."</br>";
$sql="INSERT into db_data(`fname`,`lname`,`phone_number`) VALUES('$a','$b','$c')";
$result=mysql_query($sql,$con);
}
fclose($h);
echo "DATABASE UPDATED.<br/>";
mysql_close($con);
?>
<br>
<input type='submit' class='button-secondary'value='Click here to Transfer'>
</div></form>
</body>
------------------------------------------------------------------------
transform.php
<style>
body {background-image:url("bg.jpg");}
.button-secondary {
background: rgb(66, 184, 221);
border-radius: 4px;
text-shadow: 0 1px 1px rgba(0, 0, 0, 0.2);
}
</style>
<body>
<div align='center'>
<script>
function val()
{
//alert("hi");
window.location.href="load.php";
}
function val1()
{
window.location.href="clear.php";
}
</script>
<?php
$h=fopen("text.txt","r");
$con=mysql_connect('localhost','root','');
mysql_select_db('ajay',$con);
if(!$con)
{
die("could not found");
}
else
{
echo "CONNECTED TO DATABASE.</br>";
}
while (!feof($h))
{
$str=fgets($h,4096);//read a line
list($a,$b,$c)=(explode("-",$str));//seperate a string by means of |
echo $a."-".$b."-".$c."</br>";
$ch=strlen($c);
if($ch==10)
{
//echo $c."<br>";
$s=mysql_query("INSERT into db_table3(`fname`,`lname`,`phone_number`) VALUES('$a','$b','$c')");
$r=mysql_query($s,$con);
}
else{
$ph="022".$c."<br>";
//echo $ph;
$sq=mysql_query("INSERT into db_table3(`fname`,`lname`,`phone_number`) VALUES('$a','$b','$ph')");
$re=mysql_query($sq,$con);
}
}
fclose($h);
$sql1="select * from `db_table1`";
$res=mysql_query($sql1,$con);
while($row=mysql_fetch_array($res))
{
$fname=$row['fname'];
$lname=$row['lname'];
$phone=$row['phone_number'];
echo $fname." ". $lname." ".$phone."<br>";
$phone_number=strlen($phone);
if($phone_number=='10')
{
$s="INSERT into db_table3(`fname`,`lname`,`phone_number`) VALUES('$fname','$lname','$phone')";
$r=mysql_query($s,$con);
}
else{
$p="022".$phone;
$sq="INSERT into db_table3(`fname`,`lname`,`phone_number`) VALUES('$fname','$lname','$p')";
$re=mysql_query($sq,$con);
}
}
?>
<br>
<input type='button' class='button-secondary' value='Click here to Load' onclick='val()'>
<input type='button' value='Clear' onclick='val1()'>
</div>
</body>
--------------------------------------------------------------------------------------
load.php
<style>
body {background-image:url("bg.jpg");}
.button-secondary {
background: rgb(66, 184, 221);
border-radius: 4px;
text-shadow: 0 1px 1px rgba(0, 0, 0, 0.2);
}
</style>
<body>
<div style='float:right'><input type='button' value='Clear' onclick='val()'></div>
<script>
function val()
{
window.location.href="clear.php";
}
</script>
<?php
$con=mysql_connect('localhost','root','');
mysql_select_db('ajay',$con);
$sql="select * from `db_table3`";
$res=mysql_query($sql,$con);
echo "<table style='border:1px solid black'>";
while($row=mysql_fetch_array($res))
{
$fname=$row['fname'];
$lname=$row['lname'];
$phone=$row['phone_number'];
echo "<tr><td>".$fname."</td><td>".$lname."</td><td>".$phone."</td></tr>";
}
echo "</table>";
?>
</body>
Download full source code extract transform load system project
