php - SUM 函数在 SQL 查询中生成错误
问题描述
我正在尝试通过 php 脚本获取有关 SQL 表的信息,但是,每当我添加 SUM 函数时,我都会收到以下错误“注意:尝试获取非对象的属性 'num_rows'”
我不知道发生了什么,但如果有人能解释/指出我正确的方向如何解决这个问题,将不胜感激
这是“损坏”的代码
<?php
require("connect.php");
$inNo = $_POST["inNo"];
$sql = "SELECT invoice.invoice_no, invoice.date, invoice.cust_id, invoice.emp_id, invoice_line.prod_id,
invoice_line.qty, product.cost_price, (product.cost_price * invoice_line.qty) AS `multi`,
customer.first_name AS `customer_fname`, customer.last_name AS `customer_lname`,
employee.first_name AS `emp_fname`, employee.last_name AS `emp_lname`,
product.name AS `proname`
FROM invoice INNER JOIN invoice_line ON invoice.invoice_no = invoice_line.invoice_no
INNER JOIN product ON invoice_line.prod_id = product.id
INNER JOIN customer ON invoice.cust_id = customer.id
INNER JOIN employee ON invoice.emp_id = employee.id, SUM(multi) AS `invoicetotal`
WHERE cust_id = '" . $inNo . "'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
//open table
echo '<table class="table table-striped" id="outTable">';
echo "<tr><th>Product</th><th>Qty</th><th>Price</th><th>Total cost</th></tr>";
// output data of each row
$ctr = 0;
while($row = $result->fetch_assoc()) {
if ($ctr == 0)
{
echo "<h1>Invoice Number: " . $row["invoice_no"]. "</h1>";
echo "<p>Customer: " . $row["customer_fname"]. " " . $row["customer_lname"]."</p>";
echo "<p>Employee: " . $row["emp_fname"]. " " . $row["emp_lname"]. "</p>";
echo "<p>Date: " . $row["date"]. "</p>";
}
echo "
<tr>
<td>" . $row["proname"]. "</td>
<td>" . $row["qty"]. "</td>
<td>" . $row["cost_price"]. "</td>
<td>" . $row["multi"]. "</td>
</tr>";
$ctr++;
}
} else {
echo "0 results";
}
$conn->close();
?>
解决方案
您需要在 FROM 上方的 SELECT 部分中插入 SUM():
SELECT
invoice.invoice_no,
invoice.date,
invoice.cust_id,
invoice.emp_id,
invoice_line.prod_id,
invoice_line.qty,
product.cost_price,
(product.cost_price * invoice_line.qty) AS `multi`,
customer.first_name AS `customer_fname`,
customer.last_name AS `customer_lname`,
employee.first_name AS `emp_fname`,
employee.last_name AS `emp_lname`,
product.name AS `proname`,
SUM(multi) AS `invoicetotal`
FROM invoice
INNER JOIN invoice_line
ON invoice.invoice_no = invoice_line.invoice_no
INNER JOIN product
ON invoice_line.prod_id = product.id
INNER JOIN customer
ON invoice.cust_id = customer.id
INNER JOIN employee
ON invoice.emp_id = employee.id` WHERE cust_id = 99
顺便说一句,格式化代码是明智的——SQL 代码也是如此——很好。
推荐阅读
- sql-server - SSIS 将平面文件路径作为命令行参数传递
- javascript - Sencha Cmd,C2001 闭包编译器错误:扩展本机类:数组
- python - Ctypes:获取指向结构字段的指针
- ios - sort an array by comparing a string value swift
- c# - C# 读取字符串中的类树路径并访问给定该类实例的值
- perl - 使用散列数组的散列作为计数器
- sonarqube - 无法启动 Sonarqube
- wordpress - CPT 分页 404 的 Wordpress 自定义税?
- wordpress - 即使在空闲时,Wordpress 插件也会在初始化时继续触发 register_post_type()
- javascript - 突出显示 DataTable 中的单元格内容