WarcraftRealms.com

Re: Code Using a Database

Groyff — Mon Oct 23, 2006 5:15 am

Author: Groyff

Posted: Mon Oct 23, 2006 5:15 am

I guess this request is obsolete, dantoonina?
If you still want them I'll send'em

/G

Can I get the php files?

dantoonina — Thu Jun 01, 2006 3:13 pm

Author: dantoonina

Posted: Thu Jun 01, 2006 3:13 pm

Groyff,
Can you do me a favor? Can I get a copy of the php files? If you zip them up, I would like to use yours on our guild site. I don't know much, but if you can add a not how to install it to a database I can create, I think I can get it running. Can you send it to me at my email address? Thanks in advance.

Anthony

Groyff wrote:

Thanks for the tip Ceto. For now I've done it so I have the rosterupdater at a server with remote file access, but the form thing sounds like good idea, gonna check in to it when I got then time.

The roster can be seen at http://www.violence-reborn.co.uk
oh, and I'm gonna add info that data is fetched from www.warcraftrealms.com aswell, just slipped my mind.

Re: Code Using a Database

Groyff — Fri Jan 13, 2006 6:27 am

Author: Groyff

Posted: Fri Jan 13, 2006 6:27 am

Thanks for the tip Ceto. For now I've done it so I have the rosterupdater at a server with remote file access, but the form thing sounds like good idea, gonna check in to it when I got then time.

The roster can be seen at http://www.violence-reborn.co.uk
oh, and I'm gonna add info that data is fetched from www.warcraftrealms.com aswell, just slipped my mind.

Re: Code Using a Database

Ceto — Thu Jan 12, 2006 11:18 am

Author: Ceto

Posted: Thu Jan 12, 2006 11:18 am

If you have shell access, you could probably write a bash/python/etc. script to download the roster. You can even have the script spawn whatever PHP script parses the file, set it all up via cron for automatic updates ever 12 hours or so.

Or, here's a crazy idea: create a web page with a textarea form field. Set a JavaScript onload event that checks for an updated roster, fetches that roster via HTTP, and inserts the content into the form field. On submit, the data goes to a PHP script that injects the roster into your database. That way you're sidestepping all the server-side issues and providing the data via your web browser, sort of like uploading the census .txt file but with less intervention.

Re: Code Using a Database

Groyff — Thu Jan 12, 2006 8:31 am

Author: Groyff

Posted: Thu Jan 12, 2006 8:31 am

Hi!
Perhaps I should post this at a sql/php forum. But I'll try here first.

Trying to create a workaround to the remote file access problem.
Since our guild forums host don't allow fopen and don't have cURL installed can I for the Guild Roster point to a sql-database located on a different server?
Or even better, could I have a php-script situated elsewhere uploading roster info to a the database at the forum host.

Cheers,
Groyff

Re: Code Using a Database

Anonymous — Sun Nov 27, 2005 4:57 pm

Author: Anonymous

Posted: Sun Nov 27, 2005 4:57 pm

Just a quick noobie question here. How do I know what database the characters table is going into? It would be nice if the database scripts and instructions for this part would still be updated, and maybe some more examples!

My Background:
I know HTML decently, I can follow php code (or know where to look to figure out what its doing), mysql I'm so so with (mainly do anything required through myphpadmin.

Re: Code Using a Database

Precisi — Thu Sep 01, 2005 8:58 am

Author: Precisi

Posted: Thu Sep 01, 2005 8:58 am

I have SOME sql experience, but I'm a bit confused on what to do with all this code. Is anyone willing to write up some simple instructions like..

Step 1: Make a database with these fields..
Step 2: Use this code named as this file..
etc.

It would be greatly appreciated. I would like to get a nice guild listing on my guilds site (redesigning the whole thing or I would link to it) and then once I get everything all set up I'm willing to provide all the files in a tidy zip with easy instructions for other people like me who "don't quite get it".

I have a basic guild roster running here: http://closetgamers.snowshoedesign.com/census/

I used Schwarzhuf's code, but I would like to offer sorting and whatnot with a database.

