An all-purpose PHP-based tool ("custom")

[AndMetal]

First and foremost, by no means in this "tool" complete. If anything, I would consider this to still be in the alpha/beta stages of development. However, I figure since it helps me a lot, I would share it with the community.

I have put together several different PHP-based tools to allow myself to view and edit information in the EQ Emu server database a lot easier than using a generic tool to directly access and modify it. It also allows me to make changes to my server wherever I am, and is platform independent (which can also mean accessing from mobile phones, at least to a certain extent )

Unfortunately, they are all fairly interconnected, so it isn't quite practical to release each as separate tools.

Completed functionality:
None yet :(

Beta functionality:
View a list of accounts
View a list of characters (also by account & sortable)
View characters, including a complete inventory, stats (sort of), skills, AA's, buffs, memorized spells, and the complete spell book (made to look relatively close to the in-game UI)
Edit items on a merchant (based on merchantid)
View petitions

Alpha functionality:
View, search for, and edit items (is missing several fields)
Restore a character from a backup (not very user friendly currently)
Add and edit NPC types (need to make more user friendly)
Create a table for spells (including creating all 202 columns & importing directly from spells_us.txt, no user input currently)

Future functionality:
Add more functionality to account list (add sorting, change status/password)
Create spawn points for NPCs
Edit characters
Modify/work petitions
And much more I'm sure

A direct link for what will be the "latest" download is here: http://www.andmetal.net/eqemu/custom/custom.zip

I will also keep a list of all versions here: http://www.andmetal.net/eqemu/custom/

I will also see if I can put some screenshots up of what some of the pages look like in action.

I will also try to add information to the Wiki to help anyone trying to create similar tools (my page in the Wiki)

[rojadruid]

This tool would be great if it was incorperated<sp?> into the existing http server tool.

[AndMetal]

I was thinking of doing that, although I'd have to do some learning (according to the Wiki, it uses Perl). A definite positive is that it would be able to interface with both the database data AND the dynamic data in the worldserver. In theory, it would work great for spells.

Although, if someone wants to take what I have already put together to create a better interface for the World HTTP Server, I won't be upset As of right now, I'm fairly comfortable with PHP (with what I would consider to be intermediate experience), so I might just stick with it for now.

[cubber]

This is a great utility! I am having a bit of an issue with anything that has to do with spells though. I did not see a readme in the file with installation instructions but I did find a file in the includes folder called constants.php that I put my database info into which allowed me to view the site but not spell data.

Did I need to do anything else to get the spell functionality working? I saw a file in the spells folder called add_spells.php, i copied my spells_* files from my client and put them in that folder then edited the add_spells.php file to point to "." to look for them. I then tried running add_spells.php in a browser but it failed. To me it looks like it is supposed to create a table in the eqem database called spells_new that the site is supposed to read from for its spell data?

Any information would be helpful, and thanks for your hard work on this very promising tool!

[cubber]

I looked over both of the files a bit more and noticed that they both contain the statement:

include "../includes/db_login.inc";

but there is not file called db_login.inc in the includes folder...

[cubber]

It looks like the add_spells file takes the info from the spells_* file and adds it to the table spells_new, but where is the file that creates the table spells_new in the eqemu database?

[AndMetal]

Sorry I didn't reply back sooner (haven't been on the forums as much recently).

The main reason I haven't put together a readme file yet is because this is still in the alpha stages. Once I complete some of the functionality and streamline some of the files into others, I'll be able to focus on a more user-friendly approach. In the meantime, you can just post here if you have any questions or concerns.

The idea behind the spell folder was for a few things (eventually): importing the spells_us.txt file into the database, viewing spells, editing spells in the database, and exporting a new spells_us.txt file from the database (for easy distribution to others via a web page). Thus far, only the first has been semi-implemented.

The spells folder, right now, contains scripts to import the spells_us.txt file into a new table, which currently is spells_new. "spells" is used by other utilities, which don't use the same schema.

The insert_columns.php file was meant to be run after creating the table spells_new (which is currently done manually via command line or a GUI front-end, but could be incorporated into the script relatively easy), to create a base schema. Eventually, it will contain an array of all the column names instead of just creating field{$y}, which I'm still working on.

