PHP database operation code collection

$conn = @mysql_connect("localhost", "username", "password") or die("Cannot connect to Mysql Server");

Note: The closed connection must be displayed when using this connection

2. mysql_pconnect()-Establish a database connection Format: 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");

Note: Using this connection function does not require closing the connection explicitly. It is equivalent to using a connection pool

3. mysql_close()-Close the database connection example:

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

4. mysql_select_db()-Select database Format: boolean mysql_select_db(string db_name [, resource link_id]) example:

$conn = @mysql_connect("localhost", "username", "password") or die("Cannot connect to Mysql Server");
@mysql_select_db("MyDatabase") or die("Cannot select This database, or the database does not exist");

5. mysql_query()-Query MySQL Format: resource mysql_query (string query, [resource link_id]) example:

$linkId = @mysql_connect("localhost", "username", "password") or die("Cannot connect to Mysql Server");
@mysql_select_db("MyDatabase") or die("Cannot select This database, or the database does not exist");
$query = "select * from MyTable";
$result = mysql_query($query);
mysql_close();

Note: If SQL query If the execution is successful, the resource identifier is returned, and if it fails, FALSE is returned. If the update is executed successfully, it returns TRUE, otherwise it returns FALSE

6. mysql_db_query()-Query MySQL Format: resource mysql_db_query(string database, string query [, resource link_id]) example:

$linkId = @mysql_connect("localhost", "username", "password") or die("Cannot connect to MysqlServer");
$query = "select * from MyTable";
$result = mysql_db_query("MyDatabase", $query);
mysql_close();

Note: In order to make the code clear, it is not recommended to use this function call

7. mysql_result()-obtain and display data Format: mixed mysql_result (resource result_set, int row [, mixed field]) example:

$query = "select id, name from MyTable order by name";
$result = mysql_query($query);
for($count=0;$count<=mysql_numrows($result);$ count++)
{
$c_id = mysql_result($result, 0, "id");
$c_name = mysql_result($result, 0, "name");
echo $c_id,$c_name;
}

Description: The simplest and least efficient data acquisition function

8. mysql_fetch_row()-Get and display data Format: array mysql_fetch_row (resource result_set) example:

$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) ");
}

Description: The function gets the entire data row from result_set and puts the values ​​in an indexed array. Usually the list() function is used

9. mysql_fetch_array()-Get and display data Format: array mysql_fetch_array (resource result_set [, int result_type]) example:

$query = "select id, name from MyTable order by name";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$id = $row["id"];
$name = $row["name"];
echo "Name: $name ($id) ";
}

Another example:

$query = "select id, name from MyTable order by name";
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_NUM)) {
$id = $row[0];
$name = $row[1];
echo "Name: $name ($id) ";
}

Instructions: The values ​​of result_type 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 numerical index array. The default value of result_type.

10. mysql_fetch_assoc()-Get and display data Format: array mysql_fetch_assoc (resource result_set) Equivalent to calling mysql_fetch_array(resource, MYSQL_ASSOC);

11. mysql_fetch_object()-Get and display data Format: object mysql_fetch_object(resource result_set) example:

$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) ";
}

Description: Returns an object, which is the same as mysql_fetch_array() in operation

12. mysql_num_rows()-the number of selected records Format: int mysql_num_rows(resource result_set) example:

query = "select id, name from MyTable where id > 65";
$result = mysql_query($query);
echo "There are ".mysql_num_rows($result)." records with IDs greater than 65";

Description: Only useful when determining the number of records obtained by select query.

13. mysql_affected_rows() - the number of records affected by Insert, update, delete Format: int mysql_affected_rows([resource link_id]) example:

$query = "update MyTable set name='CheneyFu' 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();

Description: This function gets the number of rows affected by the INSERT, UPDATE or DELETE update statement

14. mysql_list_dbs()-Get database list information Format: resource mysql_list_dbs([resource link_id]) example:

mysql_connect("localhost", "username", "password");
$dbs = mysql_list_dbs();
echo "Databases: ";
while (list($db) = mysql_fetch_rows($dbs)) {
echo "$db ";
}

Description: Display all database names

15. mysql_db_name()-Get the database name Format: string mysql_db_name(resource result_set, integer index) Description: This function obtains the database name located at the specified index in the result_set returned by mysql_list_dbs()

16. mysql_list_tables()-Get the list of database tables Format: resource mysql_list_tables(string database [, resource link_id]) example:

mysql_connect("localhost", "username", "password");
$tables = mysql_list_tables("MyDatabase");
while (list($table) = mysql_fetch_row($tables)) {
echo "$table ";
}

Description: This function gets the table names of all tables in the database

17. mysql_tablename()-Get the name of a database table Format: string mysql_tablename(resource result_set, integer index) example:

mysql_connect("localhost", "username", "password");
$tables = mysql_list_tables("MyDatabase");
$count = -1;
while (++$count < mysql_numrows( $tables)) {
echo mysql_tablename($tables, $count)."";
}

Description: This function gets the table name located at the specified index in the result_set returned by mysql_list_tables()

18. mysql_fetch_field()-Get field information Format: object mysql_fetch_field(resource result [, int field_offset]) example:

mysql_connect("localhost", "username", "password");
mysql_select_db("MyDatabase");
$query = "select * from MyTable";
$result = mysql_query($query);
$counts = mysql_num_fields($result);
for($count = 0; $count < $counts; $count++) {
$field = mysql_fetch_field($result, $count);
echo "$ field->name $field->type ($field->max_length) ";
}

Instructions: The returned object has a total of 12 object properties: 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, otherwise 0 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

19. mysql_num_fields()-Get the number of fields in the query Format: integer mysql_num_fields(resource result_set) example:

$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)." ";

20. mysql_list_fields()-Get the field names of all fields in the specified table Format: resource mysql_list_fields (string database_name, string table_name [, resource link_id]) example:

$fields =mysql_list_fields("MyDatabase", "MyTable");
echo "The number of fields in the table MyTable in the database MyDatabase: ".mysql_num_fields($fields)."";

21, mysql_field_flags()-Get the specified field options Format: string mysql_field_flags (resource result_set, integer field_offset) example:

$query = "select id, name from MyTable order by name";
$result = mysql_query($query);
$row=mysql_fetch_wor($row);

22 , mysql_field_len()-Get the maximum length of the specified field Format: integer mysql_field_len (resource result_set, integer field_offset) example:

$query = "select name from MyTable";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
echo mysql_field_len($result, 0)."";

illustrate: If mysql_field_len($reseult, 0) = 16777215 Then numer_format(mysql_field_len($result)) is equal to 16,777,215

23. mysql_field_name()-get the field name Format: string mysql_field_name (resource result_set, int field_offset) example:

$query = "select id as PKID, name from MyTable order by name";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
echo mysql_field_name($result, 0); // Result: PKID

24, mysql_field_type()-get the field type Format: string mysql_field_type (resource result_set, int field_offset) example:

$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

25, mysql_field_table()-Get the table name where the field is located Format: string mysql_field_table (resource result_set, int field_offset) example:

$query = "select id as PKID, name from MyTable order by name";
$result = mysql_query($query);
$row = mysql_fetch_row($result);
echo mysql_field_table($result, 0); // Result: MyTable