PHP returns the data array taken from the database using the specified field as the index_PHP Tutorial

In many cases, from the time we are exposed to a new project to the completion of development, we then go back and carefully browse the codes we have written. Many of them are codes that we have used proficiently before. Therefore, when completing each new project, do some project summary and code summary appropriately. Maybe you will use it in future projects, and you are very likely to get unexpected gains, such as code optimization and better ideas. , faster implementation methods, etc.

Sometimes what makes a great program developer is not the amount of code, but the simplicity of the program code, the complexity of the logic but the convenience of implementation. These are the things that show whether a programmer is a good programmer. We don’t want to be programmers who work overtime day and night until late at night, scrambling to write a large amount of code!
This article will share with you some PHP programming skills. Some of them were learned when looking at other people’s codes, and some were summarized by myself.

Using a specific field as an index, return the data array fetched from the database
It is easier to give an example:
If you want to count the traffic of a specified site from other websites, And make a small backend to check the traffic brought by each website every day. We first build 2 data tables:
Table 1. Site configuration table (only counts the traffic of these websites)

Copy code The code is as follows:

CREATE TABLE `site_config` (
`id` smallint(5) unsigned NOT NULL auto_increment COMMENT 'primary key, auto-increment',
`sid` smallint(5) unsigned NOT NULL COMMENT ' Website ID',
`site_url` varchar(128) NOT NULL COMMENT 'Website URL address',
`site_name` varchar(80) NOT NULL COMMENT 'Website 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 record per user)

Copy code The code is as follows:

CREATE TABLE `site_stat` (
`id` int(11) unsigned NOT NULL auto_increment COMMENT 'primary key, auto-increment',
`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 table';

Because the site_config table read operations are greater than the write operations, the type is set to MyISAM; and the site_stat table write operations are greater than the read operations , therefore, I set the table type to InnoDB. (These are also things you need to consider when designing data, and the speed will be improved a lot).
Back to the topic, if there are several pieces of data in the table site_config:
The data format is: id, sid, site_url, add_time
1,200,baidu.com,2013-06-30 14:20:00
2,201,google.com,2013-06-30 14:20:00
3,202,cnblogs.com,2013-06-30 14:20:00
4,203,codejia.net,2013-06 -30 14:20:00
Some data has been generated in the site traffic statistics table site_stat, as follows:
The data format is: id, sid, ip_address, add_time
1,200,167.87.32.4,2013-06-30 14:40:00
2,200,192.168.11.56,2013-06-30 14:40:10
3,202,167.87.32.4,2013-06-30 14:40:10
4,202,192.168.11.56, 2013-06- 30 14:40:20
5,203,167.87.32.4,2013-06-30 14:40:20
6,202,10.10.10.10,2013-06-30 14:40:30
7,200,167.87.32.4,2 013 -06-30 14:40:31
The report format you need to do in the background is: date, website ID, website URL, traffic number (sites with no traffic will also be displayed, and the traffic will be displayed as 0)
You You may think of left joining the table site_config according to the sid group by in the table site_stat based on the date. This is obviously not the method I will share.
Use 2 SQLs to do it, one is to get all sites; the other is to get site traffic statistics on a specified date.

SQL1 gets all sites:

Copy code The code is as follows:

SELECT sid,site_url,site_name FROM site_config

SQL2 to get site statistics (if it is today):

Copy code The code is as follows:

SELECT sid,COUNT(1) AS come_total FROM site_stat
WHERE add_time>='2013-06-30 00:00:00'
AND add_time<='2013-06-30 23:59:59 '
GROUP BY sid

A lot of what I said earlier is to pave the way for what follows. Let me share a PHP method that uses the passed field as an index to return a two-dimensional array:

Copy code The 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;
}

Pass in your SQL and the fields that need to be indexed. The premise is that the fields passed in must be among the fields returned by select.
We do not need to pass the past field when fetching all sites, but when fetching site traffic statistics, we pass the site sid, which is as follows:

Copy code The code is as follows:

$sites = getList($sql1); //All sites
$data = getList($sql2,'sid'); //Site traffic Data, returns a two-dimensional array with sid as the index

Finally, when displaying the report, use foreach to loop $sites, and when fetching traffic, go to the array $data to get the data with the index sid. .
Very convenient and easy to master, I do it often.

Finally, take a look at the statistical results report in the above table:
The format is: date, website ID, website URL, traffic number
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 means that It’s a small trick, but don’t underestimate it, it can save you a lot of time. Therefore, everyone should summarize more when writing code, come to the blog park to communicate and learn with everyone, and your programming level will definitely improve quickly!

http://www.bkjia.com/PHPjc/327982.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/327982.htmlTechArticleIn many cases, we go from contacting a new project to the completion of development, and then go back and carefully browse what we have written Many of the codes are codes that we have used proficiently before. So, at the end...