多调用的普通查询与PHP处理的复杂查询

Question

让我首先介绍我想实施的最后解决方案。

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

表：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   

这里市场是类别，Project_Name是子类别.

我用了两种方法，需要知道选择哪一种和为什么？此外，我正在寻找任何更好的解决方案或任何可以对现有方案进行的优化。

Method1:

使用简单的查询并为子类别进一步调用db。可以使用ajax分页或onload分页对此进行优化。

我们可以将LIMIT和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:

我们可以一次调用所有的值，包括构成每个类别的任意三个或少于三个子类别。

<?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 } ?>

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

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

提前感谢

Answer 1

我找到的一个可能的解决方案是使用临时表并包含每个类别的Id，然后在子句之间使用可以限制类别，在每个后续ajax请求上更改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);