Any guidance is appreciated!

Re: Code Using a Database

Rollie — Sat Apr 30, 2005 12:59 pm

Author: Rollie

Posted: Sat Apr 30, 2005 12:59 pm

Long as the link is there, I'm happy =) Any language will do! Cept maybe Swahili.... wonder what that would look like anyway... hrm...

Re: Code Using a Database

Anonymous — Sat Apr 30, 2005 5:09 am

Author: Anonymous

Posted: Sat Apr 30, 2005 5:09 am

Hi There!

I did a solution that clears the table completely, so removing chars no longer in the guild according to census data.

the main file "roster.php" will include the "getdata.php" whenever the file is called and the timestamp in the database is not corresponding with the current timestamp of the census data, therefore not loading/processing the code for updating when not needed

Code:

$filename = "http://www.warcraftrealms.com/exports/status.txt";
$fp = fopen($filename,"r");
$status = fread($fp,65535);
$query = "SELECT * FROM timestamp WHERE timestamp = $status";
$result = mysql_query($query);
$count=mysql_numrows($result);
if ($count == 0)
{
include ("getdata.php");
}

the getdata.php looks like this:

Code:

<?php$row = 1;
$i = 0; // used to clear headers from csv file
$handle = fopen ("http://www.warcraftrealms.com/exports/guildexport.php?guildid=foobar","r");

// clear table first
$clear = "TRUNCATE TABLE characters";
mysql_query($clear);

// insert new data
while ( ($data = fgetcsv ($handle, 1000, ",")) !== FALSE ) {
if ($i != 0) // $i will be 0 when column headers are provided
{
$update="INSERT INTO characters (CharName, Race, Class, Level, LastSeen, GuildRank) VALUES ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";
mysql_query ($update);
}
$i++;
}
fclose ($handle);

// update the timestamp in the database
$updatestatus="UPDATE timestamp SET timestamp = $status";
mysql_query ($updatestatus);

?>

the performance would be better using inserts and updates of course, but if your guild does not have 5K+ members you will not feel it i guess

you can find a running copy here:
http://www.warmuth.cc/roster/roster.php
The running version includes a column representing the char as a "main" or "alt of <maincharname>"

hope this helps, cheers, Vimes

P.S.: I did the obligatory link in german language on the page, i hope you don't mind

Re: Code Using a Database

Rollie — Mon Mar 21, 2005 1:13 pm

Author: Rollie

Posted: Mon Mar 21, 2005 1:13 pm

One easy way to do that would be to delete all entries each time before you update it.

Re: Code Using a Database

Anonymous — Sat Mar 05, 2005 12:22 am

Author: Anonymous

Posted: Sat Mar 05, 2005 12:22 am

Nevermind the issues I had, was with a setting in my PHP.ini. That last code that I posted works great. Just comment out some of the unnecessary echo lines. Thanks for the help on that Rollie.

Re: Code Using a Database

Anonymous — Sat Mar 05, 2005 12:10 am

Author: Anonymous

Posted: Sat Mar 05, 2005 12:10 am

Here's my code, however, when it updates there are some issues with update speed. As it goes through the document it sometimes stops halfway through. Therefore not everything is updated. In order to get all the information on the database I would need to uncomment the resetlocaltime line which would then cause the update everytime (which you do not want us to do). Let me know if you have any solutions.

Code:

<?php

include('db_connect.php');
// 64361
global $guild_id;
global $local_timestamp;
global $remote_timestamp;

//////////////////////////////////////////////////////////////////
// SQL TABLE SETUP //
// Census = id, date //
// guild_roster = id, name, race, class, rank, level, last_seen //
//////////////////////////////////////////////////////////////////

// Resets the local census status field to null - Debuging Only
function ResetLocal()
{ $q = 'UPDATE `Census` SET `date` = "" WHERE `id` = 1 LIMIT 1';
$result = mysql_query($q);
if (!$result)
{ echo 'ERROR: Could not run query: '.mysql_error();
exit;
}
}

