Using PHP to operate MySQL Databases

In the database, data query is completed using the Select statement. The Select statement can retrieve data from the database based on the conditions specified by the user and return the query results in a table. For example, to query the library information in the library information table, the title is the full manual developed by the PHP database system. The Code is as follows: $ sqlmysql_query (select *

In the database, data query is completed using the Select statement. The Select statement can retrieve data from the database based on the conditions specified by the user and return the query results in a table. For example, to query the library information in the library information table, the title is the full manual developed by the PHP database system. The Code is as follows: $ SQL = mysql_query ("select *

In the database, data query is completed using the Select statement. The Select statement can retrieve data from the database based on the conditions specified by the user and return the query results in a table.

For example, you can query the book information in the library information table named "PHP Database System Development full manual. The Code is as follows:

$ SQL = mysql_query ("select * from tab_book where bookname = 'php Database System Development full Manual '");

$ Info = mysql_fetch_array ($ SQL );

The following describes the data query methods and techniques through examples.

1) Use the include file command to reference the database configuration file to access the database. The Code is as follows:

(2) create a record set. First, assign a value to the Session variable by receiving the value passed in the form, and then use If... Then... Else condition statements determine the operator selected by the user, and then execute the corresponding SQL statement to retrieve information about the product and determine whether the record set is empty. If no matching record is found at the end of the record, a prompt is displayed. The main program code is as follows:

? Php

$ Txt_sel = $ _ POST [txt_sel];

$ Txt_tj =$ _ POST [txt_tj];

$ Txt_book = $ _ POST [txt_book];

If ($ _ POST [Submit] = "query "){

If ($ _ POST [txt_tj] = "like") {// if the selected condition is "like", perform a fuzzy query

$ SQL = mysql_query ("select * from tab_book where". $ txt_sel. "like '%". $ txt_book. "% '");

$ Info = mysql_fetch_array ($ SQL );

}

If ($ _ POST [txt_tj] = "= "){

$ SQL = mysql_query ("select * from tab_book where". $ txt_sel. "= '". $ txt_book ."'");

$ Info = mysql_fetch_array ($ SQL );

}

If ($ _ POST [txt_tj] = "> "){

$ SQL = mysql_query ("select * from tab_book where". $ txt_sel. "> '". $ txt_book ."'");

$ Info = mysql_fetch_array ($ SQL );

}

If ($ _ POST [txt_tj] = "<"){

$ SQL = mysql_query ("select * from tab_book where". $ txt_sel. "<'". $ txt_book ."'");

$ Info = mysql_fetch_array ($ SQL );

}

Else {

If ($ info = false) {// if the retrieved information does not exist, a prompt is displayed.

Echo"

Sorry, the information you retrieved does not exist!

";

}

}

}

?>

Note: In this example, the wildcard "%" is used for fuzzy search ". '%' Indicates any zero or multiple characters.

(3) Use Do... The While LOOP statement outputs data information in a table to the browser. The Code is as follows:

Do {

?>

 

 

 

 

}

While ($ info = mysql_fetch_array ($ SQL ));

?>

Query data in a specified time period

When querying date data, you often need to query data within a certain period of time. First, we will introduce a simple date query.

For example, you can use an SQL statement to query the purchase information at the specified time in the purchase information table. The Code is as follows:

$ SQL = mysql_query ("select * from tb_cgdan where cgdate> '2017-01-12 '");

$ Result = mysql_fetch_array ($ SQL );

Use BETWEEN… in SQL statements... The AND statement can be used to query time periods.

BETWEEN... The syntax format of the AND statement is as follows:

Test_expression [NOT] BETWEEN begin_expression AND end_expression

Test_expression: expression used for testing within the range defined by begin_expression and end_expression.

NOT: The result of the specified predicate is reversed.

Begin_expression: Any valid expression.

Begin_expression is the start date of the time period.

End_expression: Any valid expression.

Test_expression is the end date of the time period.

AND: as a placeholder, test_expression should be in the range specified by begin_expression AND end_expression.

The following describes how to query data records within a specified period of time.

Example 09-05 query data in a specified time period	Example location: mr/fl/09/05
Video location: mr/lx/09

The implementation process of this example is as follows.

(1) Use the include command to reference the database configuration file to access the database. The Code is as follows:

(2) Use the composite condition... The AND statement retrieves the product procurement information within a specified period of time. The main program code is as follows:

If ($ _ POST ["subb"] <> ""){

$ _ SESSION ["sdate"] = $ _ POST ["sdate"];

$ _ SESSION ["edate"] = $ _ POST ["edate"];

$ SQL = mysql_query ("select * from tb_cgdan where cgdate '". $ _ SESSION ["sdate"]. "'and '". $ _ SESSION ["edate"]. "'");

$ Result = mysql_fetch_array ($ SQL );

If ($ result = false ){

Echo"

Sorry! No products purchased in this period of time!

";

}

?>

(3) Use Do... The While loop output statement outputs the qualified procurement information to the browser. The Code is as follows:

Do {

?>

} While ($ result = mysql_fetch_array ($ SQL ));

}

Mysql_close ();

?>

Query the top 5 best-selling products

To query the best-selling items, you must use the order by clause to sort the query results based on one or more fields, and then use the LIMIT clause to LIMIT the number of rows returned BY the Select statement. LIMIT takes one or two parameters. If two parameters are specified, the first parameter is used to specify the offset of the first row to be returned, and the second parameter is used to specify the maximum number of returned rows. The offset of the initial row is 0 (not 1 ).

Mysql> select * from table LIMIT 5, 10; # Retrieve rows 6-15

If a parameter is specified, it indicates the maximum number of returned rows.

Mysql> select * from table LIMIT 5; # Retrieve first 5 rows

That is, LIMIT n is equivalent to LIMIT 0, n.

Data Query has an extremely important function, that is, it can query the top or the last. The following is an example.

Example 09-06 query the top 5 best-selling products	Example location: mr/fl/09/06
Video location: mr/lx/09

When querying, you can not only query records with the same field information, but also query data information in a specific range. Run this example. As shown in Figure 9.10, the detail page displays all information in the tb_stocks data table. Click the query button to calculate the top 5 most popular products (this example is sorted in descending order of the total sales volume, and then the first 5 Records of the query results are returned) and output the result to the browser.

(1) Use the nested framework technology to layout the Example page. The Code is as follows:

(2) Use the include command to reference the database configuration file to access the database. The Code is as follows:

(3) create a record set. Use the LIMIT 5 clause to return the first five records that satisfy the Where clause. The Code is as follows:

If ($ _ POST [subb] = "query "){

$ SQL = mysql_query ("select spname, cd, dw, price, sum (xssl) as sum_xssl, sum (xsje) as sum_xsje from tb _ stocks group by spname, cd, dw, price order by sum_xssl desc LIMIT 5 ");

$ Result = mysql_fetch_array ($ SQL );

}

?>

(4) use Do... The While LOOP statement outputs the query result. The Code is as follows:

Do {

?>

 

 

} While ($ result = mysql_fetch_array ($ SQL ));

?>

Multi-Table grouping statistics on Product Sales

In query statistics, query results are often sorted and grouped. The SUM aggregate function is used to return the SUM of all values in the expression or only the DISTINCT value. It must be noted that the SUM clustering function can only be used for columns whose data type is numeric, And the NULL value is ignored.

Syntax:

SUM ([ALL | DISTINCT] expression)

The ALL parameter indicates the clustering function operation on ALL values, which is the default value. The DISTINCT parameter is used to specify the SUM of the unique values returned by SUM. The expression parameter is a constant, column, or function, or any combination of arithmetic, bitwise, and string operators. Expression is an expression of exact or approximate numeric data type classification (except bit data type. In other cases, Aggregate functions and subqueries are not allowed.

Note: The SUM function ignores NULL values when adding values in the column. However, if all values in the column are NULL, the SUM function returns NULL as the result.

The following describes the application of multi-Table grouping statistics through examples.

Example 09-07 multi-Table grouping statistics product sales	Example location: mr/fl/09/07
Video location: mr/lx/09

In this example, the product sales information table and the product inventory information table are used to query the product sales quantity and the existing quantity, and group by product number. Run this example and click statistics to output the qualified statistics to the browser.

(1) Use the include command to reference the database configuration file to access the database. The Code is as follows:

(2) create a record set. Query the sales quantity and existing quantity of books in the book sales information table and book inventory information table, and group them by book code, book name, author, and so on, so as to group statistical data from multiple tables. The main program code is as follows:

If ($ _ POST [subb] = "Statistics "){

$ SQL = mysql_query ("select k. spid, k. spname, k. kcsl, sum (x. xssl) as sum_xssl from tb_stocks as k, tb_market as x where k. spid = x. spid group by k. spid, k. spname, k. kcsl ");

$ Result = mysql_fetch_array ($ SQL );

}

?>

(3) Use Do... The While statement cyclically outputs the inventory and sales information after statistics. The Code is as follows:

 

 

 

 

} While ($ result = mysql_fetch_array ($ SQL ));

Mysql_close ();

?>