importare dati da database Access in database Mysql
<?php
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
function echo_notice ($s) { echo "#NOTICE : $s\n"; }
function echo_warning($s) { echo "#WARNING: $s\n"; }
function echo_error ($s) { echo "#ERROR : $s\n"; }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
function sql_insert($t,$val=array()) {
return "INSERT INTO $t SET ".sql_sequence_val($val).";\n";
}
function sql_update($t,$val=array(), $where='') {
if($where!='') { $where=' WHERE '.$where; }
return "UPDATE $t SET ".sql_sequence_val($val)." $where;\n";
}
//! ritorna una stringa nella forma a=1,b=2,...
//! da un array associativo nella forma 'a'=>1,'b'=>2
function sql_sequence_val($val) {
$str="";
$a_regs = array();
$field_sep = ",";
foreach($val as $k=>$v) {
if( !empty($v) ) {
if (is_string($v)) {
// non quotare se sembra una funzione come ad esempio now() o sum()
// da aggiornare con l'uso di una reg exp?
//if( substr($v,-1) != ')' ){
if( !ereg('[a-z]+\(([a-z]*)\)', $v, $a_regs) ) {
$v = "'".mysql_escape_string($v)."'";
}
}
$str.=$k.'='.$v.$field_sep;
}
}
return substr($str,0,-strlen($field_sep) );//tolgo l'ultima ",\n"
}
/*
* le sql vengono stampate in files che vengono caricate manualmente nel db per facilitare il debug
*
*
*/
function mysql_open($h, $u, $p, $db) {
$l = mysql_connect($h, $u, $p) or die(mysql_error());
mysql_select_db($db, $l);
if( $l ) {
echo_notice("connected");
return $l;
} else {
echo_error("!connected");
return null;
}
}
function mysql_select_one($sql) {
if( $rs = mysql_query($sql) ) {
if( mysql_numrows( $rs ) && $a = mysql_fetch_array($rs) ) {
return $a[0];
}
} else {
die(echo_error(__LINE__."\n\n".$sql."\n\n".mysql_error()));
}
return false;
}
function mysql_insert($t, $a, &$id) {
$id = 0;
$sql = sql_insert($t, $a)."\n";
echo $sql;// dbg
if( mysql_query($sql)) {
$id = mysql_insert_id();
return true;
} else {
die(echo_error(__LINE__."\n\n".$sql."\n\n".mysql_error()));
}
}
function mysql_update($t, $a, $where='', &$updated_lines) {
$updated_lines = 0;
$sql = sql_update($t, $a, $where)."\n";
echo $sql;// dbg
if( mysql_query($sql)) {
$updated_lines=mysql_affected_rows();
return true;
} else {
die("esecuzione della qry [$sql] genera l'errore: ".mysql_error() );
}
}
function mysql_truncate($t) {
return mysql_query("TRUNCATE TABLE $t") or die(__LINE__.' '.mysql_error() );
}
//------------------------------------------------------------------------------
// access functions
//------------------------------------------------------------------------------
function map_table_sql($access_table, $mysql_table, $fields_map, $opt=array() ) {
$sql = isset( $opt['custom_sql'] ) ? $opt['custom_sql'] : "select * from $access_table" ;
$custom_values = isset( $opt['custom_values'] ) ? $opt['custom_values'] : array() ;
$exc = odbc_exec($GLOBALS['access_con'], $sql);
if($exc) {
while (odbc_fetch_row($exc)) {
foreach( $fields_map as $acc_field => $mysql_field ) {
// ritorna false su errore
if ( $v = odbc_result($exc, $acc_field) ) {
$a[$mysql_field] = $v;
}
}
foreach( $custom_values as $field => $value ) {
$a[$field] = $value;
}
echo sql_insert($mysql_table, $a);
}
}
}
function ls_tables() {
$result = odbc_tables($GLOBALS['access_con']);
$a = array();
while (odbc_fetch_row($result)) {
if(odbc_result($result,"TABLE_TYPE")=="TABLE")
$a[] =odbc_result($result,"TABLE_NAME");
}
return $a ;
}
function ls_columns($table_name) {
$field_names = array();
$cols = odbc_exec($GLOBALS['access_con'], 'select * from `'.$table_name.'` where 1=2');
$ncols = odbc_num_fields($cols);
for ($n=1; $n<=$ncols; $n++) {
$field_names[] = odbc_field_name($cols, $n);
}
return $field_names;
}
//------------------------------------------------------------------------------
// actions
//------------------------------------------------------------------------------
function act_AnaLingue() {
map_table_sql('AnaLingue', 'lang', array(
'CodLingua' => 'lang_id',
'Nome' => 'name'
));
}
// ... specificare le altre azioni
function act_list() {
$tables = ls_tables();
foreach( $tables as $table_name ) {
echo "campi della tabella $table_name \n";
$a_col = ls_columns($table_name) ;
foreach($a_col as $col) {
echo " $col\n";
}
}
}
//------------------------------------------------------------------------------
// main
//------------------------------------------------------------------------------
set_time_limit(0);
// $dbq = str_replace("/", "\\", "S:\\eco.mdb"); // settata dal wrapper it o fr
if (!file_exists($dbq)) { echo "Crap!<br />No such file as $dbq"; }
$access_con = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbq",
"ADODB.Connection", ""/*password*/, "SQL_CUR_USE_ODBC");
$result = odbc_tables($access_con);
if( $_SERVER["argc"] < 2 ) {
$a_actions = array(
'truncate',
'AnaLingue',
'AnaContinenti',
'AnaNazioni',
'AnaProvincie',
'AnaCap',
'AnaLocalita',
'AnaZone',
'subscriber',
'subscription',
'cleanup',
'list',
'populate_zone'
);
die( "indica una delle seguenti azioni: \n". implode(",\n", $a_actions) );
} else {
$action = isset($_SERVER["argv"][1]) ? $_SERVER["argv"][1] : 'All';
$f = "act_$action";
$f();
}