PHP MySQL database access methods (transfer)

2011-01-04  来源:本站原创  分类:PHP  人气:144 

PHP access to MySQL database in several ways.
First, access to MySQL database using PHP
Here I talk about the group is to use the mysql_ * functions and mysqli_ * function group to access the MySQL database.
(A): the mysql_ * functions use PHP to access MySQL database group

<?php
       $conn=mysql_connect("hostname","username","password") or die("  Links Database failure  ");
       mysql_select_db("<database>");
       $sql="<SQL statements>";
       $rs=mysql_query($sql,$conn);
       mysql_free_result($rs);
       mysql_close($conn);
      ?>

(B): Using PHP's mysqli_ * functions to access MySQL database group

<?php
       $mysqli=mysqli_connect("hostname","username","password","database");
       if(mysqli_connect_errno()){
          printf("Connect failed:%s\n",mysqli_connect_error());
          exit();
       }else{
          $sql="<SQL statements>";
          $rs=mysqli_query($mysqli,$sql);
          if($rs===TRUE){
             echo "sucessfully!";
          }else{
             echo "failed!";
          }
          mysqli_close($mysqli);
       }
       ?>

Second, the use of self class to access MySQL database

Third, access to MySQL database using ADODB
Simple ADODB library
ADODB (active data object data base) is a database system written by the php function, adobd provides a standard interface to database operations.
adodb database has been developed to support: mysql, postgresql, oracle, interbase, microsoft sql server, access, foxpro, sybase, odbc, ado
Adodb library database with a connection regardless of database use php when the first thing to do is to first connect to the database server, adodb library connect with ADOConnection objects.
To use the adodb library first go to the official website to download a class library ( http://adodb.sourceforge.net ), and then extracted to a current document,
Before using it to use include ("adodb / adodb.inc.php") to include it, following its connect to the database server to start ....

(A),

<?php
      include("adodb/adodb.inc.php");
      //  Tell adodb to connect to the database type that we
      $db=NewADOConnection("mysql");
      //  Connect to the database, will return a  false  Or the  true
      $link=$db->Connect("localhost","root","dd0633LSL");
      if(!$link){
         //  Output and stop the program to
         die("<h1>  Database connection failed  !</h1>");
      }
      echo "<h1>  Database Connection Successful  </h1>";
      ?>;

adodb object-oriented approach using a variety of database management, and use a different class to operate on different databases,
These things are carried out in ADOConnection function, users need to do is tell ADOConnection () a valid drive name of the database.
This function will return a adodb object, and then they can use this object Connect () method to connect to the database,
Section of the code above $ db-> Connect ("host", "user", "pass"), in the adodb library can connect to the database more 0 kinds of ways:
1, non-persistent connection Connect () The advantage of this type of connection can reduce the database in between WEB server overload danger.
2, persistent connection PConnect () persistent connections, this connection to the database will not be cut off so fast

(B), data source connection using dsn
dsn format such as: driver: / / username: password @ hostname / database [? option [= value]]
After using the dsn ADOConnection () will automatically call the method Conect () or PConect () to create a non-persistent connection or a persistent connection
When the connection is established based on the option / value to determine the value of a lasting connection or non-persistent connections, so the connection is successful will return a true failure to return a false.

<?php
      include("adodb/adodb.inc.php");
      //  Non-persistent connections
      $dsn="mysql://root:[email protected]/adodb";
      /*
        Persistent connections
      $dsn="mysql://root:[email protected]/adodb?persist";//  Or  persistent
      */
      $db=NewADOConnection($dsn);
      if(!$db){
         die("  Database connection failed  !");
      }
      echo "  Successful connection  ";
      ?>

-------------------------------------------------- ------------------------------------
Perform database query operations often use select statement or query to retrieve commonly used data adodb execute () method performs the database query, if successful ADORecordSet object
It contains the results of a query record set. There are many objects in the ADORecordSet way to record sets of operations such as:
-------------------------------------------------- -------------------------------------------------- -------------------------
FetchRow () returns an array of records of the current line, encountered EOF it returns false (adodb5.04 is down almost have been able to automatically pointer)
FetchInto (& $ array) into the record of the current row in the array $ array, encountered EOF returns false, otherwise 1
FetchObject ($ toupper = ture) returns the current row of the record consisting of objects, and FetchRow () method is similar to the value of true if the $ toupper returns the property will be fully converted to uppercase
FetchObj () and FEtchObject (false), except that field names will not be converted back
FetchNextObject ($ toupper = ture) to return the records of the current composition of the object and record automatically move to the next line, the value of true if the topper is the object returned by the property name in all capital
FetchNextObj () and FetchNextObject () class is the return value of knowledge will not be replaced
MoveNext () will record the current record pointer position from the record to move to the next line. As move ()
-------------------------------------------------- -------------------------------------------------- -------------------------
An example
-------------------------------------------------- -------------------

<?php
   include("adodb/adodb.inc.php");
   //  Here is the mysql database created
   //$dsn="mysql://root:[email protected]/adodb";

   //$db=& NewADOConnection($dsn);
   //  The following access database connection
   $drive="driver={Microsoft Access Driver (*.mdb)};Dbq="realpath("date/db1.mdb").";Uid=;Pwd=;";
   $db=NewADOConnection("access");
   $db->Connect($drive);
   $query="select * from user";
   $RecordSet=&$db-&gt;Execute($query);
   if(!$RecordSet)
   {
   echo $db-&gt;ErrorMsg();
   }else{
   //echo $RecordSet-&gt;RecordCount();
   while($arr=$RecordSet-&gt;FetchRow()){
     echo $arr[0]."----".$arr[1]."----".$arr[2]."<br />";

     }
   }
   $RecordSet-&gt;close();
   $db-&gt;close();
   ?>;
   *   Note :  In the test, get a lot of trouble, the use of  access  , First configure the data source  :  --- Administrative Tools in Control Panel  ---  The user data source ODBC ---  DSN  MS ACCESS DATABASE ---- Select Select   Microsoft Access Driver---  Formerly known input data, select the database click Finish  ,  Data source that is configured to avoid data source error  .
     In the above example you can use  SetFetchMode()  Method sets the return mode, the method can be set to  :
   ADODB_FETCH_DEFAULT: Default mode, depending on the data available behavior may be one of the following three values
   ADODB_FETCH_NUM:  As the construction of a numeric index
   ADODB_FETCH_ASSOC:  The field name as key
   ADODB_FETCH_BOTH:  Index and field, respectively, as key figures
   SetFetchMode()  Application Examples
   <?php
   include("adodb/adodb.inc.php");
   $db=NewADOConnection("mysql://root:[email protected]/adodb");
   //  The value can be set to change my point of view model results
   $db->SetFetchMode(ADODB_FETCH_NUM);
   $rst1=$db-&gt;Execute("select * from user");
   print_r($rst1-&gt;fields);
   ?>;

-------------------------------------------------- -------------------
ADOConnection object methods object in ADOConnection there a method called Selectlimit () This method is used to solve some of the database can not select ... limit of support, after the method call returns a ADOConnection object.
-------------------------------------------------- ---------

<?php
   include("adodb/adodb.inc.php");
   //  Here is the mysql database created
   //$dsn="mysql://root:[email protected]/adodb";
   //$db=& NewADOConnection($dsn);
   //  The following access database connection  

   $drive="driver={Microsoft Access Driver (*.mdb)};Dbq="realpath("date/db1.mdb").";Uid=;Pwd=;";
   $db=NewADOConnection("access");
   $db->Connect($drive);
   $query="select * from user";
   //SelectLimit  From as many as 3 begin to return  2  Records
   $RecordSet=&$db-&gt;SelectLimit($query,2,3);
   if(!$RecordSet)
   {
   echo $db-&gt;ErrorMsg();
   }else{
   //echo $RecordSet-&gt;RecordCount();
   while($arr=$RecordSet-&gt;FetchRow()){
     echo $arr[0]."----".$arr[1]."----".$arr[2]."<br />";

     }
   }
   $RecordSet-&gt;close();
   $db-&gt;close();
   ?>;

-------------------------------------------------- ---------
:: Rs2html () method
rs2html methods incoming ADORecordSet object into html table format, you need to use first tohtml.inc.php included.

Insert and update operations to the database in adodb insert or the update data, you can use the execute () method, then insert the data generally have some of the data processing so that the database can accept for a number of databases and string for date format different approach must first be inserted into the data processing functions, such as using adodb to provide
DBDate (), SQLDate ()
qStr ():: simply quotes the string handling problem
Some commonly used ADODB class library function
Affected_Rows ()
Insert_ID ()
These two functions correspond to the mysql_affected_rows () mysql_insert_id ()
Insert and update automatically
AutoExecute ($ table, $ arrFields, $ mode, $ where)
$ Table:: table $ arrfields:: data $ mode:: "INSERT" or "UPDATE" $ WHERE condition
:: About the use of paging functions::
The file adodb-pager.inc.php generated objects ADODB_Pager ($ db, $ query);
For example:
include ("adodb-pager.inc.php");
$ Query = "select * from user";
$ Pager = new ADODB_Pager ();
/ / Set to HTML format, such as data Xu

$ Pager-> htmlSpecialChars = false;
$ Pager-> Render (5);
When using the paging function in adodb-pager.inc.php can change the contents of the output data format
Fourth, access to MySQL database using Adodb.Connection

Adodb library does not need at this time?

try{
             $com = new COM("Adodb.Connection");
             $com->open("Driver={SQL Server};server=(local);uid=sa;pwd=*****;database=*****;");
        }catch(com_exception $e){
             die($e->getMessage());
        }try{
             $rs = new COM("Adodb.RecordSet");
             $rs->open("select * from user_list",$com,1,1);
        }catch(com_exception $e){
             die($e->getMessage());
        }

        $AllCount = $rs->RecordCount;

        for($i=1;$i<$AllCount;$i++){
             echo($rs["u_id"]->value."
             ");
             $rs->MoveNext();
        }
        $rs->Close();
        $rs = null;
        $com->Close();
        $com = null;
   ?>

Fifth, use the ODBC connection mysql

Through an ODBC connection, you can connect to your network in any database on any computer, as long as the ODBC connection is available.

1. Open the Control Panel ODBC Data Source Administrator, confirmed that already exists in the driver mysql odbc driver (window 2003 automatically have installed)
2. In the system DSN to add a data source, enter
data source name: mysqlodbc,
user: root,
password: root,
Select database and then determine
3. Enter code

<?php
       $conn=odbc_connect('mysqlodbc','root','root');
       if (!$conn){exit("Connection Failed: " . $conn);}
       $sql="SELECT * FROM class";
       $rs=odbc_exec($conn,$sql);
       if (!$rs) {exit("Error in SQL");}
       echo "<table><tr>";
       echo "<th>id</th>";
       echo "<th>name</th></tr>";
       while (odbc_fetch_row($rs)){
         $id=odbc_result($rs,"id");
         $name=odbc_result($rs,"name");
         echo "<tr><td>$id</td>";
         echo "<td>$name</td></tr>";
       }
       odbc_close($conn);
       echo "</table>";
   ?>
相关文章
  • PHP MySQL database access methods (transfer) 2011-01-04

    PHP access to MySQL database in several ways. First, access to MySQL database using PHP Here I talk about the group is to use the mysql_ * functions and mysqli_ * function group to access the MySQL database. (A): the mysql_ * functions use PHP to acc

  • Mysql database optimization methods (transfer) 2010-12-10

    1, select the most appropriate field properties MySQL support can be very good access to large amounts of data, but generally speaking, the smaller tables in the database, run queries on it, there would be faster. Therefore, when you create the table

  • PHP MYSQL database access wrapper class 2010-08-01

    Reference discuz 6.1 and a number of online resources, simplify their packaging a little, in php5 + mysql5 on tested, annex a complete code. <? Php / * MYSQL database access wrapper class MYSQL data access method, php4 support mysql_ at the beginning

  • MySQL source learning: MySQL database access to the prohibition of implementation across 2011-10-16

    Let me talk about this "cross-database" means: The current use is db1, you can still use select * from db2.table1 to access table1 table. This allows us to need to access MySQL with a table under the other one more time without use, but also mak

  • Android Location information access methods (transfer) 2010-06-05

    Number and location in the development of related procedures, often need access to equipment, Location, Location of access equipment as follows: 1. Create a LocationListener interface implementation class, you can use anonymous class, the code is as

  • (Study Notes) windows automatically under the MySQL database backup methods 2011-05-31

    Imaginary environment: MySQL installation location: C: \ "Program Files" \ MySQL \ "MySQL Server 5.0" \ bin Forum database name: db MySQL root password: 123123 Database backup destination: D: \ db_backup \ Script: @echo off echo Backin

  • MYSQL database optimization methods 2010-12-02

    1, select the most appropriate field properties, the definition of the field to minimize the length of the field is set as NOT NULL, such as 'provinces, gender', the best set to ENUM 2, using the connection (JOIN) to replace the sub-query: a. Remove

  • ARCGIS spatial database access methods OBJECTID 2011-05-25

    Save time in the spatial database approach for the primary key select sde.version_user_ddl.next_row_id ('SDE', registration_id) from dual; registration_id obtained from the following statement for the registration id number SELECT registration_id FRO

  • PHP to access MYSQL database wrapper class 2010-08-01

    Reference discuz 6.1 and a number of online resources, simplify their packaging a little, in php5 + mysql5 on tested, annex a complete code. <? Php / * MYSQL database access wrapper class MYSQL data access method, php4 support mysql_ at the beginning

  • . NET to use Oracle databases. Database Access Technology (B) 2010-05-26

    In the article describes. NET Oracle data provider to use to access Oracle's methods and the way older database access, ODBC, DAO, RDO, etc.. Overall, the emergence of ODBC allows programmers do not need to concern with the specific database interfac

  • Chinese garbled mysql database problem solutions 2010-07-09

    First introduced to the situation I encounter: Action from the JSP page spread in the Chinese language is no problem, save the time it was the Chinese, save the results page is displayed in Chinese, but the database is garbled. Cause of the problem i

  • MySQL database server on CentOS configuration 2011-03-18

    CentOS also inadvertently found a professional forum, remember: http://www.centospub.com So, start learning the configuration The environment: CentOS system mysql-server (version forget, because it is installed using yum) Process is as follows (repro

  • MySQL database to optimize the specific method described 2011-03-29

    The following article focuses on the practical realization of a simple MySQL database optimization specific methods, and practice what specific steps are worth our attention. The following article describes a simple and practical to optimize the MySQ

  • C # methods to access mysql database 2011-06-14

    MySql.Data.MySqlClient connection in C # using MySql Database 2009-01-19 09:50:28 1126 0 comments read: AAA Subscribe In C # to connect MySql database is actually a very simple thing, but just started learning C # friends, the problem is not small, m

  • [Transfer] mysql database and backup and recovery 2010-11-10

    In the database table is missing or damaged, the backup of your database is very important. In the event of system crash, you'll want your table to the least possible loss of data back to the state when the crash occurred. Sometimes the damage is mys

  • MySQL database performance optimization of the eight methods 2010-09-09

    MySQL database performance optimization of the eight methods From: http://www.blogjava.net/hellboys/archive/2009/03/25/261843.html 1, select the most appropriate field properties MySQL is well supported by a large amount of data access, but generally

  • [Transfer] MySQL database performance optimization of the eight "Cherish" 2011-05-11

    1, select the most suitable well field property MySQL support access to large amounts of data, but generally speaking, the smaller tables in the database, run queries on it, there would be faster. Therefore, when you create the table, in order to obt

  • MySQL database solution method does not allow remote access from 2010-03-03

    Solution: 1, changing table method. Your account may be allowed from a remote landing, only localhost. This time as long as the localhost of that computer, log in MySQL , change " MySQL " database in the "user" table of "host"

  • MySQL database under Linux binary log recovery methods 2010-07-08

    MySQL database under Linux binary log recovery methods September 21, 2009 04:04 pm Monday, if the MySQL server binary log is enabled, you can use mysqlbinlog tool to recover starting from the specified point in time (for example, from your last backu

  • Slow LAN access MySQL database solution 2010-08-20

    Windows systems solutions 1. WIN2003/XP system into the Windows directory 2. Identify the my.ini file. 3. To modify the file contents, in the [mysqld] add the following content under the skip-name-resolve skip-grant-tables 4. Save the file, restart s