PHP PDO MySQL get entries from Access and INSERT into MySQL

Question

My goal here is to replicate a local MS Access database into my MySQL database (using php PDO)

The MS Access database is located on a network shared drive and updates itself with new entries every 6 hours.

In the code below I retrieved the max id number from MySQL table 'production_schedule', then I made an ODBC connection to retrieve all entries from MS ACCESS database that are greater than the max id number.

But now I cannot figure out how to insert these new entries into the MySQL table 'production_schedule'.

Can anyone please help?

<?php

/*USING XAMPP*/

$dsn = "mysql:host=localhost;dbname=qmsdb;charset=utf8";
$uname = "root";
$pword = "";

$db = null;
$limit = 10;
$counter = 0;

while (true) {
try {
    $db = new PDO($dsn, $uname, $pword);
    $db->exec( "SET CHARACTER SET utf8" );
    $db->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC ); 
    $db->setAttribute( PDO::ATTR_PERSISTENT, true );
    break;
}
    catch (Exception $e) {
        $db = null;
        $counter++;
        if ($counter == $limit)
            throw $e;
    }
}

$aid = $db->prepare("SELECT MAX(id) FROM production_schedule");
$aid->execute();
$big_id = $aid->fetchColumn();
$refid = intval($big_id);


$conn=odbc_connect('Prod_Schedule','','');
if (!$conn) {
    exit("Connection Failed: " . $conn);
}

$sql="SELECT * FROM Schedule WHERE ID > $refid";
$rs=odbc_exec($conn,$sql);
if (!$rs) {
    exit("Error in SQL");
}

***** INSERT CODE TO PUT THESE MS ACCESS ENTRIES INTO THE MYSQL TABLE ******
?>

score 2 · Answer 1 · answered Feb 13 '14 at 21:52

I just tested the following code and it seems to work okay for me:

$dsn = "mysql:host=localhost;port=3307;dbname=myDb;charset=utf8";
$uname = "root";
$pword = "whatever";
$mysqlDb = new PDO($dsn, $uname, $pword);
$mysqlDb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$mysqlSql = "INSERT INTO clients (LastName, FirstName) VALUES (?, ?)";
$mysqlCmd = $mysqlDb->prepare($mysqlSql);
$LastName = '';
$FirstName = '';
$mysqlCmd->bindParam(1, $LastName, PDO::PARAM_STR, 255);
$mysqlCmd->bindParam(2, $FirstName, PDO::PARAM_STR, 255);

$connStr = 
        'Driver={Microsoft Access Driver (*.mdb, *.accdb)};' .
        'Dbq=C:\\Users\\Public\\Database1.accdb;';
$accessDb = odbc_connect($connStr, "", "");
$accessSql = "SELECT LastName, FirstName FROM Clients";
$accessResult = odbc_exec($accessDb, $accessSql);
while ($accessData = odbc_fetch_array($accessResult)) {
    $LastName = $accessData["LastName"];
    $FirstName = $accessData["FirstName"];
    $mysqlCmd->execute();
}

score 2 · Accepted Answer · edited May 23 '17 at 12:05

2

something like this maybe:

while(odbc_fetch_row($rs)){

    $sql = "INSERT INTO production_schedule (fieldName1, fieldName2, fieldName3) VALUES (?, ?, ?)";
    $stmt = $dbh->prepare($sql);

    for($i=1;$i<=odbc_num_fields($rs);$i++){
        $stmt->bindValue($i, odbc_result($rs,$i));
    }

    $stmt->execute();   
}

Note: depends on how many data you have to dump, you should use a solution like this: PDO Prepared Inserts multiple rows in single query to reduce risk of PHP timeout.

edited May 23 '17 at 12:05

Community

1
1

answered Feb 13 '14 at 21:52

MamaWalter

2,073
1
18
27

Does "VALUES" after production_schedule belong there? – bagofmilk Feb 13 '14 at 22:06
Where you put `VALUES (?, ?, ?)`. Do i actually need to put the question mark or do i assign it a name? The reason I'm asking is because I'm getting the error: `Invalid parameter number: number of bound variables does not match number of tokens` – bagofmilk Feb 13 '14 at 22:23
nevermind. I had an error elsewhere. Thank you. This works great – bagofmilk Feb 13 '14 at 23:09

score 1 · Answer 3 · answered Feb 13 '14 at 21:38

First create a function to insert the values into MySQL, then loop through the ODBC results;

function createProductionSchedule($company,$person,$order){

$mysqli_con=mysqli_connect(DBHOST,DBUSER,DBPASS,DBNAME);

if (mysqli_connect_errno($mysqli_con))
  {
  echo 'Failed to connect to MySQL';
  }

//Obviously your own fields here
$company = mysqli_real_escape_string($mysqli_con, $company);
$person = mysqli_real_escape_string($mysqli_con, $person);
$order = mysqli_real_escape_string($mysqli_con, $order);

$sql = "INSERT INTO production_schedule VALUES ('$company','$person','$order')";

mysqli_query($mysqli_con, $sql);

return mysqli_insert_id($mysqli_con);

mysqli_close($mysqli_con);
}

Then in your code section

while (odbc_fetch_row($rs))
  {
  $company=odbc_result($rs,"Company");
  $person=odbc_result($rs,"Person");
  $order=odbc_result($rs,"Order");  
  //Call the function to insert the record    
  createProductionSchedule($company,$person,$order);

  }


odbc_close($conn);

+1 for trying to make the answer match the database structure in the question. -1 for "gluing together" the SQL statement. A prepared statement would make a better example. — Gord Thompson, Feb 13 '14 at 21:57

PHP PDO MySQL get entries from Access and INSERT into MySQL

3 Answers3