Em Andamento

php scripts to manage database

We have a database with some heavey useage tables that require constant management. We would like a set of scripts that automate some of this maintenance. The database (mysql) generating script is included.

The final output would be a simple php page which has 7 possible tasks on it (see long description for content) which allow a system adminstrator to delete or archive the content. Archiving can be optional to file or tow another table. some minor re-organisation might have to happen (as described in the long description.

A decent mysql php coder can do this in a day. this is mainly around clean out old records and archiving from a date onwards.

## Deliverables

Details description of work required:

==========================

Background:

----------------

The script is needed for a chess server which can be installed like phpbb or otherphp scripts. So the code can not be site specific (no hard coded locations, only relative elements.) The code is designed to reduce table size and create an environment that lets people know where they need to maintain things.

Targeted functionality:

----------------------------

The administrative data maintenance screen should allow for 7 simple links which lead to some choices. The page also displays some key figures. The links are as follows:

**Game table maintenance:**

Archive completed games older than [input] days? to db table

Delete? games with [status drop down] older than [input] days

Delete games where player info is missing

**Player table maintenence:

**Archive Players with 0 Games to db table

Unarchive Player [input player Name]

Delete Players with 0 Games

**Message maintenance:**

Delete all Messages older than [input] days including saved messaged [y/n]

Delete all automatic messages older than [input] days

Clean-up? who is online? history for? last [input] days

**Key figures:

***PLAYERS

*Number of active player: x

Number of archived players: x

*GAMES

*Number of challanges: x

Number of active games: x

Number of completed games: x

Number of archived games: x

Number of active Moves: x

*MESSAGES:*

Number of? active messages: x

Number of electronic messages: x

who is online history count: x

A warning box should be displayed before executing any of the clean-ups to tell people that they can not undo this, and that any data-loss is permanent. Please back-up the database before proceeding.

File Content explained:

------------------------------

The attached file contains this text, the database file and a folder containing the target folder structure including where the db config is held, where the function block will sit and a description of file of the main database elements. new database structure is required for 2 items:

- game archive table doesn't exist and needs to be created. Also add on- Player archive table. everything in archive needs to be in their respective ? table.

Database design:

-----------------------

The database has 97 tables, but only a some of these are relevant. The simple aspect is messages: 2 tables deal with messages:

-? c4m_msginbox (player to player messages)

-? message_queue

-? c4m_msgsaved (players saved the message

Player database content is richer and requires a new table (playerarchive). The following tables contain player specific information.

- player (main player table) - archive all

-? c4m_avatars (archive a_imgname)

-? c4m_buddylist (delete)

-? c4m_chessboardcolors (archive cc_dcolor? and cc_lcolor)

-? c4m_invalid_players (delete)

-? c4m_msginbox (delete)

-? c4m_msgsaved (delete)

-? c4m_notification (archive p_move? and p_challange )

-? c4m_personalinfo (archive all)

-? cfm_point_caching? (archive points)

-? chess_boardstyle (archive style)

-? chess_board_type (archive? o_isdragdrop)

-? chess_club_members (delete all)

-? elo_points (archive cpoints and opoints)

-? message_queue (delete)

-? mobile_client_ip (delete)

-? pc_chat_messages (delete)

-? pc_chat_players (delete)

-? player_credits (archive? o_credits)

-? player_last_login (delete- but relevant for selection!)

-? player_purchased_activities (delete)

The archive table for player needs to be made (not included). This is a one off activity, and all I need is the sql script for it. name: playerarchive,

gamearchive is the other table that needs to be made. I need the sql statement for both of them.

The game table is easier than the player stuff, but still reasonably complex. since it involves a query execution to collect moves. here some background to understand the main table "game". The state of a game can be derived from the two main status fields:? "status" and? "completion_status". status can be:? 'I', 'A', 'T', 'P', 'C', 'W'.? (W=Waiting, A=Accepted, C=Completed, the rest? are tournament related (scheduling)) completion status can be? 'W', 'B', 'D', 'A', 'I' (W=white wins, B=black wins, D=draw, A=abandoned,I= incomplete) From this the main stati can be derived by combining Status and completion status. key ones for deletion/archiving selection should be:

1) open challanges (status W)

2)? active games (completion status? I)

