提前抱歉,帖子很长...
我有这个表单,用户输入一个开始日期,一个结束日期,结果将被发送到一个PHP文件,该文件正在制作结果的变量,如下所示:
.PHP
if(isset($_POST['user_selected_dates'])) {
$invoice_startdate = $_POST['start_date'];
$invoice_enddate = $_POST['end_date'];
}
然后是这个讨厌的MySQL查询...
SELECT
s.student_socialnr, s.student_lastname, s.student_firstname,
cs.city_name,
c.customer_name,
scp.cpl_startdate, scp.cpl_enddate, scp.cpl_coursename, scp.cpl_pricevalue,
SUM(scpe.scpe_days) AS total_days, scp.cpl_pricevalue * SUM(scpe.scpe_days) AS total_invoice
FROM studentcourseplan scp
INNER JOIN students s ON s.student_id = scp.student_id
INNER JOIN studentcourseplanelements scpe ON scpe.scpe_cpl_id = scp.cpl_id
INNER JOIN cityselections cs ON cs.city_id = s.student_city_id
INNER JOIN customers c ON c.customer_id = s.student_customer_id
WHERE scp.cpl_startdate BETWEEN '$invoice_startdate' AND '$invoice_enddate'
GROUP BY scp.cpl_id ORDER BY s.student_lastname ASC
这将在表中输出如下内容(使用 PHP mysql_fetch_array
):
+------------------+------------------+-------------------+---------------+---------------+---------------+----------------+----------------+------------+---------------+
| student_socialnr | student_lastname | student_firstname | customer_name | cpl_startdate | cpl_enddate | cpl_coursename | cpl_pricevalue | total_days | total_invoice |
+------------------+------------------+-------------------+---------------+---------------+---------------+----------------+----------------+------------+---------------+
| 000000-0000 | Doe | John | A name | 2012-06-01 | 2012-06-30 | A coursename | 620 | 40 | 24800 |
+------------------+------------------+-------------------+---------------+---------------+---------------+----------------+----------------+------------+---------------+
| 000000-0000 | Clerksson | Lisa | A name | 2012-01-05 | 2012-07-30 | A coursename | 950 | 10 | 9500 |
+------------------+------------------+-------------------+---------------+---------------+---------------+----------------+----------------+------------+---------------+
| 000000-0000 | Derpson | Derp | A name | 2012-04-01 | 2012-04-05 | A coursename | 620 | 40 | 24800 |
+------------------+------------------+-------------------+---------------+---------------+---------------+----------------+----------------+------------+---------------+
现在我正在寻找一个可以执行以下操作的函数:
为 $invoice_startdate
和 $invoice_enddate
之间的天数创建一个值。
如果这个值更小,那么第 total_days
列中的结果值会进行计算:
列 price_value
* [新值的结果] 并将其输出到每一行(但前提是它小于数据库中的值......
也许可以在MySQL
-query 中完成?
我真的很感激一些帮助。这是我第一个使用PHP和MySQL的网站,所以请耐心等待!
谢谢。
我相信你正在考虑使用MySQL IF()和TO_DAYS()函数的组合。 所以像这样:
SELECT IF( TO_DAYS(DATE_DIFF('$invoice_enddate', '$invoice_startdate')) < total_days, price_value * ... ) as total_invoice
我通过在查询中添加以下代码来自己解决这个问题:
CASE WHEN SUM(scpe.scpe_days) > DATEDIFF('$invoice_enddate', '$invoice_startdate') THEN
(scp.cpl_pricevalue * DATEDIFF('$invoice_enddate', '$invoice_startdate')) ELSE SUM(scpe.scpe_days) * scp.cpl_pricevalue
END AS to_invoice