PHP returns an array of data fetched by the database indexed by a specified field

  • 2020-07-21 07:04:37
  • OfStack

In many cases, we go from a new project to the completion of development, and then go back and look at the code we wrote, many of which we used to be familiar with. Therefore, at the completion of each new project, appropriate to do some project summary, code summary, perhaps you will be used in the future project, it is very likely to get unexpected results, such as: code optimization, came up with a better, faster implementation method, and so on.

A great programmer is sometimes not so much the amount of code, but the simplicity of the code, the complexity of the logic, and the ease of implementation that make a good programmer. We do not do day and night overtime late into the night, the code number of programmers!
This post shares a few PHP programming tips you can use, some learned while reading other people's code, some summarized by yourself.

Returns an array of data fetched from the database, indexed by a particular field
It's easier to give an example:
If you want to statistics to specify the site from other sites to the traffic, and do a small background, view each site daily traffic. Let's first build two data tables:
Table 1. Site configuration Table (only counts traffic to these sites)
 
CREATE TABLE `site_config` ( 
`id` smallint(5) unsigned NOT NULL auto_increment COMMENT ' A primary key , Since the increase ', 
`sid` smallint(5) unsigned NOT NULL COMMENT ' Web site ID', 
`site_url` varchar(128) NOT NULL COMMENT ' Web site URL address ', 
`site_name` varchar(80) NOT NULL COMMENT ' Web site name ', 
`add_time` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT ' Add the time ', 
PRIMARY KEY (`id`), 
UNIQUE KEY `adid` (`sid`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=' Site configuration table '; 

Table 2. Statistical Table of site traffic (1 record for each user)
 
CREATE TABLE `site_stat` ( 
`id` int(11) unsigned NOT NULL auto_increment COMMENT ' A primary key , Since the increase ', 
`sid` smallint(5) unsigned NOT NULL COMMENT ' Web site ID', 
`ip_address` varchar(32) NOT NULL COMMENT ' The user IP', 
`add_time` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT ' Add the time ', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=' Site traffic statistics ' ; 

Because site_config table reads are larger than writes, the type is set to MyISAM; The site_stat table write operation is larger than the read operation, so I set the table type to InnoDB. (These are also the things you need to think about when designing your data, which is a lot faster).
Back to the topic, suppose table site_config has some data like this:
The data formats are: 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
The site traffic statistics table site_stat has generated 1 data, as follows:
The data formats are: 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,2013-06-30 14:40:31
The format of the report you need to do in the background is: date, website ID, website URL, number of traffic (if there is no traffic, the site should be displayed and the traffic should be 0).
You might think of table sid group by followed by left join site_config in table site_stat by date, which is obviously not the method I'm going to share.
Use 2 SQL to do, 1 is to take all the sites; Another is to take the specified date under the site traffic statistics.

SQL1 take all sites:
 
SELECT sid,site_url,site_name FROM site_config 

SQL2 Station statistics (if it is today) :
 
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 

Share an PHP method that returns a 2-d array indexed by passed fields:
 
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 pass in the fields to be indexed on OK, provided that the fields passed in must be in the fields returned by select.
We do not need to pass the field when fetching all sites, but when fetching the site traffic statistics, we pass the site sid, which is as follows:
 
$sites = getList($sql1); // All site  
$data = getList($sql2,'sid'); // Site traffic data , In order to sid Return for index 2 Dimensional array  

Finally, when displaying the report, use foreach to loop $sites and fetch the data indexed by sid from the array $data.
It's very convenient, and it's very easy to master, and I do that a lot.

Finally, take a look at the statistical results of the above table:
Format: Date, Site ID, site URL, traffic
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
Conclusion: Writing so much may seem like a small skill, but don't underestimate this skill. It will save you a lot of time. Therefore, when we write code more summary, more to the blog garden and we communicate, learn, programming level 1 will surely progress very fast!

Related articles: