将选择计数数据放入PHP的Fetch Array Query行

I have a script which retrieves all users with the same "dealership_id" from the "users" table, this works fine. Each user within these records has an ID (users_sales_guild_id) which is also on another table called "sales_list".

我有一个脚本，从“users”表中检索具有相同“dealership_id”的所有用户，这很好。这些记录中的每个用户都有一个ID（users_sales_guild_id），该ID也位于另一个名为“sales_list”的表中。

What I am trying to do is list the total amount of sales which each user has from the "sales_list" table next to the respective user

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

Currently it prints the logged in user's amount (John Smith , value of 5), and not each individual amount, where am I going wrong?

目前它打印登录用户的金额（约翰史密斯，价值5），而不是每个单独的金额，我哪里出错了？

How I would like it to look

我多么希望它看起来

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

How it looks

看起来如何

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 Code

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.
?>

1 个解决方案

#1

You'd have to add a subquery or join to your sql, something similar to:

你必须添加一个子查询或加入你的sql，类似于：

Join:

加入：

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

Subquery:

子查询：

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

Then you can use $row['sales_count'] in the output

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

The $num_rows refers to the number of rows returned, it doesn't contain any specific counts of sales per user

$ num_rows指的是返回的行数，它不包含每个用户的任何特定销售计数

#1