The add_spells.php file was meant to be run next, which actually imports the text file into the database. Luckily, there isn't anything really special about this, except it uses a ton of memory (more than 128MB, but less than 256MB). If your PHP server is set to run in safe mode, it might not complete correctly because of the ini_set() functions at the top.

In reference to "../includes/db_login.inc", that was an old file that I used to use if a file needed to access the database. Because everything needs access to the database plus several constants (race info, class info, etc), I incorporated it into the the constants.php file (1 less line per script). Just before I started sharing the files, I removed the file for good, but apparently forgot to change some of the references. I'll make sure I update it for future releases (thank you for catching that, by the way )

[Bishop4351]

The tool looks to be a nice start. I would recommend that you tighten up on the permissions or distribute the files in a tar or something that knows that everything isn't 777.

It's a great start can't wait for the the release.

[AndMetal]

I was able to get a Sourceforge site for the project, so the releases will be moved here.

[cubber]

Is there anymore work being done on this project. I like the magelo portion of it and am trying to set it up on my servers website so my players can view their characters. However I do not want the character_/index.php page to display the account id's, and account names. The only tables I want it to show are character name, level, and class, and when the player clicks on the character name it takes them to their profile. My php skills are limited, what do I need to edit on the character_/index.php page to make this happen?

Thanks.
Default contents of character_/index.php

<html>

<head>

<title>EQ Emu - Character List</title>

<?php

$time_start = microtime(true);
include "../includes/constants.php";
include "../includes/functions.php";

if (!isset($_GET["limit"])) {
	$_GET["limit"] = 10;
};
if ((!isset($_GET["offset"]) || ($_GET["offset"] == ""))) {
	$_GET["offset"] = 0;
};

?>

</head>

<body>

<center><h1>Character List</h1></center>

<table align="center" border="1">
	<form method="GET" action="">
	<caption align="top" style="text-align:right">
		<?php CreateResultLimit();?>
<!--		<input type="text" size="1" name="limit" value="<?php echo $_GET["limit"];?>"> Per Page
		<input type="hidden" name="offset" value="<?php echo $_GET["offset"];?>">
		<input type="hidden" name="sort" value="<?php echo $_GET["sort"];?>">
		<input type="hidden" name="dir" value="<?php echo $_GET["dir"];?>">
		<a href="?limit=<?php echo $_GET["limit"];?>&offset=<?php if ($_GET["offset"] - $_GET["limit"] >= 0) {echo $_GET["offset"] - $_GET["limit"];};?>&sort=<?php echo $_GET["sort"];?>&dir=<?php echo $_GET["dir"];?>">&lt;&lt;Prev</a>&nbsp;
		<a href="?limit=<?php echo $_GET["limit"];?>&offset=<?php echo $_GET["offset"] + $_GET["limit"];?>&sort=<?php echo $_GET["sort"];?>&dir=<?php echo $_GET["dir"];?>">Next&gt;&gt;</a>
-->	</caption>
	<tr>
		<th><?php SortLink(id,'ID');?></th>
		<th><?php SortLink(account_name,'Acct');?></th>
		<th><?php SortLink(name,'Name');?></th>
		<th><?php SortLink(level,'Level','DESC');?></th>
		<th><?php SortLink('class','Class');?></th>
		<th><?php SortLink(zonename,'Zone Name');?></th>
<!--		<th><?php SortLink(pktime,'PK Time');?></th>		-->
		<th><?php SortLink(groupid,'Group ID','DESC');?></th>
		<th><?php SortLink(timelaston,'Last On','DESC');?></th>
		<th><?php SortLink(birthday,'Birthday','DESC');?></th>
	</tr>
<?php flush(); ob_flush();?>	<tr>
		<td align="right">
			<?php // CreateDropdown(id,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown(account_name,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown(name,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown(level,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown('class',character_);?>&nbsp;
		</td>
		<td>
			<?php CreateDropdown(zonename,character_);?>&nbsp;
		</td>
<!--		<td>
			<?php // CreateDropdown(pktime,character_);?>&nbsp;
		</td>
