[PHP]Sorting by a ratio

[wesman2232]

I'm probably doing this the wrong way, but I'm trying to sort my rankings by a Kill/Death ratio.
My current code:

elseif( $_POST['sort'] == "ratio" )
{
     $getratio = ratio(KillCount, DeathCount);
     $sqlplayer = odbc_exec($connect, "SELECT TOP $limit AID, CID, Level, XP, KillCount, DeathCount, ".$getratio." FROM Character WHERE CID not in (SELECT TOP $start CID FROM Character WHERE DeleteFlag='0' ORDER BY $getratio DESC, Level DESC,XP DESC, KillCount DESC, DeathCount DESC) AND DeleteFlag='0' ORDER BY $getratio DESC, Level DESC,XP DESC, KillCount DESC, DeathCount DESC");
}

Since I'm doing this sorting in a Pagination function, I was thinking of doing the calculation function first then sorting it in a DESC matter (the higher percentage of k/d would be at the top of the ranking) while also being inside the Pagination.

Also, any help of allowing alt codes to go through the website without sacrificing security would be helpful as well. htmlentities would block them all, and I can't exactly create an allowed list for it since it will only take one string and not an array. (Would have converted the allowed character back into their alt codes using html_entites_decode) Strip_tags works, but it's either the DB or the script that's still converting it. Characters like "ß" are working, but ones like other characters like ε, and Ω are converted into "&# 949;" and all.

Thanks.

 

[Dave]

How does the ratio function look like?
You can not order by something which is not a column or an expression. I don't think it would be smart to make such ranking though, I'm pretty sure the queries take a while to be executed.

Regarding the alt codes, you should use PDO because I'm not sure about the support of alt codes in odbc_* and mssql_*, but I use PDO and it works perfectly fine with that.

 

[Mambo]

Perhaps i'm overlooking some stuff,
but can't you do a query to retrieve the KillCount and Deathcount, store them in an array and then sort it using the sort function which is a standard function in PHP?

 

[Dave]

Perhaps i'm overlooking some stuff,
but can't you do a query to retrieve the KillCount and Deathcount, store them in an array and then sort it using the sort function which is a standard function in PHP?

He wants to query the database and order all characters by the K/D ratio right away. (Only select the TOP X characters.)
If you select all characters and fetch it in PHP, it will be a script which causes a big load on the server.

Edit: something you can do is just fetch the K/D ratio of all characters one time each day and store it in a column in the character table or an other table. That will work and is much easier to do.

 

[wesman2232]

How does the ratio function look like?
You can not order by something which is not a column or an expression. I don't think it would be smart to make such ranking though, I'm pretty sure the queries take a while to be executed.

Regarding the alt codes, you should use PDO because I'm not sure about the support of alt codes in odbc_* and mssql_*, but I use PDO and it works perfectly fine with that.

My ratio function:

    //Kill/Death Ratio
    function ratio($k,$d)
    {
        if (empty($k) || empty($d))
        {
            $num = $k + $d;
            if (empty($num))
            {
                $num = 0;
            }
            else
            {
                $num = 100;
            }
        }
        else
        {
            $num = $k / ( $k + $d ) * 100;
        }
        return '('.round($num,2).'%)';
    }

This is my ratio code, it's not the one that's copied and pasted everywhere. Also, thanks for the feedback for the other parts.

He wants to query the database and order all characters by the K/D ratio right away. (Only select the TOP X characters.)
If you select all characters and fetch it in PHP, it will be a script which causes a big load on the server.

Edit: something you can do is just fetch the K/D ratio of all characters one time each day and store it in a column in the character table or an other table. That will work and is much easier to do.

I may just have to create a stored procedure that would do that server-sided everyday or every 12 hours or so, but I wouldn't know where to begin on making one =P

 

[Mambo]

Creating a stored procedure sounds like a fairly efficient solution! It will take the work of the servers and make the script peanuts haha

 

[Anju]

Yeah, you can't order it by the ratio. PDO is the only option as Dave said.

 

[wesman2232]

Yeah, you can't order it by the ratio. PDO is the only option as Dave said.

Hmm, he was telling me to use PDO for the alt codes. I know I can order it if I store it into the Database, I just have to figure out how to create a stored procedure that would do that.

 

[Solaire]

SQL is perfectly capable of doing Maths as well as ordering by expressions.

ORDER BY SUM((KillCount / (KillCount + DeathCount) * 100))

 

[Mambo]

Hmm, he was telling me to use PDO for the alt codes. I know I can order it if I store it into the Database, I just have to figure out how to create a stored procedure that would do that.

You will get plenty of information googling "Stored procecure MsSQL". You can just put your query in, set some settings and your finished!

 

[wesman2232]

SQL is perfectly capable of doing Maths as well as ordering by expressions.

Awh ****, Solaire come to save the day. Thanks for that, I probably should have checked the order by function itself. I'll start experimenting with it and I'll see if I can get it working.

You will get plenty of information googling "Stored procecure MsSQL". You can just put your query in, set some settings and your finished!

Thanks Mambo!

 

[Solaire]

You're probably going to need something like this. (Untested, haven't touched SQL in a while)

DECLARE @kills int
DECLARE @deaths int
DECLARE @ratio int

DECLARE cur CURSOR LOCAL FOR
    SELECT KillCount, DeathCount FROM Character
OPEN cur

FETCH NEXT FROM cur INTO @kills, @deaths

WHILE @@FETCH_STATUS = 0 BEGIN
    IF @kills = 0
    BEGIN
        SET @ratio = 0
    END
    ELSE IF @deaths = 0
    BEGIN
        SET @ratio = 100
    END
    ELSE
    BEGIN
        SET @ratio = SUM((@kills / ((@kills + @deaths) * 100)))
    END

    FETCH NEXT FROM cur INTO @kills, @deaths
END

CLOSE cur
DEALLOCATE cur

 

[wesman2232]

The stored procedure we was finally able to come up with and get working:

DECLARE @kills int
DECLARE @deaths int
DECLARE @ratio float

DECLARE cur CURSOR LOCAL FOR
    SELECT KillCount, DeathCount FROM Character
OPEN cur

FETCH NEXT FROM cur INTO @kills, @deaths

WHILE @@FETCH_STATUS = 0 BEGIN
    IF @kills = 0
    BEGIN
        SET @ratio = 0
    END
    ELSE IF @deaths = 0
    BEGIN
        SET @ratio = 100
    END
    ELSE
    BEGIN
        SET @ratio = ROUND(((CAST(@kills AS float) / @deaths)), 2)
    END

    PRINT @ratio
    FETCH NEXT FROM cur INTO @kills, @deaths
END

CLOSE cur
DEALLOCATE cur

GO