Use MySQL to optimize your PHP Program

We sometimes implement multiple choices in the voting system, but most of our practices are to insert our data in a loop, as if in the following situations:
1 If (isset ($ _ post ['id']) {
2
3 foreach ($ _ post ['id'] as $ id ){
4
5 $ rs = mysql_query ('insert into table_name (ID) values ('. (INT) $ id .')';
6
7}
8
9} but this is a waste of resources, and it takes a lot of time to do so, because we all know that in PHP programs, the most time-consuming thing is to communicate with the database, therefore, we try to reduce the communication between PHP and the database. So how can we reduce the number of PHP database queries?
The following describes a method:
Use Stored Procedure (mysql5 only ):
We can use stored procedures to reduce the communication between PHP and MySQL databases. How can this problem be solved?
First, we can create a stored procedure:
1 delimiter //
2
3 create procedure insert_data (datas varchar (100 ))
4
5 begin
6
7 declare comma_pos int;
8
9 declare current_id int;
10
11 SVCs: loop
12
13 set comma_pos = Local (',', datas );
14
15 set current_id = substr (datas, 1, comma_pos );
16
17 if current_id <> 0 then
18
19 set datas = substr (datas, comma_pos + 1 );
20
21 else
22
23 set current_id = datas;
24
25 end if;
26
27 insert into table_name (ID) values (current_id );
28
29 If current_id = 0 then
30
31 leave SVCs;
32
33 end if;
34
35 end loop;
36
37 end
38
39 //
40
41 delimiter; this stored procedure accepts a string as a parameter, which is separated by IDs. This is because the MySQL stored procedure does not support arrays, so you can use this method to simulate arrays.
After creating this stored procedure, we can use PHP to call this stored procedure:
1 If (isset ($ _ post ['id']) {
2
3 $ datas = implode (',', $ _ post ['$ id']);
4
5 $ rs = mysql_query ('call insert_data ('. $ datas .')');
6
7} Then we can call a query to insert several pieces of data into MySQL. This reduces the communication between PHP and MySQL and speeds up the PHP operation.
In addition, the stored procedure can also help us solve the problem:
1. consistency: because our database is generally not only called for PHP, as in the library system, our MySQL database will also give. but every program needs to write a very long query to call the database, so we can also use the stored procedure to solve the problem of calling the database in multiple languages, you do not need to develop SQL statements repeatedly.
2. security: For many organizations with high confidentiality, such as banks and military secrets, we generally use stored procedures to restrict high access and modification to databases, in this way, we can protect our data!
3. efficiency: a large program cannot be completed by one person. Therefore, when developing a team, it is also a good solution to avoid repeated writing queries that call the database.
Because the stored procedure can implement general logic operations, sometimes it is very difficult to query the database data and use PHP to determine whether the data is suitable, we can allocate some simple logic operations to MySQL for processing, which will greatly improve the program efficiency! Because the stored procedure is a new function of mysql5, we should take a good look at this powerful function!