Consume file . mdb and write to Mysql with PHP

Question

I have a . mdb file, and I need to inject all of its data into Mysql using PHP code. What I do is the following:

// Executa comando via shell para upload do banco no mysql          
$scriptEsquema = shell_exec('mdb-schema uploads/arquivo.mdb mysql | grep -v ^DROP | mysql -u usuario meubancodedados');
$scriptInsert = shell_exec('mdb-export -D "%Y-%m-%d %H:%M:%S" -H -I mysql uploads/arquivo.mdb MINHATABELA | mysql -u usuario meubancodedados');

• On my computer works smoothly, but when passing to a server it no longer works.

Is there another way to do this with PHP? Or something I should do on the server?

Answer 1

If I’m not mistaken, this "shell_exec" command is usually blocked by security on the production server, especially in shared hosts.

I suggest three things:

1/Make sure your hosting releases this command to run on the server.

2/Export the data from this BD to CSV, create PHP routine to read this CSV and then load in BD Mysql.

3/If your hosting is dedicated, then upload this data via SSH, although the chance of releasing the "shell_exec" command will be greater because the server will be dedicated.

So that’s it. I hope it helps somehow the answers.

Answer 2

One way to do this is to make a connection directly to the file:

$db = 'uploads/arquivo.mdb';
$conn_mdb = new COM('ADODB.Connection');
$conn_mdb->Open("DRIVER={Driver do Microsoft Access (*.mdb)}; DBQ=$db");
if (!$conn_mdb) {
   echo "Xiii, faiô a conexão do MDB! ";
   die();
}
$sql = 'SELECT campo1, campo2 FROM tabela';
$res = $conn_mdb->Execute($sql);

$conn_mysqli=mysqli_connect("localhost","my_user","my_password","my_db");
if (mysqli_connect_errno()) {
   echo "Xiii, faiô a conexão do MySQL: " . mysqli_connect_error();
   die();
}

while (!$res->EOF) {
     mysqli_query($conn_mysqli,"INSERT INTO tabela (campo1, campo2)
                                VALUES ('".$res->Fields['campo1']->Value."',
                                        '".$res->Fields['campo2']->Value."')");
    $res->MoveNext();
}

Here you go more about this type of connection.

If your system is Linux, it is best to use a tool called MDBTOOLS.

By the way, if the option is only to export your mdb data to the Mysql database, the program itself Microsoft Access has an option to export data in Excel spreadsheet format (CSV), whose format is compatible with Mysql.

For Windows, there are also other options to export mdb files directly to Mysql. Such as Mysql Front.