CODEWITHSUNDEEP
phpmysql
al_alb
al_alb

Reputation: 145

Issue with foreach in php

i have this code which i use in order to extract first all users that exist in my database, then, i query each of them, to see how many points they have calculated. My issue is, the users that have points allocated the query extracts correct information, but if a user has not any points, then, all those users won't appear 0 points, but another user's points are given to them.. What is wrong with my code?

//here i get all user id's
$query = 'SELECT * FROM users';
    $result = mysql_query($query) or die(mysql_error());
    $id_user = array();
    $phone = array();
    $name = array();
    while($row = mysql_fetch_array($result)){
        $id_user[]=$row['user_id'];
        $phone[]=$row['phone'];
        $name[]=$row['first_name'];
    }
//here i get all points collected for each user
    foreach ($id_user as $user_id) {
    $queryuser ='SELECT SUM(basket_value) as total_sum FROM retailer WHERE user_id="'.$user_id.'"';
    $resultuser = mysql_query($queryuser) or die(mysql_error());
    echo "<table>";
    while($row = mysql_fetch_array($resultuser)){

            $total += $row['total_sum'];
            echo "<tr>";
            echo "<td>";
            echo $total;
            echo "</td>";
            echo "<td>";
            echo $user_id;
            echo "</td>";
            echo "</tr>";
        }
    }
    echo "<table>";

Upvotes: 1

Views: 75

Answers (3)

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

You must reset $total to 0 before your while loop.

You could also simplify your calculations, when you join both tables:

select u.user_id, u.phone, u.first_name, sum(r.basket_value) as total_sum
from users u
left join retailer r on r.user_id = u.user_id
group by u.user_id, u.phone, u.first_name;

Then you can loop through your result set just once and save all those additional queries.

Upvotes: 0

Jpsy
Jpsy

Reputation: 20852

You forgot to initialize $total in the foreach loop.

foreach ($id_user as $user_id) {
  $total = 0;
  ...

Upvotes: 0

GBD
GBD

Reputation: 15981

Your issue: all those users won't appear 0 points, but another user's points are given to them

Because you need to initialize $total=0; before while($row = mysql_fetch_array($resultuser)){

Upvotes: 1

Related Questions