Basic knowledge of operating MYSQL with PHP-PHP Tutorial-php.cn

mysql_connect()
resource mysql_connect([string hostname [:port] [:/path/to/socket] [, string username] [, string password]])
Example: $conn = @mysql_connect(" localhost", "username", "password") or dir("Cannot connect to Mysql Server");
Using this connection must show the closing of the connection

Copy the code

Establish a database connection

mysql_pconnect()
resource mysql_pconnect([string hostname [:port] [:/path/to/socket] [, string username] [, string password]])
Example: $conn = @mysql_pconnect("localhost" , "username", "password") or dir("Cannot connect to Mysql Server");
Using this connection function does not require closing the connection explicitly. It is equivalent to using a connection pool

Copy code

Close the database connection

mysql_close()
$conn = @mysql_connect("localhost", "username", "password") or die("Cannot connect to Mysql Server");
@mysql_select_db(" MyDatabase") or die("This database cannot be selected, or the database does not exist");
echo "You have connected to the MyDatabase database";
mysql_close();

Copy code

Select database

mysql_select_db()
boolean mysql_select_db(string db_name [, resource link_id])
$conn = @mysql_connect("localhost", "username", "password") or die(" Cannot connect to Mysql Server");
@mysql_select_db("MyDatabase") or die("This database cannot be selected, or the database does not exist");

Copy code

Query MySQL

mysql_query()
resource mysql_query (string query, [resource link_id])
$linkId = @mysql_connect("localhost", "username", "password") or die("Cannot connect to Mysql Server");
@ mysql_select_db("MyDatabase") or die("This database cannot be selected, or the database does not exist");
$query = "select * from MyTable";
$result = mysql_query($query);
mysql_close();
if If the SQL query is executed successfully, the resource identifier will be returned, and if it fails, FALSE will be returned. If the update is executed successfully, it returns TRUE, otherwise it returns FALSE

Copy code

Query MySQL

mysql_db_query()
resource mysql_db_query(string database, string query [, resource link_id])
$linkId = @mysql_connect("localhost", "username", "password") or die("Cannot Connect to MysqlServer");
$query = "select * from MyTable";
$result = mysql_db_query("MyDatabase", $query);
mysql_close();
In order to make the code clear, it is not recommended to use this function call

Copy code

Get and display data

mysql_result()
mixed mysql_result (resource result_set, int row [, mixed field])
$query = "select id, name from MyTable order by name";
$result = mysql_query($query);
$c_id = mysql_result($result, 0, "id");
$c_name = mysql_result($result, 0, "name");
The simplest, It is also the least efficient data acquisition function

Copy code

Get and display data

mysql_fetch_row()
array mysql_fetch_row (resource result_set)
$query = "select id, name from MyTable order by name";
$result = mysql_query($query);
while (list($id, $name) = mysql_fetch_row($result)) {
echo("Name: $name ($id)
");
}
The function gets the entire data row from result_set and places the values in an indexed array. Usually the list() function is used

Copy code

Get and display data

mysql_fetch_array()
array mysql_fetch_array (resource result_set [, int result_type])
$query = "select id, name from MyTable order by name";
$resul t = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$id = $row["id"];
$name = $row["name"];
echo "Name: $name ($id)
";
}
result_type values are:
MYSQL_ASSOC: The field name represents the key, and the field content is the value
MYSQL_NUM: Numeric index array, the operation is the same as the mysql_fetch_ros() function
MYSQL_BOTH : Returned both as an associative array and as a numeric index array. The default value of result_type.

Copy code

Get and display data

mysql_fetch_assoc()
array mysql_fetch_assoc (resource result_set)
Equivalent to calling mysql_fetch_array(resource, MYSQL_ASSOC);

Copy code

Get and display data

mysql_fetch_object()
object mysql_fetch_object(resource result_set)
$query = "select id, name from MyTable order by name";
while ($row = mysql_fetch_object($result)) {
$id = $row->id ;
$name = $row->name;
echo "Name: $name ($id)
";
}
Is the same as mysql_fetch_array() in operation

Copy code

Selected records

mysql_num_rows()
int mysql_num_rows(resource result_set)
#query = "select id, name from MyTable where id > 65";
$result = mysql_que ry($query );
echo "There are ".mysql_num_rows($result)." records with IDs greater than 65";
This is only useful when determining the number of records obtained by the select query.

