How can you use Microsoft ADO with PHP to query MS Access databases?
MS ADODB is supplied with MDAC and is always installed on Windows web servers. You can use ADODB to perform parameterized queries, SELECT, INSERT, and UPDATE against any ODBC or OLEDB data provider including MS Access.
If you use the OLEDB driver you will get faster results than with ODBC driver.
Here we perform a SELECT against MS Access using ADO and a parameterized query. To understand why you should want to use parameterized queries in a web environment, please read the Parameterized SQL Library Simple Select article and the linked example pages.
<?PHP
/*********************************************************************************************/
/* Caveat Emptor: You'll want to add more error handling, this is just the barebones example */
/*********************************************************************************************/
/*** Open ADODB command, connection, and recordset objects ***/
$cmd = new COM('ADODB.Command') or exit ('Cannot start ADO Command Object.');
$conn = new COM('ADODB.Connection') or exit('Cannot start ADO Connection Object.');
$rs = new COM('ADODB.Recordset') or exit('Cannot start ADO Recordset Object.');
/*** See if we got a new Company Type ***/
if ($_SERVER['REQUEST_METHOD']=='POST') {
$companyType = $_POST['type'];
if ($companyType=='') {
$companyType = "construction";
}
}else{
/*** Default Company Type ***/
$companyType = "construction";
}
/*** Some ADO Constants ***/
$adVarChar = 200;
$adParamInput = 1;
$adOpenStatic = 3;
$adLockReadOnly = 1;
$adCmdText = 1;
/*** Open the connection to the database ***/
$conn->Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\wwwroot\\domain\\database\\examples.mdb");
if (!$conn) {
exit('Cannot open database');
}else{
//echo 'Database opened<br>';
/*** Our SQL query ***/
$sql="SELECT Distinct [type] FROM companys ORDER BY [type];";
/*** Set the command objects connection to our open connection ***/
$cmd->ActiveConnection=$conn;
/*** Set the command objects command to our SQL query ***/
$cmd->CommandText=$sql;
/*** Get the records ***/
$rs->Open($cmd);
/*** Build A <select> list ***/
if (!(($rs->EOF) && ($rs->BOF))) {
$select = '<select id="type" name="type" size="1">';
$select .= '<option value="">Select...</option>';
While (!$rs->EOF) {
if ($rs->Fields['type']->Value==$companyType) {
$select .= '<option selected value="'.$rs->Fields['type']->Value.'">'. $rs->Fields['type']->Value .'</option>';
}else{
$select .= '<option value="'.$rs->Fields['type']->Value.'">'. $rs->Fields['type']->Value .'</option>';
}
$rs->movenext;
}
$select .= '</select>';
}
$rs->Close();
/*** We can save time by not closing the connection to the database ***/
/*** as we are going to access the database again to get the companies ***/
$sql="SELECT id as ID, company as Company, address as Address, city as City, state as ST, postalCode as ZipCode, phoneNumber as Phone FROM companys WHERE ([type] = @c);";
$cmd->ActiveConnection=$conn;
$cmd->CommandText=$sql;
/*** Here we set a command parameter, notice the @c in the SQL query ***/
$param = $cmd->CreateParameter('@c',$adVarChar,$adParamInput,(int)50,$companyType);
/*** We have to append the parameter to the command objects parameters collection ***/
/*** Many times, we would have multiple parameters, especially on an INPUT command ***/
$cmd->Parameters->Append($param);
/*** I want the Recordcount so I'm going to set some additional property options ***/
/*** See: http://msdn.microsoft.com/en-us/library/ms678086%28VS.85%29.aspx ***/
$cmd->CommandType=$adCmdText;
$rs->CursorType=$adOpenStatic;
$rs->LockType=$adLockReadOnly;
/*** Get the records ***/
$rs->Open($cmd);
$row="odd";
/*** Build a table of the results ***/
if (!(($rs->EOF) && ($rs->BOF))) {
$html = '<table id="table1"><colgroup><col id="cId"><col id="cCompany"><col id="cAddress"><col id="cCity"><col id="cSt"><col id="cZipcode"><col id="cPhone"></colgroup>';
$html .= '<thead id="thead1"><tr id="tHeadRow1"><td colspan="'. $rs->Fields->Count() .'">Number of records = '. $rs->Recordcount() .'</td></tr>';
$html .= '<tr id="tHeadRow2"><td>ID</td><td>Company</td><td>Address</td><td>City</td><td>ST</td><td>Zip Code</td><td>Phone</td></tr></thead><tbody>';
While (!$rs->EOF) {
if ($row=="odd") {
$html .= '<tr class="odd">';
$row="even";
}else{
$html .= '<tr class="even">';
$row="odd";
}
$html .= '<td>'. $rs->Fields['ID']->Value .'</td><td>'. $rs->Fields['Company']->Value .'</td><td>'. $rs->Fields['Address']->Value .'</td>';
$html .= '<td>'. $rs->Fields['City']->Value .'</td><td>'. $rs->Fields['ST']->Value .'</td><td>'. $rs->Fields['ZipCode']->Value .'</td><td>'. $rs->Fields['Phone']->Value .'</td></tr>';
$rs->movenext;
}
}
/*** Clean Up ***/
$rs->Close();
$rs=null;
$conn->Close();
$conn=null;
$cmd=null;
}
/* In the <body> of our page we will simply add <?PHP echo $select; ?> and <?PHP echo $html; ?>
where we want to display the <select> list and the <table>.
*/
?>
