如何使用 PHP 从 MySQL 查询中按升序排列值? [英] How to order values in ascending order from MySQL query with PHP?
问题描述
我正在使用以下 PHP 脚本从 MySQL 表中获取和更改数据,并将结果打印在 HTML 表中.我希望通过 $utilization_percentage
变量按升序对数据进行排序,该变量由 $total_client_time/$total_available_time * 100
创建.这发生在 $sql
查询之后,所以我想知道这是否可以用 PHP 完成?或者有没有办法改变 MySQL 查询以在查询中计算 $utilization_percentage
然后我可以在查询中运行 ORDER BY
子句?实现这一目标的最佳方法是什么?
I am using the following PHP script to grab and alter data from a MySQL table and print the results in a HTML table. I was hoping to order the data in ascending order by the $utilization_percentage
variable, which is created by $total_client_time / $total_available_time * 100
. This occurs after the $sql
query, so I am wondering if this can be accomplished with PHP? Or is there a way to alter the MySQL query to have the $utilization_percentage
calculated within the query and then I can run a ORDER BY
clause in the query? What would be the best way to accomplish this?
<?php
require_once 'tm-config.php';
// create connection
$conn = new mysqli($tmcurrentConfig['host'], $tmcurrentConfig['user'], $tmcurrentConfig['pass'], $tmcurrentConfig['name']);
// check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT SUM(t.available_time) AS total_available_time,
SUM(t.chargeable_time) AS total_chargeable_time,
SUM(t.admin_time) AS total_admin_time,
SUM(t.new_business_time) AS total_new_business_time,
c.name AS companyName
FROM Timesheet t
LEFT JOIN fos_user u ON(u.id = t.user_id)
LEFT JOIN company c ON(c.id = u.company_id)
GROUP BY u.company_id";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
$total_available_time = $row["total_available_time"];
$total_chargeable_time = $row["total_chargeable_time"];
$total_admin_time = $row["total_admin_time"];
$total_new_business_time = $row["total_new_business_time"];
$total_client_time = $total_chargeable_time + $total_admin_time + $total_new_business_time;
$utilization = $total_client_time / $total_available_time;
$utilization_percentage = $utilization * 100;
$company_name = $row["companyName"];
// echo data into HTML table
echo
"<tbody>" . "<tr>" . "<td>" . $company_name . "</td>" . "<td>" . round($utilization_percentage) . " %" . "</td>" . "</tr>" . "</tbody>";
}
} else {
echo "No results";
}
$conn->close();
推荐答案
可以在查询中进行计算,然后在ORDER BY
中使用.
You can calculate it in the query, and then use it in ORDER BY
.
$sql = "SELECT SUM(t.available_time) AS total_available_time,
SUM(t.chargeable_time) AS total_chargeable_time,
SUM(t.admin_time) AS total_admin_time,
SUM(t.new_business_time) AS total_new_business_time,
c.name AS companyName,
(SUM(t.chargeable_time) + SUM(t.admin_time) + SUM(t.new_business_time)) / SUM(t.available_time) * 100 AS utilization_percentage
FROM Timesheet t
LEFT JOIN fos_user u ON(u.id = t.user_id)
LEFT JOIN company c ON(c.id = u.company_id)
GROUP BY u.company_id
ORDER BY utilization_percentage";
这篇关于如何使用 PHP 从 MySQL 查询中按升序排列值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!