具有多个调用的常规查询与使用PHP处理的复杂查询

Let me first present the final solution I want to implement.

让我先介绍一下我想要实施的最终解决方案。

I want to display the categories and any three or less than three sub-categories from a particular category.

我想显示特定类别中的类别和任何三个或少于三个子类别。

TABLE: listing_info

listing_id(PK) | Market | Project_Name 
1              | A      | A.a  
2              | A      | A.b   
3              | A      | A.c  
4              | A      | A.d   
5              | A      | A.e  
6              | A      | A.f   
7              | B      | B.a  
8              | B      | B.b   
9              | B      | B.c  
10             | B      | B.d   
11             | C      | C.a   
12             | C      | C.b   
13             | D      | D.a   
14             | D      | D.b   
15             | D      | D.c   
16             | D      | D.d   
17             | D      | D.e   
18             | E      | E.a   
19             | F      | F.a

Here Market is category and Project_Name are sub-categories.

这里的市场是类别,Project_Name是子类别。

I have used two ways and need to know which to choose and why? Also I am looking for any better solution or any optimization that can be made to the existing ones

我用了两种方法,需要知道选择哪个以及为什么?此外,我正在寻找任何更好的解决方案或任何可以对现有的优化

Method1:

Using simple query and making further call to db for sub categories. This can be optimized by using ajax pagination or onload pagination

使用简单查询并进一步调用db以获取子类别。这可以通过使用ajax分页或onload分页来优化

We can set the LIMIT AND OFFSET with per load event in multiple of some number x.

我们可以将每个加载事件的LIMIT AND OFFSET设置为某个数字x的倍数。

$data = sql::read("SELECT Market FROM listing_info GROUP BY Market LIMIT ? OFFSET ?");

<?php foreach($data as $d)
{ ?> 
<div class="Market">
  <h2> <?php echo $d->Market ?> </h2>
  <?php 
  $subcat = sql::read("SELECT Project_Name FROM listing_info WHERE Market =".$d->Market."LIMIT 3");
  foreach($subcat as $sc) ?>
    <h3 class="Project_Name"> <?php echo $sc->Project_Name ?> <h3>
  <?php } ?>   
</div>
<?php } ?>

Method2:

We can call the values all in once including the any three or less than three subcategories form each category.

我们可以一次性调用这些值,包括每个类别中的任意三个或少于三个子类别。

<?php 

    $data=sql::read("SELECT t1.Market, t1.Project_Name
    , COUNT(t2.listing_Id) AS cnt
    FROM listing_info AS t1
    LEFT JOIN listing_info AS t2
    ON (t1.Project_Name, t1.listing_Id) <= (t2.Project_Name, t2.listing_Id)
    AND t1.Market = t2.Market
    GROUP BY t1.listing_Id
    HAVING cnt <= 3
    ORDER BY t1.Market, cnt
    ");

    $mydata = objectToArray($data); // Converting object array to associative array

    //And then finding the count of each category 

    $counted = array_count_values(array_map(function($value){return $value['Market'];}, $mydata));

 //var_dump($counted) 
 /* array
  'A' => int 3
  'B' => int 3
  'C' => int 2
  'D' => int 3
  'E' => int 1
  'F' => int 1
  */
$index = 0;
foreach($counted as $k=>$v)
{
  ?>
  <div class="Market">
      <h2> <?php echo $k ?> </h2> 
       <?php for($m=0; $m < $v; $m++)
       { ?>
       <h3> <?php echo $mydata[$index]["Project_Name"]; ?> <h3>
       <?php $index++; } ?> 
  <div>
<?php } ?>

The first methods gives the benefit of the ajax but concern is multiple sql call and the second method gives the benefit of less sql call but I could not get it done using ajax.

第一种方法提供了ajax的好处,但是关注的是多个sql调用,第二种方法提供了较少sql调用的好处,但我无法使用ajax完成它。

Any help to optimize the code or any other better solutions will be appreciated

任何帮助优化代码或任何其他更好的解决方案将不胜感激

Thanks in advance

提前致谢

1 个解决方案

#1

The one possible solution I found is use the temporary table and include the Id for each Category, Then using BETWEEN Clause you can restrict the categories, on each subsequent ajax request change the values BETWEEN A? AND B?

我找到的一个可能的解决方案是使用临时表并包含每个类别的Id,然后使用BETWEEN子句可以限制类别,在每个后续的ajax请求中更改BETWEEN A的值?和B?

    $maketemp = "
           CREATE TEMPORARY TABLE temp_Market_Tbl2 (
            `Id` int NOT NULL AUTO_INCREMENT,
            temp_market VARCHAR(50) NOT NULL,
            PRIMARY KEY (`Id`)) AUTO_INCREMENT=0 ;";

        mysql_query($maketemp);
        $insert = "INSERT INTO temp_Market_Tbl2
             (temp_market)
             SELECT Market FROM listing_info GROUP BY Market;
           "; 
         mysql_query($insert);

 $data2=sql::read("SELECT b.* FROM temp_market_tbl2 b INNER JOIN (SELECT t1.Market, t1.Project_Name
        , COUNT(t2.listing_Id) AS cnt
        FROM listing_info AS t1
        LEFT JOIN listing_info AS t2
        ON (t1.Project_Name, t1.listing_Id) <= (t2.Project_Name, t2.listing_Id)
        AND t1.Market = t2.Market
        GROUP BY t1.listing_Id
        HAVING cnt <= 3
        ORDER BY t1.Market, cnt) a ON a.Market = b.temp_market WHERE b.Id BETWEEN 1 AND 4 ORDER BY b.Id ");

          var_dump($data2);

1 个解决方案

#1

更多相关文章

随机推荐