// Updates the local status field to variable $date
function UpdateLocal($date)
{ $q = 'UPDATE `Census` SET `date` = "'.$date.'" WHERE `id` = 1 LIMIT 1';
$result = mysql_query($q);
if (!$result)
{ echo 'ERROR: Could not run query: '.mysql_error();
exit;
}
echo' Local TimeStamp Updated';
}

// Reads remote guildinfo.csv file and exports information to database
function GetRemoteData()
{ $guild_id=64361;
echo' Getting Data From Census Server';
$filename = 'http://www.warcraftrealms.com/exports/guildexport.php?guildid='.$guild_id;
$infile = fopen ($filename, "r");
if (!$infile)
{ echo "Unable to open remote file. \n";
exit;
}
// do one read to get the header
$buffer = fgets($infile, 4096);
// read the entries
while (!feof ($infile))
{ $buffer = fgets($infile, 4096);
list( $name, $race, $class, $level, $last_seen, $rank ) = explode(",",$buffer);
echo' NAME: '.$name;
echo' RACE: '.$race;
echo' CLASS: '.$class;
echo' LEVEL: '.$level;
echo' LAST SEEN: '.$last_seen;
echo' RANK: '.$rank;
$query = "SELECT id FROM `guild_roster` WHERE name = '$name'";
//echo' QUERY: '.$query;
$result = mysql_query($query) or die (" Error unable to select " . mysql_error() . " \n");
if (!$result)
{ die('Could not query:' . mysql_error());
}
if( ! $row = mysql_fetch_row($result))
{ // there is no entry for this character, so insert
$query = "INSERT INTO guild_roster ( name, race, class, rank, level, last_seen) " .
"VALUES ('$name', '$race', '$class', '$rank', '$level', '$last_seen')";
echo' QUERY: '.$query;
mysql_query($query) or die (" Error unable to insert " . mysql_error() . " \n");
echo "Inserting new entry => $name \n";
}
else
{ // this character is already present, let's just update him
$charid = $row[0];
echo' CHAR ID: '.$charid;
$query = "UPDATE guild_roster SET rank = '$rank', level = '$level', last_seen = '$last_seen' WHERE id = '$charid'";
mysql_query($query) or die (" Error unable to update " . mysql_error() . " \n");
echo "Updating existing entry => $name \n";
}
}
fclose($infile);
}

// Creates a table with guild information from local database
function GetLocalData()
{ echo' Getting Data From Local Database';
$q = 'SELECT `name`,`race`,`class`,`rank`,`level`,`last_seen` FROM `guild_roster` ORDER BY `class` ';
$result = mysql_query($q);
if (!$result)
{ echo 'ERROR: Could not run query: '.mysql_error();
exit;
}
echo'<hr>';
echo'<center>';
echo'<h1>Invictus Mortem</h1>';
echo'<table border=1>';
echo'<tr><td>Name</td><td>Race</td><td>Class</td><td>Rank</td><td>Level</td><td>Last Seen</td></tr>';
echo'<tr>';
$rows = mysql_num_fields($result);
while ($info = mysql_fetch_row($result))
{ for($i=0; $i<$rows;$i++)
{ echo'<td>'.$info[$i].'</td>';
}
//echo'<td>'.strftime("%m/%d/%y %H:%M:%S",$info[$rows]).'</td>';
echo'</tr>';
}
echo'</table>';
echo'</center>';
}

// Reads Local Date
function GetLocalDate()
{ global $local_timestamp;
$q = "SELECT * FROM Census WHERE id=1";
$result = mysql_query($q);
if (!$result)
{ echo 'ERROR: Could not run query: '.mysql_error();
exit;
}

$info = mysql_fetch_row($result);
$local_timestamp = $info[1];

if($info[1]==null)
{ echo'First Time Running';
$q = 'UPDATE `Census` SET `date` = "0000000000" WHERE `id` = 1 LIMIT 1';
$result = mysql_query($q);
if (!$result)
{ echo 'ERROR: Could not run query: '.mysql_error();
exit;
}
$local_timestamp = "00000000";
echo ' Rest Timestamp';
echo ' Local Time Stamp : ' . strftime("%m/%d/%y %H:%M:%S",$local_timestamp) . ' ';
}
else
{ echo 'TimeStamp: '.$info[1];
echo ' Local Time Stamp : ' . strftime("%m/%d/%y %H:%M:%S",$local_timestamp) . ' ';

}
}

