Leader of the Week php script

[regenx]

Hi all, i need some help here with this few lines please...

Code: Select all

<?php$res_list_lotw = mysql_query("SELECT * FROM characters WHERE pvpkills >= '1' ORDER BY pvpkills DESC LIMIT 1");$r_list_lotw = mysql_fetch_array($res_list_lotw);?> <div><div>This week leader:  <?php echo "{$r_list_lotw['char_name']}"; ?></div></div>

How i can select only for a week? I need most PvP points earned for player in one week...
ty in advance

[horato]

Dates of kills are not saved, you have to make new script which will save them.

[Stake]

A simple script for this:

SQL:

Code: Select all

ALTER TABLE `characters` ADD COLUMN `last_pvpkills` SMALLINT UNSIGNED DEFAULT NULL;

Java:

Code: Select all

package custom; import com.l2jserver.L2DatabaseFactory;import com.l2jserver.gameserver.ThreadPoolManager;import com.l2jserver.gameserver.model.quest.Quest;import java.sql.Connection;import java.sql.PreparedStatement;import java.util.Calendar;import java.util.Date;import java.util.GregorianCalendar; public class PVPKillsSave extends Quest {     private Calendar _date = Calendar.getInstance();    private Calendar _nextSaveDate = new GregorianCalendar();    private static final long PERIOD        = 86400000L;        // checks update each 1 day    private static final int SAVE_WEEKDAY   = Calendar.MONDAY;  // pvpkills are updated on every monday    private static final int SAVE_HOUR      = 0;                // at midnight     public PVPKillsSave(int questId, String name, String descr){        super(questId, name, descr);         _nextSaveDate.setTime(new Date());        _nextSaveDate.set(Calendar.DAY_OF_WEEK, SAVE_WEEKDAY);        _nextSaveDate.set(Calendar.HOUR_OF_DAY, SAVE_HOUR);        _nextSaveDate.set(Calendar.MINUTE, 0);        _nextSaveDate.set(Calendar.SECOND, 0);        _nextSaveDate.set(Calendar.MILLISECOND, 0);        _nextSaveDate.add(Calendar.DAY_OF_MONTH, 7);         checkSave();    }        private void checkSave(){        long nextCheckSave = _nextSaveDate.getTimeInMillis() - _date.getTimeInMillis();        if(nextCheckSave >= PERIOD){            nextCheckSave = PERIOD;        }         if(_date.getTimeInMillis() >= _nextSaveDate.getTimeInMillis()){            updatePVPKills();        }         ThreadPoolManager.getInstance().scheduleGeneral(new Runnable(){             @Override            public void run(){                checkSave();            }        }, nextCheckSave);    }     private void updatePVPKills(){        Connection con = null;        try{            con = L2DatabaseFactory.getInstance().getConnection();            PreparedStatement statement = con.prepareStatement("UPDATE `characters`"                    + " SET `last_pvpkills` = `pvpkills`");            statement.executeUpdate();        }catch(Exception e){            e.printStackTrace();        }    }     @SuppressWarnings("ResultOfObjectAllocationIgnored")    public static void main(String[] args){        new PVPKillsSave(-1, "PVPKillsSave", "custom");    }}

PHP:

Code: Select all

<?php$res_list_lotw = mysql_query("SELECT *"    ." FROM characters"    ." WHERE last_pvpkills > '0' AND pvpkills > '0'"); $week_pvpkills = array();$i = 0; while($r_list_lotw = mysql_fetch_array($res_list_lotw)){    $week_pvpkills[0][$i] = $r_list_lotw['char_name'];    $week_pvpkills[1][$i] = $r_list_lotw['pvpkills'] - $r_list_lotw['last_pvpkills'];    $i++;} @array_multisort($week_pvpkills[1], SORT_DESC, $week_pvpkills[0]); echo "The winner is ",$week_pvpkills[0][0]," with ",$week_pvpkills[1][0]," kills.";

OR

Code: Select all

<?php$res_list_lotw = mysql_query("SELECT *,"    ." (pvpkills - last_pvpkills) AS week_pvpkills"    ." FROM characters"    ." WHERE last_pvpkills > '0' AND pvpkills > '0'"    ." ORDER BY week_pvpkills DESC LIMIT 1"); $r_list_lotw = mysql_fetch_array($res_list_lotw); echo "The winner is ",$r_list_lotw['char_name']," with ",$r_list_lotw['week_pvpkills']," kills.";

I recommend the first php script, since that does not load MySQL that much with operations at each row.

Of course, it will dynamically query pvpkill counts in a week, but if you want to show the winner of last week, then you should directly save the subtracted value to last_pvpkills column under updatePVPkills, like:

UPDATE `characters` SET `last_pvpkills` = `pvpkills` - `last_pvpkills`

YET NOT TESTED!

[regenx]

You are great! Nice code...ty

[Stake]

You are great! Nice code...ty

Np, just let us know, if it works, so please provide feedback.

[FFs]

That is cool, i'll try to do that with the experience who each player gained on the week.