PHP links to common MySQL extension functions, and php links to mysql

Source: Internet
Author: User
Tags field table mysql functions php database

PHP links to common MySQL extension functions, and php links to mysql

I. PHP database connection and basic operations

MySQL adopts the 'client/Server' architecture. Using the MySQL extension function installed in PHP is the same as directly accessing the MySQL database server using the client software zone. In the same principle, you must send an SQL command to the MySQL management system and then return the result to the user.

In PHP, SQL is classified into two types (view SQL statements): one is a DQL statement with a returned result set, such as the select/desc table name. After execution, PHP is required to process the result set. Second, there is no result set, such as DML and DDL. However, after the DML statement is executed successfully, the data table records are affected.

<? Php
// Connect to the database. Common parameters include host name, user name, and password.
$ Link = mysql_connect ('localhost', 'root', '123 ');
// Determine whether the connection is successful
If (! $ Link)
{
Die ('Connection failed'. mysql. error (); // resource ID returned for successful connection; false returned for Failed Connections; error message displayed for mysql_error
}

// Select the database. mysql_error () is only used in debugging and will not be used when the project is deployed. Otherwise, the database information will be leaked.
Mysql_select_db ('test') or die ('database selection failed'. mysql_error ());

// Mysql_query () can set character sets and execute SQL statements
Mysql_query ('set names UTF-8 ');
$ SQL = 'insert into test (id, name) values ("1", "dwqs ")';
$ Result = mysql_query ($ SQL); // execute the SQL statement to return the result set.

// Process the result set. insert is a DML type and affects the table records.
If ($ result & mysql_affected_rows ()> 0)
{
// Mysql_insert_id () returns the auto_increment value of the last new record
Echo 'data inserted successfully'. mysql_insert_id (). '<br/> ';
}
Else
{
Echo 'failed to insert data, error code: '. mysql_errno ().' error message: '. mysql_error ().' <br/> ';
}

// Close the connection
Mysql_close ($ link );
?>

Ii. PHP processes the select query result set

Execute the select statement in PHP to return a result set, which can be used to process each field

$ Result = mysql_query ('select * from test ');
// Obtain the number of record rows
$ Rows = mysql_num_rows ($ result );
// Obtain the number of fields, that is, the data column
$ Cols = mysql_num_fields ($ result );

If you need to access data in the result set, you can use one of the following four functions (the resource operator of the result set is used as a parameter, and the next record is automatically returned, and false is returned at the end of the table)

1. mysql_fetch_row (): This function returns a result record and stores it with a common index data.

2. mysql_fetch_assoc (): obtain a row from the result set and save the row as the associated data.

3. mysql_fetch_array (): Get a row from the result set as an associated array, or a number array, or both. You can use MYSQL_ASSOC (associated array form), MYSQL_NUM (Index Array form), and MYSQL_BOTH as the second parameter to specify the returned data form.

4. mysql_fetch_object (): obtains a row from the result set as an object, and each field is accessed as an object.

Suggestion: there are no special requirements. Do not use mysql_fetch_array (). You can use mysql_fetch_row () or mysql_fetch_assoc () to implement the same function and improve efficiency.

There are also three common functions related to the result set.

5. mysql_data_seek (int $ num): Move the internal result pointer. $ num is the number of rows of the new result set pointer to be set.

6. mysql_fetch_lengths (resource$result): Gets the length of each output in the result set.

7. mysql_result (resource$result, Int$row[,mixed $field]): Returns the content of a unit in the MySQL result set. The field parameter can be the offset or field name of the field, or tablename. fieldname of the field table ). If an alias ('select foo as bar from... '), The column name is replaced by an alias. Calling mysql_result () cannot be used together with other functions that process result sets.


Application of php to connect mysql Functions

The mysql_fetch_array () function retrieves a row from the result set as an associated array, or an array of numbers, or both.
Returns the array generated based on the rows obtained from the result set. If no more rows exist, false is returned.

Mysql_fetch_array (data, array_type)
Parameter data: Optional. Specifies the Data Pointer to be used. This Data Pointer is produced by the mysql_query () function.
Parameter: array_type. Optional. Specifies the result to be returned. Value Options: MYSQL_ASSOC-associated array
MYSQL_NUM-number Array
MYSQL_BOTH-default. Generate join and number arrays at the same time.
Note: mysql_fetch_array () is an extended version of mysql_fetch_row. In addition to storing data in an array as a digital index, you can also store data as an associated index and use the field name as the key name.

Example:
<? Php
$ Con= mysql_connect ("localhost", "hello", "321 ");
If (! $ Con)
{
Die ('could not connect: '. mysql_error ());
}

$ Db_selected = mysql_select_db ("test_db", $ con );
$ SQL = "SELECT * from Person WHERE Lastname = 'adams '";
$ Result = mysql_query ($ SQL, $ con );
Print_r (mysql_fetch_array ($ result ));

Mysql_close ($ con );
?>
The output is similar:
Array
(
[0] => Adams
[LastName] => Adams
[1] => John
[FirstName] => John
[2] => London
[City] => London
)
///////////////////////
The mysql_fetch_assoc () function retrieves a row from the result set as an associated array.
Returns the associated array generated based on the rows obtained from the result set. If no more rows exist, false is returned.
Mysql_fetch_assoc (data)
Parameter: data Pointer to be used. The Data Pointer is the result returned from mysql_query.

Note: mysql_fetch_assoc () is identical to mysql_fetch_array () and the second optional parameter MYSQL_ASSOC. It only returns the associated array. This is also the initial working method of mysql_fetch_array.
Tip: If you need a numeric index in addition to the associated index, use mysql_fetch_array ().
Note: The field names returned by this function are case sensitive.

Example:
<? Php
$ Con= mysql_connect ("localhost", "hello", "321 ");
If (! $ Con)
{
Die ('could not connect: '. mysq ...... remaining full text>

For php functions connected to mysql

Because the complete code is not displayed, the following code is displayed:

1. Notice: Undefined variable: db in C: \ xampp \ htdocs \ shop \ files \ mysql. php on line 5
Warning: database of non-literal variables (row 5th is not clear about which line of code ).

This error message indicates that the cause of the known code is that you reference a variable (db) defined by the function in the function body. The Code does not actually notice this, the scope of the variable (global or local) is incorrectly applied.

In short, the database cannot be found in the select_mycx function.

Solution:

(1). Pass in with parameters.

Function select_mycx ($ table, $ by, $ select_str, $ number, $ db)
{
.....
}

(2) define global variable reference in the parameter body:

Function select_mycx ($ table, $ by, $ select_str, $ number)
{
Global $ db;

....
}

2. Fatal error: Call to a member function query () on a non-object in C: \ xampp \ htdocs \ shop \ files \ mysql. php on line 5

This error is actually caused by the preceding error. Because $ db is not correctly introduced, query cannot be correctly executed.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.