// Reads Remote Date
function GetRemoteDate()
{ global $remote_timestamp;
$filename = "http://www.warcraftrealms.com/exports/status.txt";
$infile = fopen ($filename, "r");
if (!$infile)
{ echo "REMOTE: Unable to open status file. ";
exit;
}
if(!feof ($infile))
{ $buffer = fgets($infile, 4096);
$remote_timestamp = trim( $buffer );
echo 'Remote Time Stamp : ' . strftime("%m/%d/%y %H:%M:%S",$remote_timestamp) . ' ';
}
fclose( $infile );
}

// Checks Time Stamps and Determines what to do
function CheckTimeStamps()
{ global $local_timestamp;
global $remote_timestamp;
echo' ';
echo'Checking Time Stamps....';
echo' Local: '.$local_timestamp;
echo' Reomote: '.$remote_timestamp;
$time = $remote_timestamp - $local_timestamp;
echo' Difference in time strings: '.$time;
if( $time > 43200)
{ echo' Update Needed';
UpdateLocal($remote_timestamp);
GetRemoteData();
GetLocalData();
}
else
{ echo' Update Not Needed';
GetLocalData();
}
}

//ResetLocal(); // Uncomment for Debugging Only
GetLocalDate();
GetRemoteDate();
CheckTimeStamps();

?>

Re: Code Using a Database

Anonymous — Fri Mar 04, 2005 11:52 pm

Author: Anonymous

Posted: Fri Mar 04, 2005 11:52 pm

I'm having trouble getting the query statements right. I am using MySQL version 2.6.1-rc2. The problem is all the single quotes and appending PHP variables. Any help would be appreciated.

Re: Code Using a Database

Anonymous — Fri Mar 04, 2005 11:24 pm

Author: Anonymous

Posted: Fri Mar 04, 2005 11:24 pm

Getting Data From Census ServerError unable to select You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

Re: Code Using a Database

Rollie — Wed Feb 23, 2005 8:09 pm

Author: Rollie

Posted: Wed Feb 23, 2005 8:09 pm

I didn't test this code, but this should do what you need it to, even if there are some unseen syntax errors =)

Code:

$filename = 'http://www.warcraftrealms.com/exports/guildexport.php?guildid=' . $guild_id;

$infile = fopen ($filename, "r");
if (!$infile)
{
echo "Unable to open remote file. \n";
exit;
}

// do one read to get the header
$buffer = fgets($infile, 4096);

// read the entries
while (!feof ($infile))
{
$buffer = fgets($infile, 4096);
list( $name, $race, $class, $level, $last_seen, $rank )
= explode(",",$buffer);

$query = "SELECT id FROM guild_roster WHERE name = $name";
$result = mysql_query($query)
or die ("Error unable to select " . mysql_error() . " \n");
if (!$result)
{
die('Could not query:' . mysql_error());
}

if( ! $row = mysql_fetch_row($result))
{
// there is no entry for this character, so insert
$query = "INSERT INTO guild_roster ( name, race, class, rank, level, last_seen) " .
"VALUES ($name, $race, $class, $rank, $level, $last_seen)";
mysql_query($query)
or die ("Error unable to insert " . mysql_error() . " \n");

echo "Inserting new entry => $name \n";
}
else
{
// this character is already present, let's just update him
$charid = $row[0];
$query = "UPDATE guild_roster SET rank = $rank, level = $level, last_seen = $last_seen WHERE id = $charid";

mysql_query($query)
or die ("Error unable to update " . mysql_error() . " \n");

echo "Updating existing entry => $name \n";
}
}
fclose($infile);

Depending on how you have the indexes set up on the table, you could do it all with a single REPLACE statement.