Php > Databases

Microsoft DAO 3.5 compliant data access to MySQL database

Microsoft DAO 3.5 compliant data access to MySQL database

<? 

// ************ 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>