PHP returns the database's data array with the specified field as the index

In many cases, from getting started with a new project to developing the project, we can go back and carefully look at the code we have written. Many of them are our previous skillful code. Therefore, when completing each new project, you may need to make some project summary and code summary, which may be used in future projects and may be expected to achieve unexpected gains, such: code optimization, think of a better and faster implementation method, and so on.

Awesome program developers sometimes do not rely on the amount of code, but on the simplicity of the Code, complicated logic, but convenient implementation of the Program. This shows whether the program is a good programmer. We don't do programmers who work late at night and work hard on code!
I would like to share with you a few of the PHP programming skills I have used. Some of them are learned when I look at other people's code, and some are my own summary.

Returns the database data Array Based on the specified field.
It is easier to give an example:
If you want to count the traffic of a specified site from other websites and create a small background to view the traffic situation of each website on a daily basis. We will first create two data tables:
Table 1. Website configuration table (only count the traffic of these websites)Copy codeThe Code is as follows: create table 'site _ config '(
'Id' smallint (5) unsigned not null auto_increment COMMENT 'Primary key, auto incrementing ',
'Sid 'smallint (5) unsigned not null comment' website id ',
'Site _ url' varchar (128) not null comment 'site url address ',
'Site _ name' varchar (80) not null comment 'site name ',
'Add _ time' timestamp not null default CURRENT_TIMESTAMP COMMENT 'add time ',
Primary key ('id '),
Unique key 'adid' ('sid ')
) ENGINE = MyISAM default charset = utf8 COMMENT = 'site configuration table ';

Table 2. site traffic statistics table (one user record)Copy codeThe Code is as follows: create table 'site _ stat '(
'Id' int (11) unsigned not null auto_increment COMMENT 'Primary key, auto incrementing ',
'Sid 'smallint (5) unsigned not null comment' website id ',
'IP _ address' varchar (32) not null comment 'user ip ',
'Add _ time' timestamp not null default CURRENT_TIMESTAMP COMMENT 'add time ',
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8 COMMENT = 'site traffic statistics ';

Because the site_config table read operation is greater than the write operation, the type is set to MyISAM; while the site_stat table write operation is greater than the read operation, so I set the table type to InnoDB. (These areas also need to be considered when designing data, and the speed is improved a lot ).
Back to the question, if the table site_config contains the following data:
Data format: id, sid, site_url, add_time
1,200, baidu.com, 14:20:00
2,201, google.com, 14:20:00
3,202, cnblogs.com, 14:20:00
4,203, codejia.net, 14:20:00
Some data has been generated in the site traffic statistics table site_stat, as follows:
Data format: id, sid, ip_address, add_time
1,200,167.87 .32.4, 14:40:00
2,200,192.168 .11.56, 14:40:10
3,202,167.87 .32.4, 14:40:10
4,202,192.168 .11.56, 14:40:20
5,203,167.87 .32.4, 14:40:20
6,202, 10.10.10.10, 14:40:30
7,200,167.87 .32.4, 14:40:31
The report format you need to make in the background is: date, website ID, website URL, and traffic count (no traffic is displayed for sites, and the traffic is displayed as 0)
You may think that, based on the date, left join table site_config after sid group by in the table site_stat, this is obviously not the method I will share.
Use two SQL statements. One is to retrieve all the sites, and the other is to retrieve the site traffic statistics under the specified date.

SQL1 takes all sites:Copy codeThe Code is as follows: SELECT sid, site_url, site_name FROM site_config

SQL2 takes site statistics (for example, today ):Copy codeThe Code is AS follows: SELECT sid, COUNT (1) AS come_total FROM site_stat
WHERE add_time> = '2017-06-30 00:00:00'
AND add_time <= '2017-06-30 23:59:59'
Group by sid

As mentioned above, a lot of data is used to pave the way for the future. We will share a PHP method to return a two-dimensional array using the field passed in as the index:Copy codeThe Code is as follows: protected function getList ($ SQL, $ filed = null ){
$ Res = mysql_query ($ SQL, $ this-> link_ SC );
$ Data = array ();
If ($ filed === null ){
While ($ row = mysql_fetch_assoc ($ res )){
$ Data [] = $ row;
}
} Else {
While ($ row = mysql_fetch_assoc ($ res )){
$ Data [$ row [$ filed] = $ row;
}
}
Return $ data;
}

Upload your SQL statement and the fields to be indexed to the SQL statement. If the fields to be indexed are passed in, they must be included in the fields returned by the select statement.
When we retrieve all sites, we do not need to pass the field in the past. However, when collecting site traffic statistics, we pass the site sid as follows:Copy codeCode: $ sites = getList ($ sql1); // all sites
$ Data = getList ($ sql2, 'sid'); // The site traffic data. The two-dimensional array is returned using the sid as the index.

Finally, when presenting the report, use foreach to loop $ sites and retrieve the data whose index is sid from the array $ data when taking the traffic.
It is very convenient and easy to master. I often do this.

Finally, let's take a look at the statistical results report in the Table above:
Format: date, website ID, website URL, traffic count
2013-06-30,200, baidu.com, 3
2013-06-30,201, google.com, 0
2013-06-30,202, cnblogs.com, 3
2013-06-30,203, codejia.net, 1
Summary: It seems that writing so much is a small skill. However, never underestimate this skill, which can save you a lot of time. Therefore, when you write code, you can sum up more and come to the blog Park to communicate with and learn from each other. The programming level will certainly improve rapidly!