两个mysql表和每个循环之间的关联数组

Question

Hey Guys i need some help with Associative array

Here is what i have.

I have two mysql table one called users...Setup like this.

[ MYSQL DB users]
***************************************
Id------name-------empnum------amount
1 ------testa------123456------40.00
2 ------testb------652526------300.00
3 ------Testc------919825------100.00
4 ------Testd------354694------50.00

And the other db is setup like this.

[ MYSQL DB po ]
***************************************
Id------ponum------reason----empnum---------total------reason
1 ------1234------testa------123456---------40.00------Demo
2 ------1235------testb------652526--------300.00------Demo
3 ------1236------Testc------919825--------100.00------other
4 ------1237------Testd------123456---------50.00------Demo

What i need to do is select each user from "Users" db and then find them in the "Po" db

Then i need to add up the total they have spent (in PO) and compare it with Amount from users db.

I have this javascript bar graph that i would like to display next to each persons name to show them how close to 100% they have spent.

<div id="progressBar<? echo $id; ?>" class="default"><div></div></div>
    <script>
        progressBar(<?php echo round(($sum/$total) * 100); ?>, $('#progressBar<? echo $id; ?>'));
    </script>  

My question is How do i go about this. My first thought was to use Associative array and get amount and empnum from users and then pull that info from mysql and then compare that with total....Some where between point a and b i get lost. I dont have a great understanding on how A-array work with mysql and two tables. from what i have found it looks like i will have to use a for each loop as well.?? Am i going about this the wrong way.. Is there an easier way???

Answer 1

You can do most of it in a single query:

SELECT u.amount, SUM(p.total) 'po_total'
FROM users u LEFT JOIN po p
  ON u.empnum = p.empnum
GROUP BY p.empnum

And then:

$percentage = $row['po_total'] / $row['amount'];