LEFT JOIN Not working properly

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 :(

You're missing a comma before SUM(amount). Try checking for errors from mysqli_query(). — Barmar
– Barmar, Commented Oct 3, 2014 at 3:30

Barmar · Accepted Answer · 2014-10-03 04:06:24Z

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.

still not working.....<pre>$result = mysqli_query($con,"select fpo.amount , fpo.empnum , user.amount , user.fpo, SUM(amount) AS total FROM users LEFT JOIN fpo ON fpo.empnum = users.empnum GROUP BY empnum"); while($row = mysqli_fetch_array($result)) { echo $row['empnum']; echo "<br>"; echo $row['total']; }</pre>
Have you looked at the error message from MySQL? There's no users.empnum, it's users.empid.
Thank you didnt know that. My bad that was my typing error it is empnum NOT empid the error code is Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home1/public_html/tck/admin/usage.php on line 202 ----- line 202 is the WHILE()
i have ini_set('display_errors',1); error_reporting(E_ALL); at the start of my document. Thats whats displaying the code.

mwigdahl · Accepted Answer · 2015-08-26 02:27:00Z

This seems to work for me. Thanks!

 result = mysqli_query($con,"select f.amount , u.empnum , u.amount , SUM(f.amount) AS total FROM users AS u LEFT JOIN fpo AS f ON f.empnum = u.empnum GROUP BY u.empnum") or die(mysqli_error($con)); while($row = mysqli_fetch_array($result)) { echo "Emp".$row['empnum']."----".$row['total']."----Amount----".$row['amount']."<br>";

Collectives™ on Stack Overflow

LEFT JOIN Not working properly

2 Answers 2

7 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Related