<?
$dbftomysql=new convertdata;
$dbftomysql->dbf_to_mysql("sourcedbf","jwcnewstudentlogin","sourcedbf.dbf","test");
class convertdata
{
function mysql_to_dbf($mysql_datasource,$source_table,$destination_dbf)
{
$link=mysql_connect("localhost","root","") or exit("不能链接到数据库");
$operate=mysql_select_db($mysql_datasource,$link);
//连mysql数据源
$rs=mysql_query("SELECT * from $source_table limit 0,1");
$number_of_fields=mysql_num_fields($rs);//取字段数量
for($field_counter=0;$field_counter<$number_of_fields;$field_counter++)
{
$sql_field_type=mysql_field_type($rs,$field_counter);
switch($sql_field_type)
{
case "char":
$dbf_field_name=substr(mysql_field_name($rs_source,$field_counter),0,10);
//自由表的字段名不能超过10,请注意
$dbf_field_len=mysql_field_len($rs,$field_counter);
if($fieldlen>=255)
{
$dbf_field_len--;
}
$dbf_field_type="C";
break;
case "decimal":
$dbf_field_name=substr(mysql_field_name($rs_source,$field_counter),0,10);
//自由表的字段名不能超过10,请注意
$dbf_field_len=mysql_field_len($rs,$field_counter);
if($fieldlen>=255)
{
$dbf_field_len--;
}
$dbf_field_type="C";
break;
case "date":
break;
}
$field[0]=$fieldname;
$field[1]=$fieldtype;
$field[2]=$fieldlen;
}
while($row=mysql_fetch_array($rs))
{
$fieldtype=$row["fieldtype"];
switch($fieldtype)
{
case "char":
$fieldname=substr($row["fieldname"],0,10);
$fieldlen=$row["fieldlen"];
if($fieldlen>=255)
$fieldlen--;
$fieldtype="C";
break;
case "number":
break;
case "date":
break;
}
$field[0]=$fieldname;
$field[1]=$fieldtype;
$field[2]=$fieldlen;
$dbf[]=$field;
}
$d = array_map(null, $dbf);
print_r($d);
$dbname = "2023484sql.dbf";
if (!dbase_create($dbname, $dbf))
print "<strong>Error!</strong>";
else
print "<strong>ok!</strong>";
$dbfp=dbase_open ($dbname,2);
//exit();
$rs=mysql_query("SELECT * from student");
//$rs=mysql_query("SELECT * from classmate");
//$rs=mysql_query("SELECT * from classmessage");
$field=mysql_num_fields($rs);
//for($fieldcounter=0;$fieldcounter<=mysql_num_fields($rs);$fieldcounter++)
//{
while($row = mysql_fetch_array($rs))
{
$counter=0;
for($fieldcounter=0;$fieldcounter<$field;$fieldcounter++)
{
$field_name=mysql_field_name($rs,$fieldcounter);
// $field_type=mysql_field_type($rs,$fieldcounter);
$field_content=$row[$field_name];
$dbffield[$counter]=$field_content;
$counter++;
}
echo "<br>";
dbase_add_record ($dbfp,$dbffield);
}
mysql_close($link);
}
function dbf_to_mysql($odbc_source,$mysql_datasource,$source_dbf,$destination_table)
{
//$odbc_source odbc数据源名称 $mysql_datasource mysql数据库名称
//$source_dbf 待转换的数据表 $destination_table 转换后的数据表
$connection=odbc_connect($odbc_source,"","");
$record_set=odbc_exec($connection,"select * from $source_dbf");
//连odbc数据源
$link=mysql_connect("localhost","root","") or exit("不能链接到数据库");
$operate=mysql_select_db($mysql_datasource,$link);
//连mysql数据源
$number_of_fields=odbc_num_fields ($record_set);
$sql_string="";
for($counter=1;$counter<=$number_of_fields;$counter++)
{
$dbf_field_name=odbc_field_name($record_set,$counter);
$dbf_field_type=odbc_field_type($record_set,$counter);
$dbf_field_len=odbc_field_len($record_set,$counter);
$sql_field_name=$dbf_field_name;
$sql_field_type='';
$sql_field_len='';
$sql_field_precision='';
switch($dbf_field_type)
{
case "C":
$sql_field_type="char";
$sql_field_len="(".strval($dbf_field_len).")";
break;
case "N":
$sql_field_type="decimal";
$sql_field_len=$dbf_field_len+2;
$sql_field_precision=odbc_field_scale($record_set,$counter);;
$sql_field_len="(".strval($sql_field_len).",".strval($sql_field_precision).")";
break;
case "L":
$sql_field_type="char";
$sql_field_len=1;
$sql_field_len="(".strval($dbf_field_len).")";
break;
case "D":
$sql_field_type="date";
break;
case "M":
$sql_field_type="text";
break;
}
$sql_string=$sql_string.$sql_field_name." ".$sql_field_type.$sql_field_len.",";
}
$sql_string=substr($sql_string,0,strlen($sql_string)-1);
$sql_string1="drop table $destination_table";
$operate=mysql_query($sql_string1);
$string="create table $destination_table"."(".$sql_string.")";
$operate=mysql_query($string);
//以上程序完成自动建立mysql表的任务
$field_list="insert into $destination_table(";
$number_of_fields=odbc_num_fields ($record_set);
for($counter=1;$counter<=$number_of_fields;$counter++)
{
$dbf_field_name=odbc_field_name($record_set,$counter);
$field_list=$field_list.$dbf_field_name.',';
}
$field_list=substr($field_list,0,strlen($field_list)-1).')';
while(odbc_fetch_row($record_set))
{
$values_list="values(";
$number_of_fields=odbc_num_fields ($record_set);
for($counter=1;$counter<=$number_of_fields;$counter++)
{
$dbf_field_name=odbc_field_name($record_set,$counter);
$dbf_field_type=odbc_field_type($record_set,$counter);
$values=odbc_result($record_set,$dbf_field_name);
if($dbf_field_type!='N')
{
$values='"'.$values.'"';
}
$values_list=$values_list.$values.',';
}
$values_list=substr($values_list,0,strlen($values_list)-1);
$values_list=$values_list.')';
$sql_string=$field_list.$values_list;
$operate=mysql_query($sql_string);
}
//以上程序段完成记录的自动添加
odbc_close($connection);
mysql_close($link);
}
function mysql_to_xml()
{
require_once "../opendatabase.php";
$link=opendatabase();
echo "starting convert";
echo "<br>";
echo "the source file is ".$sourcefile;
echo "<br>";
echo "the destination file is ".$destinationfile;
echo "<br>";
$rs=mysql_query("SELECT * from $sourcefile;");
$field=mysql_num_fields($rs);
$fp=fopen($destinationfile,'w+');
fputs($fp,"<?xml version='1.0' encoding='??????'?>");
fputs($fp,"\n");
fputs($fp,"<".$sourcefile.">");
fputs($fp,"\n");
$counter=1;
while($row = mysql_fetch_array($rs))
{
$xml_number_begin_tag="<"."id id=\"".strval($counter)."\">";
$xml_number_end_tag="</"."id".">";
fputs($fp,$xml_number_begin_tag);
fputs($fp,"\n");
for($fieldcounter=0;$fieldcounter<$field;$fieldcounter++)
{
$field_name=mysql_field_name($rs,$fieldcounter);
// echo $field_name." ";
$xml_field_name_begin_tag="<".$field_name.">";
$xml_field_name_end_tag="</".$field_name.">";
fputs($fp,$xml_field_name_begin_tag);
fputs($fp,"\n");
$field_content=$row[$field_name];
// echo $field_content;
fputs($fp,$field_content);
fputs($fp,"\n");
fputs($fp,$xml_field_name_end_tag);
fputs($fp,"\n");
// echo "<br>";
}
// echo "<br>";
fputs($fp,$xml_number_end_tag);
fputs($fp,"\n");
$counter++;
}
fputs($fp,"</".$sourcefile.">");
fputs($fp,"\n");
fclose($fp);
mysql_close($link);
echo " convert \"mysql\" database to xml success";
echo "<br>";
}
}
}
?>