Show > 100 Kills Help

[Classico]

Hi,

 

I'm trying to show the players that have more than 100 player kills. But I seem to be having a small problem.

 

I store my players username and statistics in different tables, and I can't seem to get it working. This is what I have so far:

$user = mysql_query("SELECT * FROM players LIMIT 50");
while($username = mysql_fetch_assoc($user)){

$id = $username['id'];
echo "<tr bgcolor='#BFFFFF'><td>".$username['username']."</td>";

$user_s = mysql_query("SELECT * FROM player_stats WHERE playerid = '$id' AND players_killed > 100");
while($player_stats = mysql_fetch_assoc($user_s)){

echo "<td bgcolor='#00FF00' class=\"td_b\">".$player_stats['players_killed']."</td></tr>";
}
}

 

It lists the first 50 players, which isn't what I want. I'm wanting it to display only kills over 100 from the player_stats where the id from players is equal to the player_id in player_stats.

 

I looked into joining the queries, but I wasn't sure if would of worked.

 

Could someone help me please?

 

Thanks in advance

Edited November 8, 2012 by Classico

[DavidAM]

You should definitely use a single query with a JOIN and a single loop.

 

SELECT players.id, players.username, players_killed.players_killed
FROM players JOIN players_killed ON players.id = players_killed.playerid
WHERE players_killed.players_killed > 100
LIMIT 50

 

You may want to through ORDER BY players_killed.players_killed DESC in there between the WHERE clause and the LIMIT clause.

[Classico]

Thank you. Looks like I'll have to learn JOIN. I've put in the ORDER BY as you suggested, but now I have an error in my while loop.

 

$user = mysql_query("SELECT players.id, players.username, players_killed.players_killed
   FROM players JOIN players_killed ON players.id = players_killed.playerid
   WHERE players_killed.players_killed > 100
   ORDER BY players_killed.players_killed DESC
   LIMIT 50");

   while($stats = mysql_fetch_assoc($user)){

  echo "<tr bgcolor='#BFFFFF'><td>".$stats['username']."</td>";
   echo "<td bgcolor='#00FF00'>".$stats['players_killed']."</td></tr>";
   }

 

I get an error on line 90, which is

while($stats = mysql_fetch_assoc($user)){

 

mysql_fetch_assoc() expects parameter 1 to be resource

I've had the error before, and it was simple to fix. But I can't seem to find the problem this time...

[Barand]

1. Don't use SELECT * as you did in your original query, specify the fields you need.

 

2. Check the field names in the query - because you used * David had to guess what your actual names were so there is a possibility of error.

 

3. Check output from mysql_error() to find out what is wrong with the query.

Edited November 9, 2012 by Barand

[White_Lily]

if you going to use joins you need to have

while($stats = mysql_fetch_array($user)){
//other code
}

[Barand]

if you going to use joins you need to have

while($stats = mysql_fetch_array($user)){
//other code
}

 

Bovine scatology detection meter shot off the scale again

[jcbones]

Pretty sure (by your OP query) that you should be joining players table to player_stats, not players_killed, table.

[Psycho]

if you going to use joins you need to have

while($stats = mysql_fetch_array($user)){
//other code
}

Bovine scatology detection meter shot off the scale again

 

@White_Lily:

 

Stop posting this nonsense, which you've also posted previously and were advised it was not accurate.

[Andy-H]

$query = "SELECT p.username, ps.players_killed FROM players p
 INNER JOIN player_stats ps ON ( p.id = ps.playerid )
 WHERE ps.players_killed > 100 
 ORDER BY ps.players_killed DESC LIMIT 50";
$result = mysql_query($query);
while ( list($name, $kills) = mysql_fetch_assoc($result) ) {
 echo sprintf('
   <tr bgcolor="#bfffff">
     <td>%s</td>
     <td bgcolor="#00ff00" class="td_b">%s</td>
   </tr>', htmlentities($name, ENT_QUOTES, 'UTF-8'), number_format($kills, 0));
}

Edited November 10, 2012 by Andy-H

[Classico]

You should definitely use a single query with a JOIN and a single loop.

 

SELECT players.id, players.username, players_killed.players_killed
FROM players JOIN players_killed ON players.id = players_killed.playerid
WHERE players_killed.players_killed > 100
LIMIT 50

 

You may want to through ORDER BY players_killed.players_killed DESC in there between the WHERE clause and the LIMIT clause.

 

Sorry for not selecting the columns that I actually needed. But after I changed the players_killed.players_kill to player_stats.players_killed it worked.

 

Thanks for your help