-->		<td align="right">
			<?php CreateDropdown(groupid,character_);?>
		</td>
		<td>
			<?php // CreateDropdown(timelaston,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown(birthday,character_);?>&nbsp;
		</td>
	</tr>
	</form>
<?php

if ($_GET["account_id"] != "") {
	$where = " WHERE account_id=" . $_GET["account_id"] . " ";
};
if (!isset($_GET["sort"]) || ($_GET["sort"] == "")) {
	$_GET["sort"] = "timelaston";
};
if (!isset($_GET["dir"]) || ($_GET["dir"] == "")) {
	$_GET["dir"] = "DESC";
};
$query1 = "SELECT character_.id, account.name AS account_name, character_.account_id, character_.name, character_.timelaston, character_.x, character_.y, character_.z, character_.zonename, character_.zoneid, character_.pktime, character_.groupid, character_.class, character_.level, CONV(CONCAT(LPAD(BIN(ASCII(SUBSTRING(character_.profile,232,1))),8,'0'),LPAD(BIN(ASCII(SUBSTRING(character_.profile,231,1))),8,'0'),LPAD(BIN(ASCII(SUBSTRING(character_.profile,230,1))),8,'0'),LPAD(BIN(ASCII(SUBSTRING(character_.profile,229,1))),8,'0')),2,10) AS  birthday FROM character_ LEFT JOIN account ON account.id=character_.account_id" . $where . " ORDER BY " . $_GET["sort"] . " " . $_GET["dir"] . " ";
// Limit query if $_GET["limit"] is set
if ($_GET["limit"] != "") {
	$query1 .= " LIMIT " . $_GET["limit"] . " OFFSET " . $_GET["offset"] . " ";
};
$result1 = mysql_query($query1,$db);

// Display data if any found, message if none found

if ($db_row1 = mysql_fetch_array($result1)) {
	do {
		// Find out how many characters the account has
		global $db;
		$query_chars = "SELECT COUNT(id) AS id FROM character_ WHERE account_id='" . $db_row1["account_id"] . "'";
		$result_chars = mysql_query($query_chars,$db);
		$db_row_chars = mysql_fetch_array($result_chars);

		echo "\t<tr>
		<td align=\"right\">" . $db_row1["id"] . "</td>
		<td title=\"ID: " . $db_row1["account_id"] . "\"><a href=\"../account/?id=" . $db_row1["account_id"] . "\">" . $db_row1["account_name"] . "</a> (<a href=\"?account_id=" . $db_row1["account_id"] . "\">" . $db_row_chars["id"] . "</a>)</td>
		<td><a href=\"../inventory/?id=" . $db_row1["id"] . "\">" . $db_row1["name"] . "</a></td>
		<td align=\"right\">" . $db_row1["level"] . "</td>
		<td>" . $Classes["{$db_row1["class"]}"] . "</td>
		<td title=\"X: " . $db_row1["x"] . "\nY: " . $db_row1["y"] . "\nZ: " . $db_row1["z"] . "\">" . $db_row1["zonename"] . "</td>
<!--		<td>" . $db_row1["pktime"] . "</td>		-->
		<td align=\"right\">" . $db_row1["groupid"] . "</td>
		<td>" . date("Y-m-d H:i:s",$db_row1["timelaston"]) . "</td>
		<td>" . date("Y-m-d H:i:s",$db_row1["birthday"]) . "</td>
	</tr>\n";
	flush(); ob_flush();
	} while ($db_row1 = mysql_fetch_array($result1));
} else {
	echo "\t\t<td align=\"center\" colspan=\"11\">No records found</td>
	</tr>";
};

?>
</table>

<?php

include "../includes/footer.php";

?>

</body>

</html>

[cole89103]

change
<th><?php SortLink(id,'ID');?></th>

to

<th><?php // SortLink(id,'ID');?></th>

[cole89103]

and

<td title=\"ID: " . $db_row1["account_id"] . "\"><a href=\"../account/?id=" . $db_row1["account_id"] . "\">" . $db_row1["account_name"] . "</a> (<a href=\"?account_id=" . $db_row1["account_id"] . "\">" . $db_row_chars["id"] . "</a>)</td>

