php - 按计算列循环时对数据排序

Question

请有人帮我按计算的 $changepercentage 对这张表进行排序。我的表有 1000 多条记录，并且是按日期顺序上传的。我试过 arsort() ， SORT() 当它回显时，但 PHP 说它不是数组而是字符串。我也尝试过 mysqli_fetch_array() 但没有成功。需要您的帮助。

clo_prices(表)

date        |  name   |   cl_price |
2015-09-15  |  AAA    |    10      |
2015-09-15  |  BAA    |    20      |
2015-09-15  |  ABA    |    30      |
2015-09-16  |  AAA    |    86      |
2015-09-16  |  BAA    |    65      |
2015-09-16  |  ABA    |    33      |

价格表(表格)

date       | name     | volume    | last |
2015-09-15 | AAA      | 6655245   | 56   |
2015-09-15 | ABA      | 5666      | 65   |
2015-09-15 | AAB      | 1333      | 33   |
2015-09-16 | AAA      | 112365    | 56   |
2015-09-16 | ABA      | 23131     | 8    |
2015-09-16 | AAB      | 23330     | 12   |
2015-09-16 | ABB      | 2323122   | 60   |  
2015-09-16 | ACB      | 21222     | 99   |

期望的结果

date       | company    | closing  | volume    | Change % |
2015-09-16 | AAA        | 56       | 6655245   | 66       |
2015-09-16 | ABA        | 65       | 5666      | 9.7      |
2015-09-16 | ACB        | 69       | 1333      | 7        |
2015-09-16 | AAB        | 33       | 112365    | 3.2      |

---

$queryTMG = "SELECT price_list.date, price_list.name, price_list.last, price_list.volume,
                    clo_prices.date, clo_prices.name, clo_prices.cl_price                   
                FROM price_list 
                INNER JOIN clo_prices
                ON price_list.name = clo_prices.name 
                WHERE price_list.date= '$dateT' AND 
                      clo_prices.date = '$dateT' 
                ORDER BY cl_price DESC LIMIT 10 ";

$queryPMG = "SELECT price_list.date, price_list.name, price_list.last, price_list.volume, 
                    clo_prices.date, clo_prices.name, clo_prices.cl_price
                FROM price_list 
                INNER JOIN clo_prices
                ON price_list.name = clo_prices.name 
                WHERE price_list.date= '$dateT' AND 
                      clo_prices.date = '$dateP' 
                ORDER BY cl_price DESC LIMIT 10";


$resultTMG = mysqli_query($dbc, $queryTMG) or die (mysqli_error());
$resultPMG = mysqli_query($dbc, $queryPMG) or die (mysqli_error());

echo '<table  cellpadding = 15 border =1>';
echo '<tr> 
        <th>Company</th> 
        <th>Closing Rs.</th> 
        <th>Volume</th> 
        <th>Change %</th> 
        </tr>';

while($rowTMG = mysqli_fetch_assoc($resultTMG) AND $rowPMG = mysqli_fetch_assoc($resultPMG))
{
        echo '<tr> <td>'. $rowTMG['name']. '</td>';
        echo '<td>'. $rowTMG['last']. '</td>';
        echo '<td align = "right">'. $volumeTMG = number_format($rowTMG['volume']). '</td>';
        echo '<td align = "right">'.$changeprecentage = 
           number_format((( $rowTMG['cl_price'] - $rowPMG['cl_price'] ) /
                          $rowTMG['cl_price']), 2, '.', '' ). '</td> </tr>';

}

echo '</table>';    

Answer 1

我可能还不太明白你是如何计算你的百分比的，因为使用给定的数据我得到了不同的结果，参见 my fiddle here

SELECT p.date, p.name, p.last, p.volume,
       d.cl_price+0 prevprice,
       c.cl_price+0 currprice,                  
       100*(c.cl_price-d.cl_price)/c.cl_price perc_change
FROM price_list p
INNER JOIN clo_prices c ON p.name = c.name   -- current prices
                       AND p.date = c.date
INNER JOIN clo_prices d ON p.name = d.name   -- previous prices
                       AND '2015-09-15' = d.date
WHERE p.date= '2015-09-16'
ORDER BY perc_change desc

到目前为止，此解决方案只是一个 MySQL select 语句，因为一切都可以在单个查询中完成。您可能需要整理一些逻辑或输入数据。

为了示例的目的，我假设 $dateT 是 '2015-09-16' 并且 $dateP 是 '2015-09-15'.