Clash Royale CLAN TAG#URR8PPP

Stuck with the same values when looping through database rows

I am trying to make a function where the following should happen:

By using my code (see below), step 1 and 3 is working. At step 2, it is looping the correct amount of times but in every loop it retrieves the numbers belonging to the first ID from step 1.

Example:

Step 1 finds the following IDs: 1, 2, 3 and 4.

Step 2 loops 4 times, but retrieves the numbers belonging to ID 1 every time instead of retrieving the numbers to ID 1 in the first loop, ID 2 in the second etc.

My PHP:

$users_get = mysqli_query($conn,"SELECT id FROM users");
$users_num = mysqli_num_rows($users_get);
$users_list = array();
while($users_row = mysqli_fetch_array($users_get))
$users_list = $users_row;

foreach($users_list as $users_row)
$users_items = array(
'id' => $users_row['id']
);


for($loop1 = 0; $loop1 < $users_num; $loop1++)
$numbers_get = mysqli_query($conn,"SELECT number FROM users_numbers WHERE userid = '".$users_items[$loop1]['id']."'");
$numbers_num = mysqli_num_rows($numbers_get);
$numbers_list = array();
while($numbers_row = mysqli_fetch_array($numbers_get))
$numbers_list = $numbers_row;

foreach($numbers_list as $numbers_row)
$numbers_items = array(
'number' => $numbers_row['number']
);


$numbers_added = 0;

for($loop2 = 0; $loop2 < $numbers_num; $loop2++)
$numbers_added = $numbers_added + $numbers_items[$loop2]['number'];




I later added som echos to display the IDs and numbers that is retrieved and got the following result:

User ID: 1
Amount of numbers: 4
Numbers:
4 (Belonging to ID 1)
7 (Belonging to ID 1)
5 (Belonging to ID 1)
2 (Belonging to ID 1)
Total: 18

User ID: 2
Amount of numbers: 0

User ID: 3
Amount of numbers: 3
Numbers:
4 (Belonging to ID 1)
7 (Belonging to ID 1)
5 (Belonging to ID 1)
Total: 16


The amount of numbers for ID 3 is correct, however the 3 retrieved numbers belongs to ID 1.

Another observation I made was if I edit the SELECT query inside loop 1:

"SELECT number FROM users_numbers WHERE userid = '".$users_items[$loop1]['id']."'"


And manually selects an ID, example:

"SELECT number FROM users_numbers WHERE userid = '3'"


Then it retrieves the correct numbers belonging to ID 3.

After hours of trying to figure out what I'm doing wrong I still haven't found anything, so any help is really appreciated! Is there something I can do with my current code to fix it, or maybe there is some other ways to achieve the desired function?

SELECT id, number FROM users JOIN users_numbers ON users.id = users_numbers.userid

1 Answer
1

Based on the comments on my question, I ended up with the following code:

$users_get = mysqli_query($conn,"SELECT a.id, SUM(b.number) AS number FROM users AS a INNER JOIN users_numbers AS b ON a.id = b.userid GROUP BY a.id ASC");
$loop1 = 0;

while($users_items = mysqli_fetch_array($users_get)
echo "ID: ".$users_items[$loop1]['id']." - Num total: ".$users_items[$loop1]['number']."<br />";
$loop1++;



The echo inside the while is only for testing purposes. When I run this, it displays a nice list with all the user IDs and the sum of each users numbers.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.