wellto be honest it wont really matter as you can see the account id will be displayed in the url reguardless im sire more of a rewrite can be worked ill have to tinker with it just a bit so gimme some time

[cubber]

That removed the titles but not the corresponding tables. How do I also prevent the tables from displaying? Like I said previously, I just want them to see the following info:

Character Name
Level
Class

This way I can put it up on a website and let all players have access to it, and it does not show everyones account id's and names.

Thanks for taking the time to help.

[AndMetal]

I have been working on it (implemented spell info plus some other tweaks), but I have changed servers since & have to setup the ability to post to Sourceforge via CVS again (which is kind of a pain in the butt).

This project mainly started out to help me hone my PHP abilities while creating some useful tools to use with the emu. I'm sure, eventually, it will get beyond what would be considered the Alpha stage to incorporate permissions, more flexibility, etc, but I doubt that will be anytime soon.

Specifically, if you're looking to display just the Character Name, Level, & Class, you would just have to remove the following code:

<html>

<head>

<title>EQ Emu - Character List</title>

<?php

$time_start = microtime(true);
include "../includes/constants.php";
include "../includes/functions.php";

if (!isset($_GET["limit"])) {
	$_GET["limit"] = 10;
};
if ((!isset($_GET["offset"]) || ($_GET["offset"] == ""))) {
	$_GET["offset"] = 0;
};

?>

</head>

<body>

<center><h1>Character List</h1></center>

<table align="center" border="1">
	<form method="GET" action="">
	<caption align="top" style="text-align:right">
		<?php CreateResultLimit();?>
<!--		<input type="text" size="1" name="limit" value="<?php echo $_GET["limit"];?>"> Per Page
		<input type="hidden" name="offset" value="<?php echo $_GET["offset"];?>">
		<input type="hidden" name="sort" value="<?php echo $_GET["sort"];?>">
		<input type="hidden" name="dir" value="<?php echo $_GET["dir"];?>">
		<a href="?limit=<?php echo $_GET["limit"];?>&offset=<?php if ($_GET["offset"] - $_GET["limit"] >= 0) {echo $_GET["offset"] - $_GET["limit"];};?>&sort=<?php echo $_GET["sort"];?>&dir=<?php echo $_GET["dir"];?>">&lt;&lt;Prev</a>&nbsp;
		<a href="?limit=<?php echo $_GET["limit"];?>&offset=<?php echo $_GET["offset"] + $_GET["limit"];?>&sort=<?php echo $_GET["sort"];?>&dir=<?php echo $_GET["dir"];?>">Next&gt;&gt;</a>
-->	</caption>
	<tr>
		<th><?php SortLink(id,'ID');?></th>
		<th><?php SortLink(account_name,'Acct');?></th>
		<th><?php SortLink(name,'Name');?></th>
		<th><?php SortLink(level,'Level','DESC');?></th>
		<th><?php SortLink('class','Class');?></th>
		<th><?php SortLink(zonename,'Zone Name');?></th>
<!--		<th><?php SortLink(pktime,'PK Time');?></th>		-->
		<th><?php SortLink(groupid,'Group ID','DESC');?></th>
		<th><?php SortLink(timelaston,'Last On','DESC');?></th>
		<th><?php SortLink(birthday,'Birthday','DESC');?></th>
	</tr>
<?php flush(); ob_flush();?>	<tr>
		<td align="right">
			<?php // CreateDropdown(id,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown(account_name,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown(name,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown(level,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown('class',character_);?>&nbsp;
		</td>
		<td>
			<?php CreateDropdown(zonename,character_);?>&nbsp;
		</td>
<!--		<td>
			<?php // CreateDropdown(pktime,character_);?>&nbsp;
		</td>
-->		<td align="right">
			<?php CreateDropdown(groupid,character_);?>
		</td>
		<td>
			<?php // CreateDropdown(timelaston,character_);?>&nbsp;
		</td>
		<td>
			<?php // CreateDropdown(birthday,character_);?>&nbsp;
		</td>
	</tr>
	</form>
<?php

