php - 将选择计数数据放入获取数组查询行 PHP

Question

我有一个脚本，可以从“users”表中检索具有相同“dealership_id”的所有用户，这工作正常。这些记录中的每个用户都有一个 ID (users_sales_guild_id)，该 ID 也位于另一个名为“sales_list”的表中。

我想要做的是列出每个用户从相应用户旁边的“sales_list”表中获得的销售总额

目前它打印登录用户的金额(John Smith，值为 5)，而不是每个单独的金额，我哪里出错了？

我希望它看起来如何

Name         |   Position          |    SID    |  Total Sales |  
John Smith   |   Sales Consultant  |    23434  |  5           |   Details
Jane Smith   |   Sales Consultant  |    34234  |  9           |   Details
John Chan    |   Sales Manager     |    43423  |  3           |   Details
Jane Chan    |   Sales Consultant  |    23344  |  7           |   Details

看起来怎么样

Name         |   Position          |    SID    |  Total Sales |  
John Smith   |   Sales Consultant  |    23434  |  5           |   Details
Jane Smith   |   Sales Consultant  |    34234  |  5           |   Details
John Chan    |   Sales Manager     |    43423  |  5           |   Details
Jane Chan    |   Sales Consultant  |    23344  |  5           |   Details

PHP 代码

$query = "SELECT `users_id`, `users_email` , `users_sales_guild_id` , `users_dealer_code_id` , 
      `users_first_name` , `users_surname` , `users_dealer_name` , `users_type` ,  DATE_FORMAT(`registration_date`, '%d-%m-%Y') 
      AS `dr` 
      FROM `users` 
      WHERE  `dealership_id` = '".$_SESSION['dealership_id']."'
      AND (users_type = 'Sales Manager' OR users_type = 'Sales Consultant')
      ORDER BY registration_date DESC";

$result = mysql_query("SELECT * FROM sales_list WHERE sales_list.users_sales_guild_id ='" . $_SESSION['users_sales_guild_id'] . "'");
$num_rows = mysql_num_rows($result);

$result = @mysql_query ($query); // Run the query.

echo '<table>
<tr>
<td align="center">Name</td>
<td align="center">Position</td>
<td align="center">ID</td>
<td align="center">Total Sales</td>
<td align="center"></td>
</tr>';

$bg = '#ffffff'; // Set the background color.
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

$bg = ($bg=='#e1e3e6' ? '#cdcdcf' : '#e1e3e6'); // Switch the background color.   
echo '<tr bgcolor="' . $bg . '">';

echo  '<td align="center">' . $row['users_first_name'] . ' ' . $row['users_surname'] . '    </td>';
echo  '<td align="center">' . $row['users_type'] . '</td>';
echo  '<td align="center">' . $row['users_sales_guild_id'] . '</td>';

echo  '<td align="center">' . $num_rows . '</td>';

echo  '<td align="center"><a href="sm-sales-ind-2.php?smid=' . $row['users_id'] . '">Details</td>'; 
}
echo '</table>';

mysql_free_result ($result); // Free up the resources.

mysql_close(); // Close the database connection.
?>

Answer 1

您必须将子查询或连接添加到您的sql中，类似于:

加入:

SELECT name, COUNT(sales_list.*)
FROM salesman
JOIN sales_list ON salesman.id = sales_list.salesman_id
GROUP salesman.id

子查询:

SELECT
  name,
  (SELECT COUNT(*) FROM sales_list 
   WHERE salesmen.id = sales_list.salesman.id) as sales_count
FROM salesmen

然后您可以在输出中使用$row['sales_count']

$num_rows 指返回的行数，它不包含每个用户的任何具体销售额