Saturday, March 17, 2012

PHP : How to import DBF table into MySQL database



I am currently working on a web project where requires a feature to import the DBF's table(s) into MySQL database.

After some experiments, i found a powerful PHP class named 'dbf_class' written by Mr. Faro K. Rasyid and with the help of Mr. Eko Wahyudiharto (thanx dude), I finally succeeded to create it.

Basically, the process is fairly easy. As the DBF file uploaded, do a loop to read records line by line, and also doing the inserting into MySQL database.

Here's the script (on PHP) (of course, download the dbf_class first, i put it at /lib directory) :

The script for index.php :
<html>
<head>
<title>Mulia Web</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<h2>Upload File DBF</h2>
<form action="upload.php" method="post" enctype="multipart/form-data" name="file_upload_form" id="file_upload_form">
<input type="file" name="filenya">
<input type="submit" name="X" value="Submit">
<iframe id="upload_target" name="upload_target" src="" style="width:0;height:0;border:0px solid #fff;"></iframe>
</form>
</body>
</html>

And here's the script for upload.php
<?
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASS", "123456");
define("DB_NAME", "muliaweb");
$link=mysql_connect(DB_HOST,DB_USER,DB_PASS);
mysql_select_db(DB_NAME);
$base_path="http://localhost/dbase";
mysql_query("SET time_zone='Asia/Jakarta'");

if (isset($_POST['X']))
if ($_FILES['filenya']['size']>150000)
    {
    echo "<script>alert('File tidak boleh melebihi 150Kb')</script>";
    }
else
    {
    $thefile = '';
    $name = $_FILES['filenya']['name'];
   
    if (move_uploaded_file($_FILES['filenya']['tmp_name'],"dbf/$name")) $thefile = 'dbf/'.$name;
    include('./lib/dbf_class.php');
    $timer = new timerClass();
    $timer ->start();
    $dbf = new dbf_class($dir.$thefile);
    $num_rec=$dbf->dbf_num_rec;
    $field_num=$dbf->dbf_num_field;
    $endexct = $timer->end();
   
    for($i=0; $i<$num_rec; $i++)
        {
        if ($row = $dbf->getRow($i))
            {
            $perintah="";
            for($j=0; $j<$field_num; $j++)
                {
                if ($dbf->dbf_names[$j]['type']=='N')
                    $perintah = $perintah . $row[$j] . ",";
                else if ($dbf->dbf_names[$j]['type']=='C')
                    $perintah = $perintah . "'" . $row[$j] . "',";
                else if ($dbf->dbf_names[$j]['type']=='D')
                    $perintah = $perintah . "date('" . $row[$j] . "'),";
                }
                $pjg_query= strlen($perintah);
                $perintah = "INSERT INTO temp_order VALUES (" . substr($perintah, 0, $pjg_query-1) . ")";
                $hasil=mysql_query($perintah) or die ("<script>alert('data doble!')</script>");
            }
        }
    }
?> 

The next stage, is how to export database from MySQL to the DBF. So stay tuned, I 'll be right back!

.

8 comments:

  1. dimana dbf_class.php nya??

    ReplyDelete
  2. waah baru liat saya link ini, senang bisa membantu

    Faro K Rasyid

    ReplyDelete
    Replies
    1. wow.. dapat kunjungan langsung dari author-nya :-)
      thanx for coming and thanx for the class.. u rock :-)

      Delete
  3. $base_path="http://localhost/dbase";
    itu maksudnya apa gan?
    dabase nya dari mana

    ReplyDelete
  4. gan minta script nya dong yg uda jadi..
    dicoba banyak errornya

    ReplyDelete
  5. halo, apakah boleh dikirmkan file yang sudah jadinya. karena saya sudah coba tidak bisa berjalan

    ReplyDelete

Leave your comments with attitude please. This blog does DOFOLLOW :-)