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

Question

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.

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

We can set the LIMIT AND OFFSET with per load event in multiple of some number 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.

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

Thanks in advance

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

    $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);