比较相等性并计算返回的sql查询中出现的相同字符串的次数

I am coding a similar game to cards against humanity and I am a bit stuck on collating the results using PHP, I'm new to PHP so have hit a stumbling block.

我正在编写一个类似的游戏来对抗人类卡片，我有点坚持使用PHP整理结果，我是PHP的新手，因此遇到了绊脚石。

So I have all my game data in an SQL table on my localhost and it looks like this roughly: These are the column headers and input as it is in the table:

所以我将我的所有游戏数据放在我的localhost上的SQL表中，它看起来大致如下：这些是列标题和输入，因为它在表中：

+------------+-----------------+------------+-----------------+
| FirebaseID |     Votes       | GameNumber |    Submission   |
+------------+-----------------+------------+-----------------+
|     id1    | Option number 6 |   Game 9   | Option number 6 |
|     id2    | Option number 6 |   Game 9   | Option number 1 |
|     id3    | Option number 6 |   Game 9   | Option number 6 |
|     id4    | Option number 1 |   Game 9   | Option number 1 |
|     id5    | Option number 6 |   Game 9   | Option number 4 |
+------------+-----------------+------------+-----------------+

Where Votes is the answer they vote to be the best and Submission is their own submission to the card game. I know that the values don't mean much at the moment but once I get the thing working I'll enter different values for each card.

如果投票是答案他们投票是最好的，提交是他们自己提交给纸牌游戏。我知道这些价值目前并不多，但是一旦我开始工作，我会为每张卡输入不同的值。

My code is this:

我的代码是这样的：

$conn = new mysqli($servername, $username, $password, $db);

// Check connection
if ($conn->connect_error) {
    die("Database connection failed: " . $conn->connect_error);
}

$q = $_GET['q'];

$sql="SELECT Votes, FirebaseID FROM cauusers WHERE GameNumber = '".$q."'";

$result = mysqli_query($conn,$sql);

$length = $result->num_rows;

if ($result->num_rows > 0) {
// output data of each row
    $myResArray = [];
    $count = 0;

    while($row = $result->fetch_assoc()) {
        $myResArray[$count] = $row["Votes"];
        $count++;
    }

    //var_dump($myResArray);
    $max = 0;
    $winner = "";
    $index = 1;
    //$length = count($myResArray) (This prints 5);

    for($i = 0; $i < $length;$i++){
        for($j = 0; $j < $length; $j++){
            if($myResArray[$i]===$myResArray[$j]){
                $max += 1;
                $winner = $myResArray[$i];

            }
        }
    }
    echo $winner  . "<br>";

    $sqlSel="SELECT FirebaseID FROM cauusers 
             WHERE GameNumber = '".$q."' AND Submission = '".$winner."'";

    $Submission = mysqli_query($conn,$sql);
    echo var_dump($Submission);

    if ($Submission->num_rows > 0) {
        while($row = $Submission->fetch_assoc()) {
            echo $row["FirebaseID"] . "<br>";
        }
    }
 } else {
     echo "0 results";
 }
 mysqli_close($conn);
 ?>

When I print $q I get:

当我打印$ q时，我得到：

Game 9

第9场比赛

When I var_dump $myResArray I get;

当我得到var_dump $ myResArray时;

 array(5) { 
    [0] => string(15) "Option number 6"
    [1] => string(15) "Option number 6" 
    [2] => string(15) "Option number 6" 
    [3] => string(15) "Option number 1" 
    [4] => string(15) "Option number 6"
 }

So I am trying to gather which card was voted for most and then based on that find out what the person (or people) who submitted that cards ID's are so I can then award the winners with coins for later use in the game. I can see that I am retrieving the data I need but can't seem to access each individual string to compare against each other. At the moment if there are two answers voted for the same amount of times then only one answer is returned.

因此，我正在努力收集哪张卡被投票最多，然后在此基础上找出提交该卡ID的人（或人）是什么，以便我可以奖励获胜者用硬币以供以后在游戏中使用。我可以看到我正在检索我需要的数据，但似乎无法访问每个单独的字符串以相互比较。目前，如果有两个答案投票的次数相同，那么只返回一个答案。

It prints the correct winning answer but it prints all 5 Firebase ID's instead of the

它打印正确的获胜答案，但它打印所有5个Firebase ID而不是

Any help would be massively appreciated.

任何帮助都将受到大力赞赏。

Cheers

干杯

1 个解决方案

#1

Instead of coding this logic in PHP, you could do most in SQL. The following query will determine what is most voted for, and return the records that match that vote:

您可以在SQL中执行大部分操作，而不是在PHP中编写此逻辑。以下查询将确定最多投票的内容，并返回与该投票匹配的记录：

SELECT *
FROM   cauusers
WHERE  Votes = (
    SELECT   Votes
    FROM     cauusers 
    WHERE    GameNumber = 'Game 9'
    GROUP BY Votes
    ORDER BY COUNT(*) DESC
    LIMIT    1);

See SQL Fiddle.

请参阅SQL小提琴。

Integrate that in your PHP, and you'll get something like this (not tested):

将它集成到PHP中，你会得到这样的东西（未经过测试）：

$sql = "SELECT *
        FROM   cauusers
        WHERE  Votes = (
            SELECT   Votes
            FROM     cauusers 
            WHERE    GameNumber = GameNumber = '$q'
            GROUP BY Votes
            ORDER BY COUNT(*) DESC
            LIMIT    1)";
$result = mysqli_query($conn, $sql);
if ($result->num_rows > 0) {
    // output winners
    $winner = "";
    while($row = $result->fetch_assoc()) {
        if ($winner=="") {
            $winner = $row['Votes'];
            echo "winner: $winner<br>";
        }
        echo $row["FirebaseID"] . "<br>";
    }
} else {
    echo "0 results";
}

One thing you should still improve: it is better not to inject $q in the SQL string, certainly not when it is a value that is provided by the user. Then you are vulnerable to SQL injection. Better is to use prepared statements and parameters.

有一点你还应该改进：最好不要在SQL字符串中注入$ q，当然它不是在用户提供的值时注入。那么你很容易受到SQL注入攻击。更好的是使用预准备的语句和参数。

#1