SQL inserting twice

advertisements

Hello i have a sql statement that ranks my records and inserts the ranks into a database.

The problem i am having is that when i run the script. It deletes the old rankings. And inserts the new rankings fine, but it inserts every ranking twice. So there will be 2 rank 1's, 2 rank 2's, ect ect.

The kicker to my problem, is if i delete all the records in phpmyadmin. Then run the script. It doesnt make duplicates.

I tried just letting the script delete, to debug if the delete script is working, and it deleted all the rows fine. Hence im really confused :(

if you could look at my code and give me some insight that'd be awesome. Thank you

<?php

// Connect to the database we want to insert/update
$Server_Location = "localhost";
$Server_User_Name = "x";
$Server_Password = "y";
$Database_Name = "z";

// Create connection
$Conn_Info = mysqli_connect($Server_Location, $Server_User_Name, $Server_Password, $Database_Name);

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

if($_GET['Password'] != 'q'){
    die();
}

$Sql_Delete_Duplicate = "DELETE FROM ranks";

if ($Conn_Info->query($Sql_Delete_Duplicate) === TRUE) {
    echo "User Deleted";
}
else {
    echo "Error User Not Deleted";
    echo $Conn_Info->error;
}

// Overall Games - Rankings (1000)
$Top_1000_Sql = "Select games, overall_games_user.ID as UserID, Name FROM overall_games, overall_games_user WHERE overall_games.User_ID = overall_games_user.ID AND DATE(Date_Updated) = DATE(NOW()) GROUP BY User_ID ORDER BY Games DESC LIMIT 10000";

$Top_1000_Results = $Conn_Info->query($Top_1000_Sql);

$rank = 0;

if ($Top_1000_Results->num_rows > 0) {
    echo $Top_1000_Results->num_rows;
    while($Top_Player = $Top_1000_Results->fetch_assoc()) {

        $rank += 1;
        $User_ID = $Top_Player["UserID"];
        $Games_Played = $Top_Player["games"];

        $Insert_Top_Player_Sql = "INSERT INTO ranks (Rank_Type,Rank, User_ID, games)
                            VALUES ('Total', {$rank}, {$User_ID}, {$Games_Played})";

        if ($Conn_Info->query($Insert_Top_Player_Sql) === TRUE) {
            echo $rank . "-";
        }
        else {
            echo "Error User Not Added";
            echo $Conn_Info->error;
        }

    }
}

// Yesterday Games - Rankings

// Graph Stats

/*
SELECT AVG(Games) FROM overall_games WHERE DATE(Date_Updated) = DATE(NOW()) AND User_ID IN (SELECT ID FROM overall_games_user WHERE division LIKE('%BRONZE%'));
SELECT AVG(Games) FROM overall_games WHERE DATE(Date_Updated) = DATE(NOW()) AND User_ID IN (SELECT ID FROM overall_games_user WHERE division LIKE('%SILVER%'));
SELECT AVG(Games) FROM overall_games WHERE DATE(Date_Updated) = DATE(NOW()) AND User_ID IN (SELECT ID FROM overall_games_user WHERE division LIKE('%GOLD%'));
SELECT AVG(Games) FROM overall_games WHERE DATE(Date_Updated) = DATE(NOW()) AND User_ID IN (SELECT ID FROM overall_games_user WHERE division LIKE('%PLATINUM%'));
SELECT AVG(Games) FROM overall_games WHERE DATE(Date_Updated) = DATE(NOW()) AND User_ID IN (SELECT ID FROM overall_games_user WHERE division LIKE('%DIAMOND%'));

*/

?>


You are using Group By without aggregate function so change your query to:

$Top_1000_Sql = "Select Count(games) as gameCount, overall_games_user.ID as UserID, Name FROM overall_games, overall_games_user WHERE overall_games.User_ID = overall_games_user.ID AND DATE(Date_Updated) = DATE(NOW()) GROUP BY UserID ORDER BY Games DESC LIMIT 10000";

Also note change in GROUP BY UserID in above SQL

In $Games_Played variable fetch value of count:

$Games_Played = $Top_Player["gameCount"];