1. Establish and close the connection
1) mysql_connect()
resource mysql_connect([string hostname [:port][:/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 caused by the prohibition of failed attempts, and the user will see the error message specified in die().
Note that when connecting with multiple mysqls, the link ID of each connection must be specified, 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(), it is: the existing link will be searched first and will be created only when it does not exist.
Note that there is no need to display 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])
Close the connection is not necessary, as it can be handled by mysql's garbage collection.
If link_id is not specified, close the most recent link.
2. Select the 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 parameters.
4. Get and display data
1) mysql_result()
mixed mysql_result(resource result_set, int row [,mixed field])
Get data for a field from the specified row of result_set. Simple but inefficient.
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 above code is just the field value of the first data in the result set. 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, then 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.
For example (note the clever cooperation 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().
Get each row of result_set as an associative array or/sum numeric index array.
By default, two arrays can be obtained, result_type can be set:
MYSQL_ASSOC: Returns the associative array, field name => field value
MYSQL_NUM: Returns the numeric index array.
MYSQL_BOTH: Gets two kinds of 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)
The same function as mysql_fetch_array(), but it returns 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 result_set.
Note that mysql_num_rows() is only valid when determining the number of records obtained by the select statement query. If you want to obtain the number of records affected by the insert/updata/delete query, you need to use mysql_affected_rows().
2) mysql_affected_rows()
int mysql_affected_rows([resource link_id])
Get the number of records affected by insert/update/delete query
Note that no parameters are required, and the most recent results of the recently established database connection are used by default. You can use the optional parameter link_id to select the database connection.
6. Get information about database and tables
1) mysql_list_dbs()
resource mysql_list_dbs([resource link_id])
Gets all 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 result is related to the user permissions used.
2) mysql_db_name()
string mysql_db_name(resource result_set, interger index)
Get the database name with index in the result_set returned by mysql_list_dbs().
3) mysql_list_tables()
resource mysql_list_tables(string database [,resource link_id])
Gets all table names in database.
4) mysql_tablename()
string mysql_tablename(resource result_set, interger index)
Get the table name in the result_set returned by mysql_list_tables() with the position index.
When learning PHP's COM and .Net (Windows) functions, I found an example of operating SQL SERVER through COM and searched for relevant information. So I came up with this article about PHP connecting ACCESS. I believe there are already many on the Internet, so I'd better post it here.
My machine environment: WIN2000, APACHE2, PHP Version 5.1.0RC1
<?php
$conn = new COM(“”) or die(“Cannot start ”);
$conn->Open(“Provider=.4.0;Data Source=D:\\php5\\”);
$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 increases automatically
$rs->MoveNext();
}
$rs->Close(); //Close the dataset
$conn->Close();
?>
1) mysql_connect()
resource mysql_connect([string hostname [:port][:/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 caused by the prohibition of failed attempts, and the user will see the error message specified in die().
Note that when connecting with multiple mysqls, the link ID of each connection must be specified, 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(), it is: the existing link will be searched first and will be created only when it does not exist.
Note that there is no need to display 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])
Close the connection is not necessary, as it can be handled by mysql's garbage collection.
If link_id is not specified, close the most recent link.
2. Select the 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 parameters.
4. Get and display data
1) mysql_result()
mixed mysql_result(resource result_set, int row [,mixed field])
Get data for a field from the specified row of result_set. Simple but inefficient.
Example:
Copy the codeThe code is as follows:
$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 above code is just the field value of the first data in the result set. If you want to output all records, you need to loop through it.
Copy the codeThe code is as follows:
…
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, then 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.
For example (note the clever cooperation with list):
Copy the codeThe code is as follows:
…
$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().
Get each row of result_set as an associative array or/sum numeric index array.
By default, two arrays can be obtained, result_type can be set:
MYSQL_ASSOC: Returns the associative array, field name => field value
MYSQL_NUM: Returns the numeric index array.
MYSQL_BOTH: Gets two kinds of arrays. Therefore, each field can be referenced by index offset or by field name.
Example:
Copy the codeThe code is as follows:
…
$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)
The same function as mysql_fetch_array(), but it returns not an array, but an object.
Example:
Copy the codeThe code is as follows:
…
$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 when determining the number of records obtained by the select statement query. If you want to obtain the number of records affected by the insert/updata/delete query, you need to use mysql_affected_rows().
2) mysql_affected_rows()
int mysql_affected_rows([resource link_id])
Get the number of records affected by insert/update/delete query
Note that no parameters are required, and the most recent results of the recently established database connection are used by default. You can use the optional parameter link_id to select the database connection.
6. Get information about database and tables
1) mysql_list_dbs()
resource mysql_list_dbs([resource link_id])
Gets all database names on the server.
Example:
Copy the codeThe code is as follows:
mysql_connect(“localhost”, “name”,”pwd”);
$dbs = mysql_list_dbs();
while (list($db) = mysql_fetch_row(dbs)) {
echo “$db <br>”;
}
Note that the output result is related to the user permissions used.
2) mysql_db_name()
string mysql_db_name(resource result_set, interger index)
Get the database name with index in the result_set returned by mysql_list_dbs().
3) mysql_list_tables()
resource mysql_list_tables(string database [,resource link_id])
Gets all table names in database.
4) mysql_tablename()
string mysql_tablename(resource result_set, interger index)
Get the table name in the result_set returned by mysql_list_tables() with the position index.
When learning PHP's COM and .Net (Windows) functions, I found an example of operating SQL SERVER through COM and searched for relevant information. So I came up with this article about PHP connecting ACCESS. I believe there are already many on the Internet, so I'd better post it here.
My machine environment: WIN2000, APACHE2, PHP Version 5.1.0RC1
Copy the codeThe code is as follows:
<?php
$conn = new COM(“”) or die(“Cannot start ”);
$conn->Open(“Provider=.4.0;Data Source=D:\\php5\\”);
$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 increases automatically
$rs->MoveNext();
}
$rs->Close(); //Close the dataset
$conn->Close();
?>