Copy code

Affected records

mysql_affected_rows()
int mysql_affected_rows([resource link_id])
$query = "update MyTable set name="Che neyFu" where id> =5";
$result = mysql_query($query);
echo "The number of updated records with names with ID greater than or equal to 5:".mysql_affected_rows();
This function obtains the number of records affected by the INSERT, UPDATE or DELETE update statement Number of lines

Copy code

Get database list information

mysql_list_dbs()
resource mysql_list_dbs([resource link_id])
mysql_connect("local host", "username", "password" );
$dbs = mysql_list_dbs();
echo "Databases:
";
while (list($db) = mysql_fetch_rows($dbs)) {
echo "$db
";
}

Copy code

Get the database name

mysql_db_name()
string mysql_db_name(resource result_set, integer index)
This function gets the mys In the result_set returned by ql_list_dbs() The database name located at the specified index index

Copy code

Get the database table list

mysql_list_tables()
resource mysql_list_tables(string database [, resource link_id])
mysql_ connect("localhost" , "username", "password");
$tables = mysql_list_tables("MyDatabase");
while (list($table) = mysql_fetch_row($tables)) {
echo "$table
";
}This function gets the table names of all tables in the database

Copy code

Gets the database table names

mysql_tablename()
string mysql_tablename(resource result_set, integer index)
mysql_connect( "localhost", "username", "password");
$tables = mysql_list_tables("MyDatabase");
$count = -1;
while (++$count < mysql_numrows($tables)) {
echo mysql_tablename ($tables, $count)."
";
}
This function gets the table name located at the specified index in the result_set returned by mysql_list_tables()

Copy code

Get field information

mysql_fetch_field()
object mysql_fetch_field(resource result [, int field_offset])
mysql_connect("localhost", "username", "password");
mysql_ select_db("MyDatabase ");
$query = "select * from MyTable";
$result = mysql_query($query);
$fields = mysql_num_fields($result);
for($count = 0; $count < $fieds; $ count++) {
$field = mysql_fetch_field($result, $count);
echo "
$field->name $field->type ($field->max_length)
";
}

Copy code

The returned object has a total of 12 object attributes: name: field name table: the table where the field is located max_length: the maximum length of the field not_null: 1 if the field cannot be null, 0 otherwise primary_key: 1 if the field is the primary key, 0 otherwise unique_key: 1 if the field is a unique key, 0 otherwise multiple_key: 1 if the field is non-unique, 0 otherwise numeric: 1 if the field is numeric, 0 otherwise blob: 1 if the field is a BLOB, 0 otherwise type: the data type of the field unsigned: 1 if the field is an unsigned number, 0 otherwise zerofill: 1 if the field is "zero filled", 0 otherwise

Get the number of fields in the query

mysql_num_fields()
integer mysql_num_fields (resource result_set)
$query = "select id, name from MyTable order by name";
$result = mysql_query ($query );
echo "The number of fields in this query is: ".mysql_num_fields($result)."
";

Copy code

Return the number of fields in the query result_set

Get the field names of all fields in the specified table

mysql_list_fields()
resource mysql_list_fields (string database_name, string table_name [, resource link_id])
$fields = mysql_list_fields("MyDatabase", " MyTable ");
echo "The number of fields in the table MyTable in the database MyDatabase: ".mysql_num_fields($fields)."
";

Copy code

Get the specified field option

mysql_field_flags()
string mysql_field_flags (resource result_set, integer field_offset)

Copy code

Get the maximum length of the specified field

mysql_field_len()
integer mysql_field_len (resource result_set, integer field_offset)
$query = "select name from MyTable";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
echo mysql_field_len($result, 0)."< ;br />";
If mysql_field_len($reseult, 0) = 16777215
then numer_format(mysql_field_len($result)) is equal to 16,777,215

Copy code

Get the field name

mysql_field_name()
string mysql_field_name (resource result_set, int field_offset)
$query = "select id as PKID, name from MyTable order by name";
$result = mysql_query($query);
$row = mysql_fetch_row($result ; string mysql_field_type (resource result_set, int field_offset)
$query = "select id, name from MyTable order by name";
$result = mysql_query($query);

$row = mysql_fetch_row($result);

echo mysql_field_type($result, 0); // Result: int

Copy code

Get the table name where the field is located