current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

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

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

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 

$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.
?>
share|improve this question

1 Answer 1

up vote 1 down vote

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

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

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

share|improve this answer
    
Why the downvote? –  helion3 Feb 5 at 23:05
    
While this might work, you would have much better results from joining and grouping. –  Jonathan Kuhn Feb 5 at 23:06
    
Updated with a join/group by version –  helion3 Feb 5 at 23:09
    
When I want to count things I generally go with a join/group. But it just popped into my head that with the join/group, there is one small problem. An inner join will exclude rows from salesman where there are no sales rows and if you left join you will return a count of 1 when there are no sales (you will get a sales rows with null values). I generally don't have a problem with doing an inner join and leaving off those results but if OP wants to show them, it might just be better to sub-query. –  Jonathan Kuhn Feb 5 at 23:14
    
I'll try the subquery and join, will let you know how it goes –  AdamMc Feb 5 at 23:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.