if ($_GET["account_id"] != "") {
	$where = " WHERE account_id=" . $_GET["account_id"] . " ";
};
if (!isset($_GET["sort"]) || ($_GET["sort"] == "")) {
	$_GET["sort"] = "timelaston";
};
if (!isset($_GET["dir"]) || ($_GET["dir"] == "")) {
	$_GET["dir"] = "DESC";
};
$query1 = "SELECT character_.id, account.name AS account_name, character_.account_id, character_.name, character_.timelaston, character_.x, character_.y, character_.z, character_.zonename, character_.zoneid, character_.pktime, character_.groupid, character_.class, character_.level, CONV(CONCAT(LPAD(BIN(ASCII(SUBSTRING(character_.profile,232,1))),8,'0'),LPAD(BIN(ASCII(SUBSTRING(character_.profile,231,1))),8,'0'),LPAD(BIN(ASCII(SUBSTRING(character_.profile,230,1))),8,'0'),LPAD(BIN(ASCII(SUBSTRING(character_.profile,229,1))),8,'0')),2,10) AS  birthday FROM character_ LEFT JOIN account ON account.id=character_.account_id" . $where . " ORDER BY " . $_GET["sort"] . " " . $_GET["dir"] . " ";
// Limit query if $_GET["limit"] is set
if ($_GET["limit"] != "") {
	$query1 .= " LIMIT " . $_GET["limit"] . " OFFSET " . $_GET["offset"] . " ";
};
$result1 = mysql_query($query1,$db);

// Display data if any found, message if none found

if ($db_row1 = mysql_fetch_array($result1)) {
	do {
		// Find out how many characters the account has
		global $db;
		$query_chars = "SELECT COUNT(id) AS id FROM character_ WHERE account_id='" . $db_row1["account_id"] . "'";
		$result_chars = mysql_query($query_chars,$db);
		$db_row_chars = mysql_fetch_array($result_chars);

		echo "\t<tr>
		<td align=\"right\">" . $db_row1["id"] . "</td>
		<td title=\"ID: " . $db_row1["account_id"] . "\"><a href=\"../account/?id=" . $db_row1["account_id"] . "\">" . $db_row1["account_name"] . "</a> (<a href=\"?account_id=" . $db_row1["account_id"] . "\">" . $db_row_chars["id"] . "</a>)</td>
		<td><a href=\"../inventory/?id=" . $db_row1["id"] . "\">" . $db_row1["name"] . "</a></td>
		<td align=\"right\">" . $db_row1["level"] . "</td>
		<td>" . $Classes["{$db_row1["class"]}"] . "</td>
		<td title=\"X: " . $db_row1["x"] . "\nY: " . $db_row1["y"] . "\nZ: " . $db_row1["z"] . "\">" . $db_row1["zonename"] . "</td>
<!--		<td>" . $db_row1["pktime"] . "</td>		-->
		<td align=\"right\">" . $db_row1["groupid"] . "</td>
		<td>" . date("Y-m-d H:i:s",$db_row1["timelaston"]) . "</td>
		<td>" . date("Y-m-d H:i:s",$db_row1["birthday"]) . "</td>
	</tr>\n";
	flush(); ob_flush();
	} while ($db_row1 = mysql_fetch_array($result1));
} else {
	echo "\t\t<td align=\"center\" colspan=\"11\">No records found</td>
	</tr>";
};

?>
</table>

<?php

include "../includes/footer.php";

?>

</body>

</html>

You'll then want to change the last part that is pink (colspan=\"11\") to colspan=\"3\", because you only have 3 columns with the changes.

To be honest, I'm not sure what the issue is with the IDs. For the character table (which is really all you're looking at), the IDs are indexed, so MySQL is able to find it a lot faster than searching for a name, which is not indexed. As for the account table, neither the Account ID or Account Name are indexed, so you don't really see a speed difference. However, the Account Name is the same one you login with, so although it might be easier for searches, it would be bad when sharing info about a character, etc, because others will be able to see the account name (and then try guessing the password, etc). In any case, you shouldn't have to worry about the account stuff, since you don't need it for the page you're trying to make.

Anyways, hope this helps.

[cubber]

Thanks, I will give it a shot.