Associative array between two mysql tables and for each loop

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

Sammitch · Accepted Answer · 2014-09-29 19:00:21Z

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'];

answered Sep 29, 2014 at 19:00

Sammitch

32.5k7 gold badges58 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kelly Over a year ago

hmm i have not used left join before. What would u in u.amount and the p in p.total

KM. Over a year ago

+1, let the database do what it is supposed to, there is no need to have the application do this while looping. "u" is an alias to users and "p" is an alias to po: FROM users u LEFT JOIN po p

Kelly Over a year ago

sorry @sammithc you wrong db name po should be fpo here is what i have now doesnt seam to be working.'code'<? $con = new mysqli("localhost","******","***","*****"); ?> <? $result = mysqli_query($con,"SELECT u.amount, SUM(p.total) 'po_total' FROM users u LEFT JOIN fpo p ON u.empnum = p.empnum GROUP BY p.empnum"); while($row = mysqli_fetch_array($result)) { echo $percentage = $row['total'] / $row['amount']; echo "<br>"; } ?>

Collectives™ on Stack Overflow

Associative array between two mysql tables and for each loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related