UPDATE multiple rows using GROUP BY?!

PHP
1

I’m trying to update multiple rows by using GROUP BY but something is wrong?

In my db table I always have 2 rows with the same game_id like this:

id fk_game_id invite accepted

1 34 1 0
2 34 0 0
3 57 0 0
4 57 0 0
5 89 0 1
6 89 0 1
7 92 0 0
8 92 0 0
9 96 0 0
10 96 1 0

Now, what I want is to update all fk_game_id where none of them has 1 in invite, in this case the games with fk_game_id 57 and 92.

I have tried this but with no luck:

$sql = mysql_query("SELECT * FROM ".$prefix."_test_gameplayer WHERE invite=0 AND accepted = 0 GROUP BY fk_game_id");
while($row = mysql_fetch_assoc($sql)){
	
	$thisgame = $row['fk_game_id'];
	echo $row['fk_game_id'].' - Invite: '.$row['invite'].'<br>';
	mysql_query("UPDATE ".$prefix."_test_gameplayer SET accepted=1 WHERE fk_game_id=$thisgame");
	
	
}

Please help and thanks in advance :slight_smile:

2

your GROUP BY approach is fine, if its purpose is to identify the fk_game_id which needs updating

and your UPDATE statement is okay, if its purpose is to update ~both~ rows to accepted=1 for that fk_game_id

however, the SELECT with GROUP BY is not returning an accurate result set

for example, look at rows 9 and 10 –

 9 96 0 0
10 96 1 0

row 9 will, by itself, be accepted by the existing WHERE clause (while row 10 will not)

thus when you do the GROUP BY, row 9 will generate 96 as one of the fk_game_ids that come out of the SELECT

here’s what i think you should try –

SELECT fk_game_id
  FROM ...
 WHERE accepted = 0
GROUP
    BY fk_game_id
HAVING MAX(invite) = 0

please let me know how that works out

3

It worked… Thanks. You rock :wink: