PHP operations mysql functions mysql and php interaction functions
- 2020-05-07 19:17:54
- OfStack
1. Establish and close connections
1) mysql_connect()
resource mysql_connect([string hostname [:port][:/path/to/socket][,string username] [,string password]])
All parameters are optional
For example:
@ mysql_connect (" localhost ", "user", "password")
or die(" Could not connect to mysql server! );
Note that the @ symbol represents any error message that prevents a failed attempt, and the user will see the error message specified in die().
Note that when connecting to more than one mysql, you must specify the link ID for each connection, as follows:
$link1 = @mysql_connect (" server1 ", "user", "password")
or die(" Could not connect to mysql server! );
$link2 = @mysql_connect (" server2 ", "user", "password")
or die(" Could not connect to mysql server! );
2) mysql_pconnect()
resource mysql_pconnect([string hostname [:port][:/path/to/socket][,string username] [,string password]])
Unlike mysql_connect(), an existing link is looked for first and created when it does not exist.
Note that there is no need to show the closed 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's garbage collection.
If link_id is not specified, close the nearest link.
2. Select a 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])
It is equivalent to mysql_select_db() + mysql_query(), which can be clearly seen from the parameter.
4. Get and display data
1) mysql_result()
mixed mysql_result(resource result_set, int row [,mixed field])
Get 1 field data from result_set specified row. Simple but inefficient.
For example:
Note that the code above is just the field value of the first data in the output set, and if you want to output all records, you need to loop through it.
Note that if the query field name is an alias, an alias is used in mysql_result.
2) mysql_fetch_row()
array mysql_fetch_row(resource result_set)
Get the entire row from result_set and put the data into the array.
Examples (note the clever coordination with list):
3) mysql_fetch_array()
array mysql_fetch_array(resource result_set [,int result_type])
An enhanced version of mysql_fetch_row().
Get each row of result_set as an associative or/and numerically indexed array.
Get two arrays by default,result_type can be set:
MYSQL_ASSOC: returns an associative array, field name = > The field values
MYSQL_NUM: returns an array of numeric indexes.
MYSQL_BOTH: gets two types of arrays, so each field can be referenced either by index offset or by field name.
For example:
4) mysql_fetch_assoc()
array mysql_fetch_assoc(resource result_set)
mysql_fetch_array($result, MYSQL_ASSOC)
5) mysql_fetch_object()
object mysql_fetch_object(resource result_set)
It does the same thing as mysql_fetch_array(), but instead of an array, it returns an object.
For example:
5. Selected records and affected records
1) mysql_num_rows()
int mysql_num_rows(resource result_set)
Returns the number of rows in result_set.
Note that mysql_num_rows() is only valid to determine the number of records obtained by the select statement query. To obtain the number of records affected by the insert/updata/delete query, mysql_affected_rows() is required.
2) mysql_affected_rows()
int mysql_affected_rows([resource link_id])
Gets the number of records affected by insert/updata/delete queries
Note that you do not need to enter parameters, but default to the most recent result of the most recently established database connection. You can use the optional parameter link_id to select the database connection.
Get information about databases and tables
1) mysql_list_dbs()
resource mysql_list_dbs([resource link_id])
Gets all database names on the server.
For example:
Note that the output is related to the user permissions used.
2) mysql_db_name()
string mysql_db_name(resource result_set, interger index)
Gets the database name index in result_set returned in mysql_list_dbs().
3) mysql_list_tables()
resource mysql_list_tables(string database [,resource link_id])
Gets all the table names in database.
4) mysql_tablename()
string mysql_tablename(resource result_set, interger index)
Gets the table name returned by mysql_list_tables() with result_set at index.
When learning PHP's COM and.Net (Windows) functions, I found an example of SQL SERVER via COM operation SQL SERVER, and found the relevant information, so I have this article PHP connected ACCESS.
My machine environment: WIN2000, APACHE2, PHP Version 5.1.0RC1
1) mysql_connect()
resource mysql_connect([string hostname [:port][:/path/to/socket][,string username] [,string password]])
All parameters are optional
For example:
@ mysql_connect (" localhost ", "user", "password")
or die(" Could not connect to mysql server! );
Note that the @ symbol represents any error message that prevents a failed attempt, and the user will see the error message specified in die().
Note that when connecting to more than one mysql, you must specify the link ID for each connection, as follows:
$link1 = @mysql_connect (" server1 ", "user", "password")
or die(" Could not connect to mysql server! );
$link2 = @mysql_connect (" server2 ", "user", "password")
or die(" Could not connect to mysql server! );
2) mysql_pconnect()
resource mysql_pconnect([string hostname [:port][:/path/to/socket][,string username] [,string password]])
Unlike mysql_connect(), an existing link is looked for first and created when it does not exist.
Note that there is no need to show the closed 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's garbage collection.
If link_id is not specified, close the nearest link.
2. Select a 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])
It is equivalent to mysql_select_db() + mysql_query(), which can be clearly seen from the parameter.
4. Get and display data
1) mysql_result()
mixed mysql_result(resource result_set, int row [,mixed field])
Get 1 field data from result_set specified row. Simple but inefficient.
For example:
$link1 = @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 code above is just the field value of the first data in the output set, and if you want to output all records, you need to loop through it.
...
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, an alias is used in mysql_result.
2) mysql_fetch_row()
array mysql_fetch_row(resource result_set)
Get the entire row from result_set and put the data into the array.
Examples (note the clever coordination 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])
An enhanced version of mysql_fetch_row().
Get each row of result_set as an associative or/and numerically indexed array.
Get two arrays by default,result_type can be set:
MYSQL_ASSOC: returns an associative array, field name = > The field values
MYSQL_NUM: returns an array of numeric indexes.
MYSQL_BOTH: gets two types of arrays, so each field can be referenced either by index offset or by field name.
For 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)
mysql_fetch_array($result, MYSQL_ASSOC)
5) mysql_fetch_object()
object mysql_fetch_object(resource result_set)
It does the same thing as mysql_fetch_array(), but instead of an array, it returns an object.
For 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 result_set.
Note that mysql_num_rows() is only valid to determine the number of records obtained by the select statement query. To obtain the number of records affected by the insert/updata/delete query, mysql_affected_rows() is required.
2) mysql_affected_rows()
int mysql_affected_rows([resource link_id])
Gets the number of records affected by insert/updata/delete queries
Note that you do not need to enter parameters, but default to the most recent result of the most recently established database connection. You can use the optional parameter link_id to select the database connection.
Get information about databases and tables
1) mysql_list_dbs()
resource mysql_list_dbs([resource link_id])
Gets all database names on the server.
For 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 permissions used.
2) mysql_db_name()
string mysql_db_name(resource result_set, interger index)
Gets the database name index in result_set returned in mysql_list_dbs().
3) mysql_list_tables()
resource mysql_list_tables(string database [,resource link_id])
Gets all the table names in database.
4) mysql_tablename()
string mysql_tablename(resource result_set, interger index)
Gets the table name returned by mysql_list_tables() with result_set at index.
When learning PHP's COM and.Net (Windows) functions, I found an example of SQL SERVER via COM operation SQL SERVER, and found the relevant information, so I have this article PHP connected ACCESS.
My machine environment: WIN2000, APACHE2, PHP Version 5.1.0RC1
<?php
$conn = new COM( " ADODB.Connection " ) or die( " Cannot 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();
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 Since the increase
$rs->MoveNext();
}
$rs->Close(); // Close the data set
$conn->Close();
?>