PHP returns an array of data from the database with the specified field as the index

In many cases, we've been using skilled code from touching a new project to development and then looking back over the code we wrote. Therefore, in the completion of each new project, appropriate to do some project summary, code summary, perhaps you will be useful in future projects, most likely to get unexpected harvest, such as: code optimization, think of a better, faster implementation methods and so on.

The great programmers are sometimes not the amount of code, but the simplicity of the program's code, the complexity of the logic, and the convenience of implementation, which will explain whether it is a good programmer. We do not do day and night overtime to midnight, the coding amount of programmers!
This article and you share a few of the use of PHP programming skills, some of the techniques are to read the code of others learned, and some of their own summary.

returns an array of data from a database, indexed by a specific field
It's easier to give an example:
If you want to count the flow of the designated site from other sites, and do a small background, to see the daily traffic generated by each site. We first built 2 data tables:
Table A, Site configuration table (only to count the traffic of these sites)

Copy Code code as follows:


CREATE TABLE ' site_config ' (


' id ' smallint (5) unsigned not NULL auto_increment COMMENT ' primary key, self-increasing ',


' Sid ' smallint (5) unsigned not NULL COMMENT ' site ID ',


' site_url ' varchar (128) Not NULL COMMENT ' website URL address ',


' site_name ' varchar 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 II, Site traffic statistics (to a user a record)

Copy Code code as follows:


CREATE TABLE ' Site_stat ' (


' id ' int (one) unsigned not NULL auto_increment COMMENT ' primary key, self-increasing ',


' Sid ' smallint (5) unsigned not NULL COMMENT ' site ID ',


' ip_address ' varchar () 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 and the Site_stat table write is greater than the read operation, so I set the table type to InnoDB. (These are the places you need to think about when you design your data, and you have a lot more speed.)
Back to the point, if the table site_config have such a few data:
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
Site traffic statistics Site_stat has produced some data, 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,2013-06-30 14:40:31
You need to do in the background of the report format: date, site ID, site URL, traffic number (no traffic site also to show, traffic shows 0)
You might think that by date, in table Site_stat, after Sid Group by, the left join table Site_config, which is obviously not the way I would share it.
With 2 SQL, one is to take all the sites, and the other is to take the site traffic statistics for the specified date.

SQL1 All sites:

Copy Code code as follows:


SELECT Sid,site_url,site_name from Site_config

SQL2 take the site statistics (if for today):

Copy Code code 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

The front said a lot of is for the back to do bedding, share a PHP to pass past fields as index, return two-dimensional array method:

Copy Code code 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 your SQL in and pass the fields that need to be indexed to OK, provided the fields that are entered must be in the field returned by the Select.
We are taking all the sites do not need to pass the past field, but in the site traffic statistics when we put, the site Sid passed, that is as follows:

Copy Code code as follows:


$sites = getlist ($sql 1); All Sites


$data = GetList ($sql 2, ' Sid '); Site traffic data, which returns a two-dimensional array of SIDS-indexed

Finally, in the presentation of the report, with a foreach loop $sites, the flow of time to the array $data to take the index SID data is OK.
Very convenient, and very easy to master, I often do so.

Finally, look at the results of the following table statistics Report:
Format: date, site ID, site 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: Seems to write so much on the description of a small skill ah, but, do not underestimate this skill, can save you a lot of time. So, everyone in writing code when a lot of summary, a lot to blog park and everyone exchanges, learning, programming level will certainly progress quickly!