Page 1 of 1
Code Using a Database
Posted: Wed Feb 23, 2005 5:48 pm
by Nukes
Hey guys, this code will help you manage your guild data in a MySQL database. I do need some help in reading the "guildinfo.csv" file and putting the information into a table on the database. We also need to write this so it updates current members and adds new members as it reads. Here's what I have so far.
Code: Select all
<?php
include('db_connect.php');
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'<br>Local TimeStamp Updated';
}
// Reads remote guildinfo.csv file and exports information to database
function GetRemoteData()
{ echo'<br>Getting Data From Census Server';
// NEED HELP WITH THIS CODE
// NEED HELP WITH THIS CODE
}
// Creates a table with guild information from local database
function GetLocalData()
{ echo'<br>Getting Data From Local Database';
$q = 'SELECT `name`,`race`,`class`,`rank`,`level`,`last_seen` FROM `guild_roster` ORDER BY `level` ASC ';
$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-1;$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 '<br>Rest Timestamp';
echo '<br>Local Time Stamp : ' . strftime("%m/%d/%y %H:%M:%S",$local_timestamp) . '<br>';
}
else
{ echo 'TimeStamp: '.$info[1];
echo '<br>Local Time Stamp : ' . strftime("%m/%d/%y %H:%M:%S",$local_timestamp) . '<br>';
}
}
// Reads Remote Date
function GetRemoteDate()
{ global $remote_timestamp;
$filename = "http://www.warcraftrealms.com/exports/status.txt";
$infile = fopen ($filename, "r");
if (!$infile)
{ echo "<p>REMOTE: Unable to open status file.<br>";
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) . '<br>';
}
fclose( $infile );
}
// Checks Time Stamps and Determines what to do
function CheckTimeStamps()
{ global $local_timestamp;
global $remote_timestamp;
echo'<br>';
echo'Checking Time Stamps....';
echo'<br>Local: '.$local_timestamp;
echo'<br>Reomote: '.$remote_timestamp;
$time = $remote_timestamp - $local_timestamp;
echo'<br>Difference in time strings: '.$time;
if( $time > 43200)
{ echo'<br>Update Needed';
UpdateLocal($remote_timestamp);
GetRemoteData();
GetLocalData();
}
else
{ echo'<br>Update Not Needed';
GetLocalData();
}
}
//ResetLocal(); // Uncomment for Debugging Only
GetLocalDate();
GetRemoteDate();
CheckTimeStamps();
?>
Posted: Wed Feb 23, 2005 8:09 pm
by Rollie
I didn't test this code, but this should do what you need it to, even if there are some unseen syntax errors =)
Code: Select all
$filename = 'http://www.warcraftrealms.com/exports/guildexport.php?guildid=' . $guild_id;
$infile = fopen ($filename, "r");
if (!$infile)
{
echo "<p>Unable to open remote file.<br>\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() . " <br />\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() . " <br />\n");
echo "Inserting new entry => $name<br />\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() . " <br />\n");
echo "Updating existing entry => $name<br />\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.
Posted: Fri Mar 04, 2005 11:24 pm
by Nukes
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
Posted: Fri Mar 04, 2005 11:52 pm
by Guest
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.
Posted: Sat Mar 05, 2005 12:10 am
by Nukes
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: Select all
<?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'<br>Local TimeStamp Updated';
}
// Reads remote guildinfo.csv file and exports information to database
function GetRemoteData()
{ $guild_id=64361;
echo'<br>Getting Data From Census Server';
$filename = 'http://www.warcraftrealms.com/exports/guildexport.php?guildid='.$guild_id;
$infile = fopen ($filename, "r");
if (!$infile)
{ echo "<p>Unable to open remote file.<br>\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'<br>NAME: '.$name;
echo'<br>RACE: '.$race;
echo'<br>CLASS: '.$class;
echo'<br>LEVEL: '.$level;
echo'<br>LAST SEEN: '.$last_seen;
echo'<br>RANK: '.$rank;
$query = "SELECT id FROM `guild_roster` WHERE name = '$name'";
//echo'<br>QUERY: '.$query;
$result = mysql_query($query) or die ("<br>Error unable to select " . mysql_error() . " <br />\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'<br>QUERY: '.$query;
mysql_query($query) or die ("<br>Error unable to insert " . mysql_error() . " <br />\n");
echo "Inserting new entry => $name<br />\n";
}
else
{ // this character is already present, let's just update him
$charid = $row[0];
echo'<br>CHAR ID: '.$charid;
$query = "UPDATE guild_roster SET rank = '$rank', level = '$level', last_seen = '$last_seen' WHERE id = '$charid'";
mysql_query($query) or die ("<br>Error unable to update " . mysql_error() . " <br />\n");
echo "Updating existing entry => $name<br />\n";
}
}
fclose($infile);
}
// Creates a table with guild information from local database
function GetLocalData()
{ echo'<br>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 '<br>Rest Timestamp';
echo '<br>Local Time Stamp : ' . strftime("%m/%d/%y %H:%M:%S",$local_timestamp) . '<br>';
}
else
{ echo 'TimeStamp: '.$info[1];
echo '<br>Local Time Stamp : ' . strftime("%m/%d/%y %H:%M:%S",$local_timestamp) . '<br>';
}
}
// Reads Remote Date
function GetRemoteDate()
{ global $remote_timestamp;
$filename = "http://www.warcraftrealms.com/exports/status.txt";
$infile = fopen ($filename, "r");
if (!$infile)
{ echo "<p>REMOTE: Unable to open status file.<br>";
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) . '<br>';
}
fclose( $infile );
}
// Checks Time Stamps and Determines what to do
function CheckTimeStamps()
{ global $local_timestamp;
global $remote_timestamp;
echo'<br>';
echo'Checking Time Stamps....';
echo'<br>Local: '.$local_timestamp;
echo'<br>Reomote: '.$remote_timestamp;
$time = $remote_timestamp - $local_timestamp;
echo'<br>Difference in time strings: '.$time;
if( $time > 43200)
{ echo'<br>Update Needed';
UpdateLocal($remote_timestamp);
GetRemoteData();
GetLocalData();
}
else
{ echo'<br>Update Not Needed';
GetLocalData();
}
}
//ResetLocal(); // Uncomment for Debugging Only
GetLocalDate();
GetRemoteDate();
CheckTimeStamps();
?>
Posted: Sat Mar 05, 2005 12:22 am
by Guest
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.
Posted: Mon Mar 21, 2005 1:13 pm
by Rollie
One easy way to do that would be to delete all entries each time before you update it.
Posted: Sat Apr 30, 2005 5:09 am
by Vimes
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: Select all
$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: Select all
<?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
Posted: Sat Apr 30, 2005 12:59 pm
by Rollie
Long as the link is there, I'm happy =) Any language will do! Cept maybe Swahili.... wonder what that would look like anyway... hrm...
Posted: Thu Sep 01, 2005 8:58 am
by Precisi
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!
Posted: Sun Nov 27, 2005 4:57 pm
by Praxi
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.
Posted: Thu Jan 12, 2006 8:31 am
by Groyff
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
Posted: Thu Jan 12, 2006 11:18 am
by Ceto
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.
Posted: Fri Jan 13, 2006 6:27 am
by Groyff
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.
Can I get the php files?
Posted: Thu Jun 01, 2006 3:13 pm
by dantoonina
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.
Posted: Mon Oct 23, 2006 5:15 am
by Groyff
I guess this request is obsolete, dantoonina?
If you still want them I'll send'em
/G