Mega Code Archive
Microsoft DAO 3.5 compliant data access to MySQL database
<![CDATA[
<?
// ************ set these constants to automatically connect to the local MySQL listener
$gcon_mysqlhost = "localhost";
$gcon_mysqluid = "username";
$gcon_mysqlpwd = "password";
//
***************************************************************************
***********
function opendatabase($dbname)
{
return new dao_database($dbname);
}
class dao_database
{
//*** public properties***
//DAO properties
var $databasename; // (get only)
//MySQL Back-Door Properties (use these only when absolutely necessary, or not at all!)
var $connection_handle; // (get only)
var $databased_select_handle; // (get only)
//*** private ***
// (note: its my own convention that private varaibles are given the "l_" prefix, for "local")
var $l_databasename;
var $l_ch;
var $l_dsh;
//*** constructor ****
function dao_database($dbname)
{
//make available the globals for mysql connectivity within this class (PHP does this funny-
like)
global $gcon_mysqlhost, $gcon_mysqluid, $gcon_mysqlpwd;
//DAO users: don't use this method directly, use the more familiar opendatabase()
function
$this->l_ch=mysql_connect($gcon_mysqlhost, $gcon_mysqluid, $gcon_mysqlpwd);
$this->l_dsh = mysql_select_db($dbname,$this->l_ch);
//error check
if ((intval($this->l_ch)==0)||(intval($this->$l_dsh)==0)) return false;
$this->databasename=$dbname;
$this->l_databasename=$dbname;
return true;
}
function openrecordset($sql)
{
return new dao_recordset($sql,$this->l_ch);
}
function execute($sql)
{
mysql_query($sql,$this->l_ch);
return true;
}
function close()
{
//note: this does not remove the database object from PHP, nor does it close the MySQL
connection (as mysql_connect() re-uses connection handles and we may inadvertently close
another database object's connection)
//what does it do then? Right now nothing, but in the near future it will close access to
the object's methods, returning an error.
return true;
}
}
class dao_recordset{
//*** public ***
//DAO properties
var $eof; //(get only)
var $nomatch; //(get only)
var $fields_count; //(get only)
var $recordcount; //(get only)
//*** private ***
var $l_res;
var $l_sql;
var $l_numrows;
var $l_currownum; //this is the result set's current internal seek index
var $l_currow; //this is the row array returned by mysql_fetch_row containing the current
rows literal data;
//*** private helper functions *** (DO NOT CALL PUBLICALLY UNDER ANY CIRCUMSTANCES!)
function translate_fieldname_to_ordinal($fn)
{
for ($a=0;$a<$this->fields_count;$a++)
{
//make it fieldnames case insensitive (important DAO feature!)
$fn=strtolower($fn);
if (strtolower($this->fields_name($a))==$fn) return $a;
}
return -1;
}
//*** constructor ***
function dao_recordset($sql,$ch)
{
$this->l_res=mysql_query($sql,$ch);
//set the internal vars to correct values
$this->l_sql = $sql;
$this->l_numrows = intval(mysql_num_rows($this->l_res));
$this->l_currow = 0;
$this->fields_count=mysql_num_fields($this->l_res);
//remember that it is possible to have a recordset WITH ZERO ROWS!
if ($this->l_numrows==0)
{
$this->eof=true;
$this->l_currow=NULL;
}
else
{
$this->eof=false;
$this->l_currow=mysql_fetch_row($this->l_res);
}
//set the DAO property "Recordcount"
$this->recordcount=intval($this->l_numrows);
return true;
}
//*** methods ***
function value($fn)
{
//Pain-in-the-butt: Microsoft's object model allows definition of a "default" property
// that allows the following usage (in VB):
//
// r$ = recordset1("userid")
//
// but in PHP (because of language limitations), you must use the "value" method to
// return the data in a field, like this (in PHP):
//
// $r=recordset1->value("userid");
//IMPORTANT: Technically, DAO formally perpetuates the field type to the variable type,
but since
// PHP does NOT support variable typing (neither does MySQL for that matter) the
variable
// will always be intepreted as a STRING (i.e. a CHAR or a VARCHAR for you DB junkies
out there!)
//note: DAO allows access via field name or field ordinal, see what it is
if (intval($fn)==0)
$fn=$this->translate_fieldname_to_ordinal(strval($fn));
return strval($this->l_currow[intval($fn)]);
}
function movenext()
{
if ($this->eof!=true)
{
$this->l_currow=mysql_fetch_row($this->l_res);
if ($this->l_currow==false)
{
$this->eof=true;
$this->l_currownum=-1;
}
else
{
$this->l_currownum++;
}
}
}
function movefirst()
{
mysql_data_seek($l_res,0);
$l_currownum=0;
$l_currow=mysql_fetch_row($l_res);
if ($l_numrows>0) $eof=false;
}
function movelast()
{
mysql_data_seek($l_res,$l_numrows);
$l_currownum=$l_numrows;
$l_currow=mysql_fetch_row($l_res);
$eof=true;
}
function moveprevious()
{
if ($l_currownum==0)
{
$bof=true;
return false;
}
mysql_data_seek($res,$l_currownum-1);
$l_currownum--;
$l_currow=mysql_fetch_row($l_res);
return true;
}
/*
// findfirst and findnext are NOT IMPLEMENTED IN THIS VERSION OF DAO.PHP3
function find($wc)
{
//let's see if you can figure out what I am doing here!
//form a modified version of the record set's source SQL
$s=strtolower($this->l_sql);
$insert_pos=strpos(1,$s,"where");
if (($insert_pos)==false)
{
//there is no where clause in the original SQL, place one after " ...FROM tablename..."
$insert_pos=strpos(1,$s," from ");
$sc=0;
for ($a=$insert_pos;($a<strlen($s))&&($sc<4);$a++)
{
$c=substr($s,$a,1);
if ($c==" ") $sc++;
}
$insert_pos=$a;
if ($insert_pos==strlen($s))
$ms="$s where $wc";
else
$ms=substr($s,0,$insert_pos)." where $wc ".substr($s,$insert_pos+1);
}
else
{
$ms=substr($s,0,$insert_pos+4)." and ($wc) ".substr($s,$insert_pos+5);
}
//debug
echo("Original: $this->l_sql<p>Modified: $ms<p>\n");
}
// findfirst($whereclause);
// findnext($whereclause);
*/
function fields_name($fo)
{
return mysql_field_name($this->l_res,intval($fo));
}
function fields_type($fo)
{
$ft=mysql_field_type($this->l_res, intval($fo));
//we really should translate this type value from MySQL native value to
//one recognizable to DAO users (i.e. convert VARCHAR codes to "char" codes, and
TEXT/BLOB codes to "Memo" codes
return $ft;
}
function close()
{
mysql_free_result($this->l_res);
$this->l_sql="";
$this->eof=true;
$this->bof=true;
$this->l_currow=NULL;
$this->l_currownum=0;
$this->l_numrows=0;
$this->recordcount=0;
$this->nomatch=true;
}
}
?>
Example :
========
<html>
<?
//remember to set up the global constants in dao.php3 before using...
include("dao.php3");
$db=opendatabase("yourdatabasename");
$rs1=$db->openrecordset("select * from users");
echo($rs1->recordcount." records were returned.\n");
if ($rs1->recordcount>0)
{
echo("<table border=1 cellpadding=3>\n");
while ($rs1->eof==false)
{
$username=$rs1->value("username");
$password=$rs1->value("password");
echo("<tr><td><center>$username</td><td><center>$password</td></tr>\n");
$rs1->movenext();
}
echo("</table>\n");
}
$rs->close();
?>
</html>
]]>