Sunday, August 30, 2015

[IT] Connecting PHP with Microsoft Access (.mdb or .accdb)




WHAT I USED:
1. XAMPP 2.5 (PHP 5.3.8)
2. Microsoft Access 2007


STEPS:
1. Prepare the database on Microsoft Access. Here, I used 2002-2003 format (.mdb).

(picture 1: a first  Microsoft Access 2007 interface)


(picture 2: preparing a new database named phptoacc.mdb)

(picture 3: preparing new table named testdb with its fields, testid and testname)


(picture 4: inserting two new records)

2. Save the database where the PHP can easily reach it. Here, I saved it on "access" folder inside "htdocs" folder of "xampp" folder.

(picture 5: where I stored my database)

3. Type the PHP script for trying to connect to the database.


<?php

$db = $_SERVER["DOCUMENT_ROOT"] ."/access/phptoacc.mdb";
if (!file_exists($db))
{
       die("No database file.");
}

$dbNew = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=$db; Uid=; Pwd=;");
$sql = "select * from testdb";
$rs = $dbNew->query($sql);

while($result = $rs->fetch())
{
     echo $result[0].": ".$result[1]."<br />";
}


?>



I will explain each part of the script:

a. SELECTING DATABASE

$db = $_SERVER["DOCUMENT_ROOT"] ."/access/phptoacc.mdb";
if (!file_exists($db))
{
        die("No database file.");
}

The above script is used to validate whether the phptoacc,mdb exists on "access" folder.


b. CONNECTING TO THE DATABASE

$dbNew = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=$db; Uid=; Pwd=;");

The above script uses the PDO driver to connect to Microsoft Access.



NOTE:
If the error message show "PDOException Could not find driver", you must check whether the PDO ODBC driver is installed or not. You can check it by executing:
<?php  phpinfo() ?>
 The output should show information like:

PDO

PDO supportenabled
PDO driversmssql, mysql, odbc, sqlite, sqlite2

pdo_mysql

PDO Driver for MySQLenabled
Client API versionmysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $

PDO_ODBC

PDO Driver for ODBC (Win32)enabled
ODBC Connection PoolingEnabled, strict matching


If an entry for PDO ODBC is not present, you will need to ensure your installation includes the PDO extension and ODBC drivers. To do so on Windows, uncomment the line extension=php_pdo_odbc.dll in php.ini, restart Apache (or XAMPP), and then try to connect to the database again. (source: http://www.sitepoint.com/using-an-access-database-with-php/)

c. THE SQL TO SHOW THE RECORDS

$sql = "select * from testdb";
$rs = $dbNew->query($sql);

while($result = $rs->fetch())
{
     echo $result[0].": ".$result[1]."<br />";
}

The above script is used to show the records stored on testdb table.



5. Execute the script.

(picture 6: the result on Google Chrome)
Powered by Blogger.