3) completed games (status C)

4) all games (any status)

Tables involved in deletion and archiving are:

- game (all fields)

-? c4m_gamechat (archive all relevant? tgc_message as text blob)

-? c4m_gamedraws? (delete)

-? c4m_gamerealtime (delete)

-? cfm_gamesrealtime (delete)

-? cfm_game_options (delete)

-? move_history (collect moves and archive into single line) >>*!<<

-? timed_games (delete)

-? timed_game_stats (delete)

You might notice that deleting games is easier than deleting players, and the two are interconnected. In archive mode this should not matter.?

INCLUDED FILES LOCATION:

------------------------

root: [url removed, login to view] (this file)

application folder structure: inside is the actual deployed html directory structure in use

../phpchess - application folder

../phpchess/bin - location of config file and Function code - your functions code goes into this directory.

../phpchess/admin - location of the content page framework for administrators - your page goes here, you should use the sample file as a guide

../phpchess/includes/cells - the page content should go here - this is where you will put the content of the admin page (see sample as reference)

../phpchess/skins/default - existing skin - no new elements should be needed here

../phpchess/install - contains the installation script for the db

The rest should be self explanatory / not relevant.

>>*!<<

? I need some input for this(move history), since I will need to read these for replay archived games purposes. Should it be simicolon seperated? let me know what works best to read the values back out. current move retrieval function is:

function get_move_history_list($ConfigFile, $game_id){

$conf = $this->conf;

$result="*";

$moves=1;

$hist="[Event \"".$game_id."\"]\n[CustomFEN \"".$this->GetGameCustomStaringFEN($ConfigFile, $game_id)."\"]\n[Mode \"ICS\"]\n";

$next_to_move="w";

// connect to mysql and open database

// before we create the PGN move string, gather info on the game itself for the PGN tags.

$stt = "SELECT game.completion_status FROM game WHERE game_id='".$game_id."'";

$sttreturn = mysql_query($stt) or die(mysql_error());

$sttnum = mysql_numrows($sttreturn);

$status = mysql_result($sttreturn,0,0);;

if($status == "W"){

$result="1-0";

}elseif($status == "B"){

$result="0-1";

}elseif($status == "D"){

$result="1/2-1/2";

}

$sti = "SELECT * FROM move_history WHERE game_id='".$game_id."' ORDER BY time ASC";

$stireturn = mysql_query($sti) or die(mysql_error());

$stinum = mysql_numrows($stireturn);

$stt22 = "SELECT * FROM game WHERE game_id='".$game_id."'";

$sttreturn22 = mysql_query($stt22) or die(mysql_error());

$sttnum22 = mysql_numrows($sttreturn22);

Habilidades: Engenharia, MySQL, PHP, Arquitetura de software, Teste de Software, Hospedagem Web, Gestão de Site , Teste de Website

Ver mais: who to use as a reference, what do i need to go into game design, what are the application of data structure, ups drop box, tournament 2008, this day in history database, the w games, the tournament 2008, the best php coder, the best game site design, string in data structure, string data structure, simple html scripts, scripts in html, sample ics file, root info, php string to html, php main functions, php coder file, php admin framework, people needed for game design, online game need coder, on line game design, one deal a day script, number of possible chess games

Acerca do Empregador:
( 59 comentários ) Weybridge, United Kingdom

ID do Projeto: #3050608

Premiar a:

webdeversl

See private message.

$76.5 USD em 5 dias
(13 Avaliações)
3.7

5 freelancers are bidding on average $112 for this job

technologybox

See private message.

$127.5 USD in 5 dias
(43 Comentários)
5.2
ubunto

See private message.

$127.5 USD in 5 dias
(70 Comentários)
4.9
sannioprogetti

See private message.

$127.5 USD in 5 dias
(5 Comentários)
4.0
marcellepaisley

See private message.

$102 USD in 5 dias
(7 Comentários)
3.8