Foreach help

[Smackie]

Alright I'm trying to create a script that will put each users and 5 different stats and have them in side a table. but not sure on how to pull multi $values out of an array into a table.. :-\ i have read up on foreach but i can only figure out how to pull only 1 $value at a time..

here is where i am at..

$total_users = array($id, $kills, $wins, $draws, $bonus);
//(left some script out so you didn't have to see all the sql querys)
	$total_users[$user_name] = $user_name;
	$total_id += $id;
	$total_kills += $kills;
	$total_wins += $wins;
	$total_draws += $draws;
	$total_bonus += $bonus;
}
arsort($total_users);
$i = 1;
foreach ($total_users as $key => $val) {
	if ($val != 0) {
?>
  <tr>
  	<td class="txt"><center><?php echo $i; ?></center></td>
    <td class="txt"><center><?php echo ucwords($key); ?></center></td>
    <td class="txt"><center><?php each number_format($val); ?></center></td>
    <td class="txt"><center><?php echo number_format($val); ?></center></td>
    <td class="txt"><center><?php echo number_format($val); ?></center></td>
    <td class="txt"><center><?php echo number_format($val); ?></center></td>
    <td class="txt"><center><?php echo number_format($val); ?></center></td>
  </tr>
<?php
	}
}
?>

can someone help me please

Thank you
Smackie

[bretticus]

I can't see all of your code, but it appears you just need to change the way you go about creating your overall users array. I would have each users information as an array and make it an element in the $total_users array ($total_users becomes an array of arrays.) Here's an example to illustrate:

<?php
$total_users = array();
// use player id for array key (this is player 1) and add one user to parent array
$total_users[1] =array(
	'name'=>'John Doe',
	'kills'=>100,
	'wins'=>100,
	'draws'=>0,
	'bonus'=>5000
);

// add another user to parent array (and so on...)
$total_users[2] = array(
	'name'=>'Jane Doe',
	'kills'=>10,
	'wins'=>1000,
	'draws'=>5,
	'bonus'=>500
);

foreach ($total_users as $id => $stats) {
?> 
  <tr> 
    <td class="txt"><center>player id: <?php echo $id; ?></center></td> 
    <td class="txt"><center>player name: <?php echo ucwords($stats['name']); ?></center></td> 
    <td class="txt"><center><?php echo number_format($stats['kills']); ?></center></td> 
    <td class="txt"><center><?php echo number_format($stats['wins']); ?></center></td> 
    <td class="txt"><center><?php echo number_format($stats['draws']); ?></center></td> 
    <td class="txt"><center><?php echo number_format($stats['bonus']); ?></center></td> 
  </tr> 
<?php
}
?>

[Smackie]

See i understand that but I'm pulling data from database for each user.. which the query is accually pulling all the games the users have played.. (i guess i should have added the sql with it so you could understand).

from the query pulling all the games it would show how many kills and stuff used buy SUM() and COUNT().. and im clueless on how to get this done..

but here is what i left out on the other post

  $sql = "SELECT user_name FROM users";

$result = mysql_query($sql); 
while ($row = mysql_fetch_array($result)) {
$user_name = $row['user_name'];

$query1 = "SELECT user_name, COUNT(id), SUM(kills), SUM(wins), SUM(draws), SUM(bonus) FROM reports WHERE user_name='$user_name' GROUP BY user_name"; 

$result1 = mysql_query($query1) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result1)){

$id = COUNT($row['id']);
$kills = COUNT($row['kills']);
$wins = COUNT($row['wins']);
$draws = COUNT($row['draws']);
$bonus = COUNT($row['bonus']);
}

$query2 = "SELECT user_name, COUNT(id), SUM(kills), SUM(wins), SUM(draws), SUM(bonus) FROM reports2 WHERE user_name='$user_name' GROUP BY user_name"; 

$result2 = mysql_query($query2) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result2)){
$id = COUNT($row['id']);
$kills = COUNT($row['kills']);
$wins = COUNT($row['wins']);
$draws = COUNT($row['draws']);
$bonus = COUNT($row['bonus']);
}

