PHP Code (2) — Array_column function formats two-dimensional arrays into fixed one-dimensional arrays and optimizes query methods


Xiao Bai often uses multi-table queries, such as obtaining a list of card vouchers received by members, which includes, of course, a main table of “t_coupon_members” of the membership coupon record table, then 2 “t_coupon of the store coupon table” and 3 “t_shop of the store information table”. The linked table relationship is that the redundant field coupon_id in the main table corresponds to table 2, and the redundant field shop_id in Table 2 corresponds to the shop ID in table 3. So it can be obtained by direct 3-table joint search, but it is told that it needs to be optimized. Here is the result of optimization.

How to optimize

1. The optimization here is to get a two-dimensional array $list with store ID by directly checking the main table 1 and 2, then query the qualified store information $shop_info through the store ID in the database IN statement, then loop the $list, and match the $shop_info to the $list through the array_column of PHP array processing function. The following is the concrete implementation steps, code framework TP5.1.

II. Specific steps

1. Connect tables to obtain arrays in Tables 1 and 2

$list = $this->alias('M1')
                    ->('t_coupon M2','M1.coupon_id =','left')
                    ->page($page + 1,$psize)
$list ? $list = $list->toArray() : [];

2. The result is shown in Figure 2 below. Here we should pay attention to why we use -> toArray (), because in TP5.1 – > select () query returns an object. Someone may say that I use dump () to print an array. This is also an array function, array_column. The error message parameter must be an array. Discovery. The original dump () is TP5.1 to encapsulate it, you can use PHP’s own var_dump () print it to understand, as shown in Figure 1.

3. After we get the shop_id with the store, we process it with an array and get an IN statement using an array, similar to [4,5]. The code is as follows:

$shop_id = array_column ($list, "shop_id"); //formatted store ID

4. In this way, we can get all the store information in the current $list in a single SQL IN statement. The code is as follows:

$shops_model = new Shops();
$shop_info = $shops_model->where('id','IN',$shop_id)->field('id,shop_name')->select();
$shop_info? $shop_info = $shop_info - > to Array (): []; //store information

5. Next, the array of $shop_info is processed, so that the store ID becomes the key name and the store name becomes the key value. The function array_column() can be used with PHP. The specific usage can be explained by referring to

$shop_info = array_column($shop_info,"shop_name","id");

6. Finally, we directly cycle foreach $list to add the processed store information to the data source, and the results are as follows.

foreach($list as $k=>$v){
        $list[$k]['shop_name'] = $shop_info[$v['shop_id']];

Ps: Actually, at first, when I was in the loop $list, I inquired about store information directly from the database, but I was told that this was not good. I should do as little inquiry as possible in the loop, and then asked the ape to use array_column(), which really worked well.

Well, this is mainly to explain the use of array_column(), I feel that PHP really has a lot of rich functional methods to meet the mainstream business needs, but I don’t know, so I wrote a lot of redundant code_, the experience of the old ape is really commendable.


Recommended Today

Detailed steps for installing Perl and Komodo IDE for windows

Perl official website: document: address: The installation package of Perl Windows version is divided into activestate Perl and strawberry Perl. For the difference between the two, see: Note: the download speed of activestate Perl is slow. You may need KX to surf the Internet I have uploaded all the versions of […]