counting score

[crakotte]

Hello,

(sorry if my english is bad)

 

I would like to make a online game like "unanimo":

(rules : I give a image , players give eight words they associate with this image. They score points for each words another player also wrote..see here for more explications about real game :   http://cocktailgames.com/en/cocktailgames/produit/unanimo )

 

(i want that for my forum, for a christmas contest) but i'm a php amateur so is very difficult. Some can help me to finish score.php please ? i find occurence but i don't understand how i can count point for each player ....any idea ?

 

single answer = 0 point

2 identical answers = 2 points

3 identical answers = 3 points

etc...

 

This is i have already made:

 

index

<form action="insert.php" method="post">
Pseudo: <input type="text" name="pseudo" /><br><br>
Mot 1: <input type="text" name="mot1" /><br><br>
Mot 2: <input type="text" name="mot2" /><br><br>
Mot 3: <input type="text" name="mot3" /><br><br>
Mot 4: <input type="text" name="mot4" /><br><br>
Mot 5: <input type="text" name="mot5" /><br><br>
Mot 6: <input type="text" name="mot6" /><br><br>
Mot 7: <input type="text" name="mot7" /><br><br>
Mot 8: <input type="text" name="mot8" /><br><br>
<input type="submit" />
</form>

 

insert.php

<?php
$con = mysql_connect("xxx","xxx","xxx");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("unanimo", $con);
$sql="INSERT INTO unanimo (pseudo, mot1)
VALUES
 ('$_POST[pseudo]','$_POST[mot1]'),
 ('$_POST[pseudo]','$_POST[mot2]'),
 ('$_POST[pseudo]','$_POST[mot3]'),
 ('$_POST[pseudo]','$_POST[mot4]'),
 ('$_POST[pseudo]','$_POST[mot5]'),
 ('$_POST[pseudo]','$_POST[mot6]'),
 ('$_POST[pseudo]','$_POST[mot7]'),
('$_POST[pseudo]','$_POST[mot8]')";
if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  else
{
header('Location: http://xxxxxx'); 
  }
mysql_close($con)
?>

score.php

<?php$con = mysql_connect("xxx","xxx","xxx");
mysql_select_db("unanimo");
$req = mysql_query("SELECT mot1, COUNT(*) as nbre FROM unanimo GROUP BY mot1 HAVING ( COUNT(nbre) > 1 ) ORDER BY nbre DESC ")or die(mysql_error());
while ($donnees = mysql_fetch_array($req)) {
     echo "<TR><TD>".$donnees['mot1']." </TD><TD>".$donnees['nbre']."</TD></TR>";
}
?>

Edited November 6, 2015 by crakotte

[benanamen]

You are using deprecated MySQL code. You need to use PDO with prepared statements.

[Barand]

this query should calculate the scores for you

SELECT pseudo
  , SUM(nmbr) as score
FROM 
    unanimo
    INNER JOIN 
	(
	SELECT mot
          , COUNT(*) as nmbr
        FROM unanimo
        GROUP BY mot
        HAVING nmbr > 1
        ) tot USING (mot)
GROUP BY pseudo
ORDER BY score DESC;

Data and results

 

 

mysql> SELECT * FROM unanimo;
+----+--------+-------------+
| id | pseudo | mot         |
+----+--------+-------------+
|  1 | Peter  | caterpillar |
|  2 | Peter  | dolphin     |
|  3 | Peter  | bull        |
|  4 | Peter  | duck        |
|  5 | Peter  | animal      |
|  6 | Peter  | spider      |
|  7 | Peter  | beetle      |
|  8 | Peter  | bug         |
|  9 | Paul   | dog         |
| 10 | Paul   | cat         |
| 11 | Paul   | cow         |
| 12 | Paul   | bird        |
| 13 | Paul   | animal      |
| 14 | Paul   | spider      |
| 15 | Paul   | beetle      |
| 16 | Paul   | egg         |
| 17 | Mary   | dog         |
| 18 | Mary   | camel       |
| 19 | Mary   | cow         |
| 20 | Mary   | bird        |
| 21 | Mary   | animal      |
| 22 | Mary   | spider      |
| 23 | Mary   | beetle      |
| 24 | Mary   | bug         |
+----+--------+-------------+

+--------+-------+
| pseudo | score |
+--------+-------+
| Mary   |    17 |
| Paul   |    15 |
| Peter  |    11 |
+--------+-------+

 

 

Edited November 6, 2015 by Barand

[crakotte]

Wahouuu! thank you for the fast answer ! your query work great 

 

I would not abuse (anyway in the state is functional) but if you can tell me a last things, how to get out the pseudo list for each word?

 

I would like a result like this:

| wood| 4 | players list who give this word here
| church| 2 | players list who give this word here
| pen| 1 | players list who give this word here

 

 

I do like this many time   but is not good : i don't know 

$reqtest = mysql_query("SELECT mot1, COUNT(*) as nbre FROM unanimo GROUP BY mot1 HAVING ( COUNT(nbre) > 1 ) ORDER BY nbre DESC LIMIT 1 OFFSET 0")or die(mysql_error());
while ($test = mysql_fetch_array($reqtest)) {
$res=$test['mot1'];
}
$reqq = mysql_query("SELECT pseudo FROM unanimo WHERE mot1 ='$res' ")or die(mysql_error());
while ($donneesq = mysql_fetch_array($reqq)) {
     echo $donneesq['pseudo']." / ";
}

current code

echo "<TR><TD>".$donnees['mot1']." </TD><TD>".$donnees['nbre']."</TD></TR>";

i would like :

echo "<TR><TD>".$donnees['mot1']." </TD><TD>".$donnees['nbre']."</TD><TD>".$pseudo['list']."</TD></TR>";

Sorry if you don"t understand , I don't know how explain this in english, i want  to dispaly a third colum with pseudo list

 

 

Never mind if it's not clear, you gave me the principal key, that is  retail thank you

[Psycho]

Well, you could use GROUP_CONCAT() which will concatenate the values within a grouped record. But, it has some limitations. For example, I don't think it will remove duplicates. BUt, there are other, more technical issues. that I don't want to go into details with. If you need those values, then I would suggest just selecting all the records instead of getting calculated totals. Then when outputting the results, get the totals.

 

Using GROUP_CONCAT

 

SELECT pseudo, SUM(nmbr) as score,
       GROUP_CONCAT(DISTINCT player_name
                    ORDER BY player_name ASC SEPARATOR ', ') as player_list
FROM 
    unanimo
    INNER JOIN 
(
SELECT mot
          , COUNT(*) as nmbr
        FROM unanimo
        GROUP BY mot
        HAVING nmbr > 1
        ) tot USING (mot)
GROUP BY pseudo
ORDER BY score DESC;

 

Or, just query all the relevant records without the GROUP BY and handle the logic to calculate totals and determine the player list in the code.

[Barand]

try

SELECT mot
          , COUNT(*) as nmbr
          , GROUP_CONCAT(pseudo ORDER BY pseudo SEPARATOR ', ') as players
        FROM unanimo
        GROUP BY mot
        HAVING nmbr > 1
        ORDER BY nmbr DESC;
+--------+------+-------------------+
| mot    | nmbr | players           |
+--------+------+-------------------+
| beetle |    3 | Mary, Paul, Peter |
| spider |    3 | Mary, Paul, Peter |
| animal |    3 | Mary, Paul, Peter |
| bird   |    2 | Mary, Paul        |
| bug    |    2 | Mary, Peter       |
| cow    |    2 | Mary, Paul        |
| dog    |    2 | Mary, Paul        |
+--------+------+-------------------+

[crakotte]

Perfect, You are great, thank you for your welcome and your help