Last updated on January 8th, 2020 at 10:30 pm

Microsoft access database is very useful when we creating a software and we need database we can use access to save data like we use SQL Server. Setup database in MS access is very  easy if you have any experience of excel you feel same when you setting up database in MS access. In this post i will explain you how we can create database in Microsoft access and and perform CRUD (Create Read Update Delete) operation on that using PHP Data Object (PDO).

What is PDO?

We mostly use MySQL or MySQLi extensions when we need to access database but in PHP version 5.1 there is a new and better way to do this  using PDO.

“PDO – PHP Data Object is a new light extension to provide fast access to multiple database.”

PDO extension support no of database here is list of database supported by PDO.

  • PDO_DBLIB ( FreeTDS / Microsoft SQL Server / Sybase )
  • PDO_FIREBIRD ( Firebird/Interbase 6 )
  • PDO_IBM ( IBM DB2 )
  • PDO_INFORMIX ( IBM Informix Dynamic Server )
  • PDO_MYSQL ( MySQL 3.x/4.x/5.x )
  • PDO_OCI ( Oracle Call Interface )
  • PDO_ODBC ( ODBC v3 (IBM DB2, unixODBC and win32 ODBC) )
  • PDO_PGSQL ( PostgreSQL )
  • PDO_SQLITE ( SQLite 3 and SQLite 2 )
  • PDO_4D ( 4D )

To find out which PDO driver you have use this code:

<?php
echo '<pre>';
print_r(PDO::getAvailableDrivers());
?>

If you did not find any driver you need to enable PDO extensions from php ini file. Open your  php.ini file in any text editor and look for PDO extension

extension=php_openssl.dll
;extension=php_pdo_firebird.dll
extension=php_pdo_mysql.dll
;extension=php_pdo_oci.dll
extension=php_pdo_odbc.dll
;extension=php_pdo_pgsql.dll
extension=php_pdo_sqlite.dll
;extension=php_pdo_sqlite_external.dll
;extension=php_pgsql.dll
;extension=php_pspell.dll
;extension=php_shmop.dll

remove ( ; ) before extension you want to enable as shown in above.

After enable extension in php.ini you are ready to use PDO.

Step 1. Create database in Microsoft access.

Search for Microsoft access in window search and open it.
Create new database by using shortcut Ctrl+n  or by selecting option from Office Button at top of window.
Now we can create table inside it you can download a sample db file from here.

Step 2. Create connection to Microsoft access db file with php.

Now we are going to create a connection to MS Access file using PHP PDO.

<?php
// Location to file
$db = 'F:\localhost\htdocs\msaccess\trinitytuts.accdb';
if(!file_exists($db)){
	die('Error finding access database');
}
// Connection to ms access
$conn = new PDO("odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=".$db.";Uid=; Pwd=;");

Please not you need to enter your complete location of Ms Access file as i show in above code. We are using PDO odbc driver to access microsoft access database i did not set any Uid or Pwd to my database file if you like you can set userid and pwd to the file.

Step 3.  Insert data to Ms Access using PDO.

Now we are going to insert data to database we use same SQL query we use to insert data

<?php
try{
	$sql = "INSERT INTO tuts (name, age, status) VALUES (:name, :age, :status);";
	$query = $conn->prepare($sql);
	$inst = $query->execute(array(
		":name" => 'Aneh Thakur',
		":age" => '22',
		":status" => 'Love Think Code!'
	));
}catch(PDOExepction $e){
	echo $e->getMessage();
}
?>

I use try and catch statement in above code to handle error. We can simply add data to database in PDO first we prepare or query and after we execute our code to insert data.

Step 4. Read all data from database of MS Access

<?php
try{
	$sql = "SELECT * FROM tuts";
	$result = $conn->query($sql);
	$row = $result->fetchAll(PDO::FETCH_ASSOC);
	
	var_dump($row);
	
}catch(PDOExepction $e){
	echo $e->getMessage();
}
?>

In above code we fetch all data from database in Associative array but we more option to fetch data in different format here is list of option.

  • PDO::FETCH_ASSOC: returns an array indexed by column name
  • PDO::FETCH_BOTH (default): returns an array indexed by both column name and number
  • PDO::FETCH_BOUND: Assigns the values of your columns to the variables set with the ->bindColumn() method
  • PDO::FETCH_CLASS: Assigns the values of your columns to properties of the named class. It will create the properties if matching properties do not exist
  • PDO::FETCH_INTO: Updates an existing instance of the named class
  • PDO::FETCH_LAZY: Combines PDO::FETCH_BOTH/PDO::FETCH_OBJ, creating the object variable names as they are used
  • PDO::FETCH_NUM: returns an array indexed by column number
  • PDO::FETCH_OBJ: returns an anonymous object with property names that correspond to the column names

Step 5. Update data in database using PDO

<?php
try{
	$sql = "UPDATE tuts SET name= :name, age = :age, status = :status WHERE ID= :id ;";
	$query = $conn->prepare($sql);
	$inst = $query->execute(array(
		":name" => 'Aneh',
		":age" => '22',
		":status" => 'I love development',
                ":id" => '1'
	));
}catch(PDOExepction $e){
	echo $e->getMessage();
}
?>

In this code we can update data in database.

Step 6.  Delete data in database using PDO

<?php
try{
$id = '1';
$sql= "DELETE * FROM tuts WHERE ID = :Id";
$qur = $conn->prepare($sql);
$qur->bindParam(':Id', $id, PDO::PARAM_INT);
$qur->execute();
}
catch(PDOExepction $e){
	echo $e->getMessage();
}
?>

In above code we can perform delete operation we are using a filter here

$obj->bindParam(‘id’, ‘value by reference’,  ‘parameter/filter’);

to read more about this please follow this link.
this is a basic tutorial to perform CRUD operation over database using PHP PDO.
Some other simple example using PDO

<?php

// Code to perform like search
try{
$filmName = 'aneh';
$sql= "SELECT * FROM tuts WHERE name LIKE :Name";
$qur = $conn->prepare($sql);
$qur->bindParam(':Name', $filmName, PDO::PARAM_STR);
$qur->execute();
$total = $qur->rowCount();
while ($row = $qur->fetchObject()) {
   echo $row->name;
   echo '<br>';
   echo $row->status;
}
}catch(PDOExepction $e){
	echo $e->getMessage();
}

// Count row and display result for particular search
try{
$filmName = 'aneh';
$sql= "SELECT * FROM tuts WHERE name LIKE :Name";
$qur = $conn->prepare($sql);
$qur->bindParam(':Name', $filmName, PDO::PARAM_STR);
$qur->execute();
$total = $qur->rowCount();
while ($row = $qur->fetchObject()) {
   echo $row->name;
   echo '<br>';
   echo $row->status;
}
}catch(PDOExepction $e){
	echo $e->getMessage();
}

?>

I hope it will help you to learn to connect different database and perform CRUD operation on the database and help you to learn to php PDO.

Happy coding!!.

Posted in: php