Detailed PHP operation MySQL database

Operation database is the basis of dynamic Web programming, this article gives you a detailed introduction to PHP how to operate the MySQL database

1. Establish and close the connection
1) mysql_connect ()
Resource mysql_connect ([string hostname [:p ort][:/path/to/socket][,string username] [, string password]])
All parameters are optional
Example:
@mysql_connect ("localhost", "User", "password")
Or Die ("could not connect to MySQL server!");
Note that the @ symbol indicates any error message that is preventing a failed attempt, and the user will see the error message specified in die ().

Note that when connecting to multiple MySQL, you must specify the link ID for each connection as follows:
$link 1 = @mysql_connect ("Server1″, user", "password")
Or Die ("could not connect to MySQL server!");
$link 2 = @mysql_connect ("Server2″, user", "password")
Or Die ("could not connect to MySQL server!");

2) Mysql_pconnect ()
Resource Mysql_pconnect ([string hostname [:p ort][:/path/to/socket][,string username] [, string password]])
Unlike mysql_connect (), the existing links are first found and are not created until they are present.
Note that you do not need to show the close connection (Mysql_close ()) because the connection will be placed in the pool, so it is called a persistent connection.

3) Mysql_close ()
Boolean mysql_close ([Resource link_id])
Closing the connection is not necessary because it can be handled by MySQL garbage collection.
If link_id is not specified, close the most recent link.

2. Select Database
mysql_select_db ()
Boolean mysql_select_db (String db_name [, Resource link_id])

3. Query MySQL
1) mysql_query ()
Resource mysql_query (string query [, Resource link_id])
Responsible for executing query.

2) Mysql_db_query ()
     resource Mysql_db_query (string database, string query [, Resource LINK_ID]
     is equivalent to mysql_select_db () + mysql_query (), which can be clearly seen from the parameters.
  
4. Get and display Data
1) mysql_result ()
     mixed mysql_result (resource result_set, int row [, mixed field
     gets data from a field in the specified row of result_set. Simple but inefficient.
  
     Example:

$link 1 = @mysql_connect ("Server1″, WebUser", "password")
Or Die ("could not connect to MySQL server!");
@mysql_select_db (' company ') or die ("could not select database!");

$query = "SELECT ID, name from product order by name";
$result = mysql_query ($query);
$id = mysql_result ($result, 0, "id");
$name = mysql_result ($result, 0, "name");

Mysql_close ();

Note that the above code is just the field value of the first data in the output set, and that if you want to output all the records, you need to recycle.

…
for ($i = 0; $i <= mysql_num_rows ($result); $i + +)
{
$id = mysql_result ($result, 0, "id");
$name = mysql_result ($result, 0, "name");
echo "Product: $name ($id)";
}
...

Note that if the query field name is an alias, the alias is used in Mysql_result.

2) mysql_fetch_row ()
Array mysql_fetch_row (Resource result_set)
Get the entire row from the Result_set and put the data in the array.
For example (note the ingenious Fit with list):

...
$query = "SELECT ID, name from product order by name";
$result = mysql_query ($query);
while (list ($id, $name) = Mysql_fetch_row ($result)) {
echo "Product: $name ($id)";
}
...

3) mysql_fetch_array ()
Array mysql_fetch_array (Resource result_set [, int result_type])
Enhanced version of Mysql_fetch_row ().
Gets each row of the Result_set as an associative array or an array of/and numeric indices.
Two arrays are obtained by default, and Result_type can be set:
MYSQL_ASSOC: Return associative array, field name => field value
Mysql_num: Returns an array of numeric indices.
Mysql_both: Gets two arrays. Therefore, each field can be referenced by index offset or by field name.
Example:

     ...
     $query = "SELECT ID, name from product order by name";
     $result = mysql_query ($query);
     while ($row = Mysql_fetch_array ($result, Mysql_both)) {
          $name = $row [' name '];//or $name = $row [1];
         $name = $row [' id '];//or $name = $row [0];
         echo "Product: $name ($id)";
    }
     ...

4) Mysql_fetch_assoc ()
Array Mysql_fetch_assoc (Resource result_set)
Equivalent to Mysql_fetch_array ($result, MYSQL_ASSOC)

5) Mysql_fetch_object ()
Object Mysql_fetch_object (Resource Result_set)
As with the mysql_fetch_array () feature, the return is not an array, but an object.
Example:

...
$query = "SELECT ID, name from product order by name";
$result = mysql_query ($query);
while ($row = Mysql_fetch_object ($result)) {
$name = $row->name;
$name = $row->id;
echo "Product: $name ($id)";
}
...

5. Selected records and affected records

1) mysql_num_rows ()
int mysql_num_rows (Resource result_set)
Returns the number of rows in the Result_set.
Note that mysql_num_rows () is only valid in determining the number of records obtained by a SELECT statement query, and you need to use Mysql_affected_rows () if you want to get the number of records affected by the Insert/updata/delete query.

2) Mysql_affected_rows ()
int Mysql_affected_rows ([resource link_id])
Get the number of records affected by a insert/updata/delete query
Note that you do not need to enter parameters, and the default is to use the most recent results of a recently established database connection. You can use the optional parameter link_id to select a database connection.

6. Get information about databases and tables
1) Mysql_list_dbs ()
Resource Mysql_list_dbs ([Resource link_id])
Gets all the database names on the server.
Example:

mysql_connect ("localhost", "name", "pwd");
$dbs = Mysql_list_dbs ();
while (list ($db) = Mysql_fetch_row (DBS)) {
echo "$db <br>";
}

Note that the output is related to the user rights being used.

2) Mysql_db_name ()
String Mysql_db_name (Resource Result_set, Interger index)
Gets the name of the database in the Result_set returned by Mysql_list_dbs () where is index.

3) Mysql_list_tables ()
Resource Mysql_list_tables (String database [, Resource link_id])
Gets all the table names in the database.

4) Mysql_tablename ()
String Mysql_tablename (Resource Result_set, Interger index)
Gets the table name in the result_set where Mysql_list_tables () returns the index.

In the learning php com and. Net (Windows) function, I found a case of SQL Server through COM operation, looked for relevant information, so I have this PHP connection access article, I believe that the internet has a lot of, or posted here.

My machine Environment: win2000,apache2,php Version 5.1.0RC1

<?php
$conn = new COM ("ADODB. Connection ") or Die (" Cant start ADODB. Connection ");
$conn->open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\php5\netbook.mdb");
$rs = $conn->execute ("SELECT * from Manage");   //Recordset
$num _columns = $rs->fields-> Count (); The
Echo $num _columns. "<br/>n";
for ($i =0 $i < $num _columns $i + +) {
    $fld [$i] = $rs->fields ($i);
}
$rowcount = 0;
while (! $rs->eof) {
     for ($i =0; $i < $num _columns; $i + +)
      {
        echo htmlspecialchars ($fld [$i]->value). "T";
   }
     echo "<br/>n";
    $rowcount ++;           //ROWCOUNT Self-increasing
    $rs->movenext ();

$rs->close ();//Close the DataSet
$conn->close ();
?;