LEFT JOIN无法正常工作

Question

I need some help with LEFT Join

first db is "fpo"

id----empnum-----amount---date----ponum

second db is "users"

id----amount-----empid----username----password

I need to total the "amount" in fpo for each user and compare the total to the amount from users.

This is the code i have now that is NOT working.

 $result = mysqli_query($con,
 "select fpo.amount , fpo.empnum , user.amount , user.fpo SUM(amount) 
  FROM LEFT JOIN fpo ON fpo.empnum = users.empnum GROUP BY empnum");

while($row = mysqli_fetch_array($result)) {
  echo $row['empnum'];
  echo "<br>";
  echo $row['amount'];
}

For some reason this is not working.... What am i doing wrong i have never worked with JOIn command.

Here is what the data looks like in mysql

**first db is "fpo"**
id----empnum-----amount-----date-------ponum
1-----854245-----5.00------9/7/14------12345
2-----123987-----8.00------9/7/14------12345
3-----123987-----5.00------9/7/14------12345
4-----854245-----15.00-----9/7/14------12345
5-----548798-----10.00-----9/7/14------12345
6-----854245-----30.00-----9/7/14------12345

**second db is "users"**
id----amount-----empid----username----password
1-----700.00-----854245---admin-------abc123
2-----500.00-----123987---admin-------abc123
3-----200.00-----548798---admin-------abc123

What i am trying to do is sum(amount) From fpo where empnum (note:from fpo) = empnum (note:from users) then echo the summed amount with the empnumber and the amount from the users table.

I need to do this for everyone in the users db. I have about 150 people in there right now.....

Thank you for your time and help!!! Probably a simple problem :(

Answer 1

You're not echoing the summed amount, $row['amount'] is the amount of a single row, you want $row['SUM(amount)']. I suggest you give it an alias to make it easier to access:

$result = mysqli_query($con,"select f.amount , u.empid , u.amount , 
                                u.fpo, SUM(u.amount) AS total
                             FROM users AS u
                             LEFT JOIN fpo AS f ON f.empnum = u.empid
                             GROUP BY u.empid") or die(mysqli_error($con));

while($row = mysqli_fetch_array($result)) {
      echo $row['empnum'];
      echo "<br>";
      echo $row['total'];
}

You were also missing the comma before SUM(amount), the table name after FROM, and you need to qualify amount with the table name because both tables have a column named amount. You also had user.empnum in your ON clause, but that column is users.empid.

You should select and group by the column from users, not fpo, because LEFT JOIN can return rows where the columns from fpo are NULL if there's no match for users.empid.