$query3 = "SELECT user_name, COUNT(id), SUM(hkills), SUM(wins), SUM(draws), SUM(bonus) FROM reports3 WHERE user_name='$user_name' GROUP BY user_name"; 

$result3 = mysql_query($query3) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result3)){
$id = COUNT($row['id']);
$kills = COUNT($row['hkills']);
$wins = COUNT($row['wins']);
$draws = COUNT($row['draws']);
$bonus = COUNT($row['bonus']);
}

[laserlight]

You are executing queries in a loop, and that is usually the wrong approach. You probably want to do something like this:

$sql = "SELECT user_name, COUNT(id) AS id_count, SUM(kills) AS kills_total,
    SUM(wins) AS wins_total, SUM(draws) AS draws_total, SUM(bonus) AS bonus_total
    FROM reports GROUP BY user_name";
$result = mysql_query($sql) or die(mysql_error());

// Print out result
while ($row = mysql_fetch_assoc($result)) {
    $id = $row['id_count']);
    $kills = $row['kills_total'];
    $wins = $row['wins_total'];
    $draws = $row['draws_total'];
    $bonus = $row['bonus_total'];
}

The same goes for the other two tables, but why have three tables when you probably only need one?

[Smackie]

The reason why i have 3 tables is because we have 3 different games and different data that would be going in and out except for these data..

and the reason why i am executing queries in a loop is because if you look up above where the loop start is to pull the user_name from users and then pull records from each of the reports tables..

[bretticus]

<?php
// users array like I suggested before...

$users = array();

$query1 = "SELECT user_name, COUNT(*) AS 'count', SUM(kills) AS 'kills', ";
$query1 .= "SUM(wins) AS 'wins', SUM(draws) AS 'draws', SUM(bonus) AS 'bonus' ";
$query1 .= "FROM reports GROUP BY user_name";

$result1 = mysql_query($query1) or die(mysql_error());

// Print out result 
while($row = mysql_fetch_assoc($result1)){
	if ( ! isset($users[$row['username']]) ) {
		$users[$row['username']] = array(
			'count' => $row['count'],
			'kills' => $row['kills'],
			'wins' => $row['wins'],
			'draws' => $row['draws'],
			'bonus' => $row['bonus']
		);
	} else {
		$users[$row['username']]['count'] += $row['count'];
		$users[$row['username']]['kills'] += $row['kills'];
		$users[$row['username']]['wins'] += $row['wins'];
		$users[$row['username']]['draws'] += $row['draws'];
		$users[$row['username']]['bonus'] += $row['bonus'];
	}
} 

// repeat for other two queries ...


// print them out
foreach ($users as $name => $stats) { 
?> 
  <tr> 
    <td class="txt"><center><?php echo ucwords($name); ?></center></td> 
    <td class="txt"><center><?php echo number_format($stats['kills']); ?></center></td> 
    <td class="txt"><center><?php echo number_format($stats['wins']); ?></center></td> 
    <td class="txt"><center><?php echo number_format($stats['draws']); ?></center></td> 
    <td class="txt"><center><?php echo number_format($stats['bonus']); ?></center></td> 
  </tr> 
<?php 
} 
?>

However, since you have the same columns in each table, you could do this with one query using UNION joins, right?

Good luck!

[laserlight]

Smackie wrote:

and the reason why i am executing queries in a loop is because if you look up above where the loop start is to pull the user_name from users and then pull records from each of the reports tables..

If you look at my example (and at bretticus' new example below), you will see that that first query is unnecessary to begin with. If you really needed it, then you should perform a join, and thus still have just one query.

Smackie wrote:

The reason why i have 3 tables is because we have 3 different games and different data that would be going in and out except for these data..

That suggests that you have one table, but with another column for the game id. This would be a foreign key to the main games table.

You could use bretticus' suggestion of a union, but that would be just a hack around poor database design.