WarcraftReamls.com
  FAQFAQ    SearchSearch    MemberlistMemberlist    UsergroupsUsergroups   RegisterRegister 
  ProfileProfile    Log in to check your private messagesLog in to check your private messages    Log inLog in 
Code Using a Database

 
Post new topic   This topic is locked: you cannot edit posts or make replies.    WarcraftRealms.com Forum Index -> WarcraftRealms Developers
View previous topic :: View next topic  
Author Message
Nukes
Guest







PostPosted: Wed Feb 23, 2005 5:48 pm    Post subject: Code Using a Database Reply with quote

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:
<?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();

?>
Back to top
Rollie
Site Admin


Joined: 28 Nov 2004
Posts: 5374
Location: Austin, TX
WR Updates: 480,131
Rollie WR Profile

PostPosted: Wed Feb 23, 2005 8:09 pm    Post subject: Reply with quote

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 "<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.
Back to top
View user's profile Send private message Visit poster's website
Nukes
Guest







PostPosted: Fri Mar 04, 2005 11:24 pm    Post subject: Reply with quote

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
Back to top
Guest








PostPosted: Fri Mar 04, 2005 11:52 pm    Post subject: Reply with quote

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.
Back to top
Nukes
Guest







PostPosted: Sat Mar 05, 2005 12:10 am    Post subject: Reply with quote

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'<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();

?>
Back to top
Guest








PostPosted: Sat Mar 05, 2005 12:22 am    Post subject: Reply with quote

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.
Back to top
Rollie
Site Admin


Joined: 28 Nov 2004
Posts: 5374
Location: Austin, TX
WR Updates: 480,131
Rollie WR Profile

PostPosted: Mon Mar 21, 2005 1:13 pm    Post subject: Reply with quote

One easy way to do that would be to delete all entries each time before you update it.
Back to top
View user's profile Send private message Visit poster's website
Vimes
Guest







PostPosted: Sat Apr 30, 2005 5:09 am    Post subject: Reply with quote

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 Wink

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 Wink
Back to top
Rollie
Site Admin


Joined: 28 Nov 2004
Posts: 5374
Location: Austin, TX
WR Updates: 480,131
Rollie WR Profile

PostPosted: Sat Apr 30, 2005 12:59 pm    Post subject: Reply with quote

Long as the link is there, I'm happy =) Any language will do! Cept maybe Swahili.... wonder what that would look like anyway... hrm...
Back to top
View user's profile Send private message Visit poster's website
Precisi



Joined: 01 Sep 2005
Posts: 10

WR Updates: 11,987
Precisi WR Profile

PostPosted: Thu Sep 01, 2005 8:58 am    Post subject: Reply with quote

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!
_________________
Back to top
View user's profile Send private message
Praxi
Guest







PostPosted: Sun Nov 27, 2005 4:57 pm    Post subject: Reply with quote

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! Smile

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.
Back to top
Groyff



Joined: 27 Jul 2005
Posts: 14

WR Updates: 14,473
Groyff WR Profile

PostPosted: Thu Jan 12, 2006 8:31 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Ceto
Shady Dealer


Joined: 16 Oct 2005
Posts: 350
Location: Plymouth, NH
WR Updates: 193,167
Ceto WR Profile

PostPosted: Thu Jan 12, 2006 11:18 am    Post subject: Reply with quote

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.
_________________
Back to top
View user's profile Send private message Visit poster's website
Groyff



Joined: 27 Jul 2005
Posts: 14

WR Updates: 14,473
Groyff WR Profile

PostPosted: Fri Jan 13, 2006 6:27 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
dantoonina



Joined: 01 Jun 2006
Posts: 3

WR Updates: 0
dantoonina WR Profile

PostPosted: Thu Jun 01, 2006 3:13 pm    Post subject: Can I get the php files? Reply with quote

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.
Back to top
View user's profile Send private message MSN Messenger
Groyff



Joined: 27 Jul 2005
Posts: 14

WR Updates: 14,473
Groyff WR Profile

PostPosted: Mon Oct 23, 2006 5:15 am    Post subject: Reply with quote

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

/G
_________________
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   This topic is locked: you cannot edit posts or make replies.    WarcraftRealms.com Forum Index -> WarcraftRealms Developers All times are GMT - 6 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
WarcraftRealms.com  


Powered by phpBB © 2001, 2005 phpBB Group