Steps involve in converting Postgres data to Mysql data
For this script 2 databases(postgres,mysql) must have identical relations
Here i am using “relname like ‘tm%’ and relkind=’r'” i.e tm is the prefix for table name(u can use your own prefix) ans “r” refer to relations.
Here i am using adodb package for connecting databases.
please see the script
define(“POS_DATABASE_IP”,”YOUR POSTGRES IP”);
define(“POS_DATABASE_UID”,”POSTGRES USERID”);
define(“POS_DATABASE_PWD”,”PASSWORD”);
define(‘POS_DATABASE_NAME’,'DBNAME’);define(“MY_DATABASE_IP”,”YOUR MYSQL IP”);
define(“MY_DATABASE_UID”,”MYSQL USERID”);
define(“MY_DATABASE_PWD”,”PASSWORD”);
define(‘MY_DATABASE_NAME’,'DBNAME’);include($glb_folderlevel.”adodb/adodb.inc.php”);
include($glb_folderlevel.’adodb/adodb-errorhandler.inc.php’);
//define(‘ADODB_ERROR_LOG_TYPE’,3);
//$error_path=$_SERVER["DOCUMENT_ROOT"].APP_FOLDER.’/sql_errors/sql_error.log’;
//define(‘ADODB_ERROR_LOG_DEST’,$error_path);$glb_conn = &ADONewConnection(‘postgres’);
Connect(POS_DATABASE_IP,POS_DATABASE_UID,POS_DATABASE_PWD,POS_DATABASE_NAME);
$glb_conn->SetFetchMode(ADODB_FETCH_ASSOC);$my_glb_conn = &ADONewConnection(‘mysql’);
$my_glb_conn->Connect(MY_DATABASE_IP,MY_DATABASE_UID,MY_DATABASE_PWD,MY_DATABASE_NAME);
$my_glb_conn->SetFetchMode(ADODB_FETCH_ASSOC);//getting all the user created table name
$str_query=” SELECT relname FROM pg_class where relname like ‘tm%’ and relkind=’r'”;
$rs_query=$glb_conn->Execute($str_query);
if($rs_query && $rs_query->RecordCount()>0)
{
while(!$rs_query->EOF)
{
$arr=$rs_query->FetchRow();
$tablename=$arr[relname];
//echo $tablename;
$str_get_query=”select * from $tablename”;
$rs_get_query=$glb_conn->Execute($str_get_query);
$tmp_int_columncount=$rs_get_query->FieldCount();
$tmp_int_columnname=array();
for($tmp_int_we=0;$tmp_int_weFieldCount();$tmp_int_we++){
$tmp_obj=$rs_get_query->FetchField($tmp_int_we);
$tmp_int_columnname[$tmp_obj->name]=$tmp_obj->name;
}
if($rs_get_query && $rs_get_query->RecordCount()>0)
{
while(!$rs_get_query->EOF)
{
unset($arr);
$str_column=”";
$str_values=”";
$arr=$rs_get_query->FetchRow();
foreach($tmp_int_columnname as $key => $value)
{
if($str_column!=”")
$str_column.=”,”;
$str_column.=$key;if($str_values!=”")
$str_values.=”,”;
$str_values.=”‘”.addslashes($arr[$key]).”‘”;
}
//chk before insert
//insert query
$str_insert=”insert into $tablename($str_column)values($str_values)”;
echo $str_insert;
echo “\n”;
$rs_insert=$my_glb_conn->Execute($str_insert);
}
}}
}
?>
