 |
Account Login
|
 |
|
Latest Articles
|
|
|
IRC Channel
|
|
|
Associates
|
|
|
Associates
|
|
|
 |
|
|
|
|
10-05-2007, 06:42 PM
|
#1 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
Need your feedback
Yea, this isn't working.. no idea why.
search.html
HTML Code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
<meta name="author" content="Tanax">
<script src="AJAXsearch.js"></script>
<title>Search</title>
</head>
<body>
<h1>Search:</h1>
<form>
<input type="text" name="search" onkeyup="search(this.value)">
</form>
<p>
<div id="results"><b>You can search for guilds, players, levels and vocations!</b></div>
</p>
</body>
</html>
AJAXsearch.js
Code:
var xmlHttp
function search(string) {
xmlHttp = GetXmlHttpObject()
if(xmlHttp == null) {
alert("Your browser does not support HTTP requests!")
return
}
var url = "search.php"
var url = url+"?keyword="+str
var url = url+"&sid="+Math.random()
xmlHttp.onreadystatechange = stateChanged
xmlHttp.open("GET", url, true)
xmlHttp.send(null)
}
function stateChanged() {
if(xmlHttp.readyState == 4 || xmlHttp.readyState == "complete") {
document.getElementById("results").innerHTML = xmlHttp.responseText
}
}
function GetXmlHttpObject() {
var xmlHttp = null
try {
// Firefox, Opera 8.0+, Safari
xmlHttp = new XMLHttpRequest()
}
catch (e) {
//Internet Explorer
try {
xmlHttp = new ActiveXObject("Msxml2.XMLHTTP")
}
catch (e) {
xmlHttp = new ActiveXObject("Microsoft.XMLHTTP")
}
}
return xmlHttp
}
search.php
PHP Code:
<?php
include('config.php');
$get = $_GET["keywords"];
if (isset($get) && $get != "") {
$search = urldecode($get);
$search = $system->db->makesafe($search);
$pSql = sprintf("SELECT * FROM ".$system->db->table['players']." WHERE name LIKE %1$s OR level LIKE %1$s OR vocation LIKE %1$s",
%$search%);
$pResult = $system->db->query($pSql);
$gSql = sprintf("SELECT * FROM ".$system->db->table['guilds']." WHERE name LIKE %s", %$search%);
$gResult = $system->db->query($gSql);
if (mysql_num_rows($pResult) != 0) {
echo '<h1>Players:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
echo '<table><tr>';
echo '<td>Result</td>';
echo '<td>Name</td>';
echo '<td>Level</td>';
echo '<td>Vocation</td></tr>';
for ($i = 1; $player = mysql_fetch_object($pResult); $i++) {
echo '<tr>';
echo '<td>' .$i. '</td>';
echo '<td>' .$player->name. '</td>';
echo '<td>' .$player->level. '</td>';
echo '<td>' .$player->vocation. '</td>';
echo '</tr>';
}
echo '</table>';
}
else {
echo '<h1>Players:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
}
if (mysql_num_rows($gResult) != 0) {
echo '<h1>Guilds:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($gResult). ' results.</div><br />';
echo '<table><tr>';
echo '<td>Result</td>';
echo '<td>Name</td>';
echo '<td>Owner</td></tr>';
for ($i = 1; $guild = mysql_fetch_object($gResult); $i++) {
$system->player->load($guild->ownerid);
$name = $system->player->getName();
echo '<tr>';
echo '<td>' .$i. '</td>';
echo '<td>' .$guild->name. '</td>';
echo '<td>' .$name. '</td>';
echo '</tr>';
}
echo '</table>';
}
else {
echo '<h1>Guilds:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
}
}
else {
echo "You can search for guilds, players, levels and vocations!";
}
?>
When I write something, NOTHING gets up :S
I haven't actually created the table players yet, but that shouldn't matter, it should change the results div message to a db error message in that case. |
|
|
|
|
10-05-2007, 08:21 PM
|
#2 (permalink)
|
|
Moderateur
Join Date: Apr 2007
Posts: 1,393
Thanks: 5
|
Code:
// JavaScript line 15
var url = url+"?keyword="+str
// PHP line 4
$get = $_GET["keywords"];
For starters, the two keys are different (keyword/keywords) which really won't help at all.
Also, why do you keep putting %$search% (I've noticed this in other topics)? That'll produce a syntax error because the percent symbols just aren't allowed to be used like that.
Since you're using sprintf anyway, why not put the table name as an argument? (e.g, SELECT * FROM %2$s...)
Depending on your php.ini settings, if you don't pass along the keywords in the query string then the PHP engine will raise a Notice error ("Undefined index") -- it's trivial to check if the key exists before trying to assign it's value to a variable.
In your JavaScript, with regards to the multiple " var url", you only need to use var the first time to declare url as a variable local to that function. On line 15, quoted at the top of this post, you make reference to a str variable but the function argument is called string. I'd love to see you using semi-colons at the end of the lines but that's just a personal preference.
That's it for starters, I haven't even looked at the code in more than a brief manner but the above should get you moving along a bit. |
|
|
|
|
10-05-2007, 08:48 PM
|
#3 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
Yea, but it's like the htm file doesn't even connect with the js file, because the div id result value doesn't change when I type in something.
Even if the php file is wrong, and that I did some query wrong, the mysql error should still be visible under the searchform :S
|
|
|
|
|
10-05-2007, 09:51 PM
|
#4 (permalink)
|
|
Moderateur
Join Date: Apr 2007
Posts: 1,393
Thanks: 5
|
Give your search function a different name. That, along with making the argument str instead of "string", should help.
|
|
|
|
|
10-05-2007, 10:16 PM
|
#5 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
Okey, I got this to so that the JS at least shows up:
HTML Code:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
<meta name="author" content="Tanax">
<script src="AJAXsearch.js"></script>
<title>Search</title>
</head>
<body>
<h1>Search:</h1>
<form>
<input type="text" onkeyup="search(this.value)">
</form>
<p>
<div id="txtHint"><b>You can search for guilds, players, levels and vocations!</b></div>
</p>
</body>
</html>
Code:
var xmlHttp
function search(str) {
xmlHttp=GetXmlHttpObject()
if (xmlHttp==null) {
alert ("Browser does not support HTTP Request")
return
}
var url="search.php"
url=url+"?keyword="+str
url=url+"&sid="+Math.random()
xmlHttp.onreadystatechange=stateChanged
xmlHttp.open("GET",url,true)
xmlHttp.send(null)
}
function stateChanged() {
if (xmlHttp.readyState == 4 || xmlHttp.readyState == "complete") {
document.getElementById("txtHint").innerHTML = xmlHttp.responseText
}
}
function GetXmlHttpObject() {
var xmlHttp=null;
try {
// Firefox, Opera 8.0+, Safari
xmlHttp = new XMLHttpRequest();
}
catch (e) {
//Internet Explorer
try {
xmlHttp = new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e) {
xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");
}
}
return xmlHttp;
}
PHP Code:
<?php
include('config.php');
$get = $_GET["keyword"];
if (isset($get) && $get != "") {
$search = urldecode($get);
$search = $system->db->makesafe($search);
$pSql = sprintf("SELECT * FROM %1$s WHERE name LIKE %s OR level LIKE %s OR vocation LIKE %s",
$table['players'],
'%'.$search.'%',
'%'.$search.'%',
'%'.$search.'%');
$pResult = $system->db->query($pSql);
$gSql = sprintf("SELECT * FROM %s WHERE name LIKE %s",
$table['guilds'],
'%'.$search.'%');
$gResult = $system->db->query($gSql);
if (mysql_num_rows($pResult) != 0) {
echo '<h1>Players:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
echo '<table><tr>';
echo '<td>Result</td>';
echo '<td>Name</td>';
echo '<td>Level</td>';
echo '<td>Vocation</td></tr>';
for ($i = 1; $player = mysql_fetch_object($pResult); $i++) {
echo '<tr>';
echo '<td>' .$i. '</td>';
echo '<td>' .$player->name. '</td>';
echo '<td>' .$player->level. '</td>';
echo '<td>' .$player->vocation. '</td>';
echo '</tr>';
}
echo '</table>';
}
else {
echo '<h1>Players:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
}
if (mysql_num_rows($gResult) != 0) {
echo '<h1>Guilds:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($gResult). ' results.</div><br />';
echo '<table><tr>';
echo '<td>Result</td>';
echo '<td>Name</td>';
echo '<td>Owner</td></tr>';
for ($i = 1; $guild = mysql_fetch_object($gResult); $i++) {
$system->player->load($guild->ownerid);
$name = $system->player->getName();
echo '<tr>';
echo '<td>' .$i. '</td>';
echo '<td>' .$guild->name. '</td>';
echo '<td>' .$name. '</td>';
echo '</tr>';
}
echo '</table>';
}
else {
echo '<h1>Guilds:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
}
}
else {
echo "<strong>Search again?</strong>";
}
?>
However, it gives me this error msg:
Quote:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\DB Class\search.php on line 33
Players:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\DB Class\search.php on line 61
Found results.
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\DB Class\search.php on line 65
Guilds:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\www\DB Class\search.php on line 94
Found results.
|
|
|
|
|
|
10-06-2007, 02:20 PM
|
#6 (permalink)
|
|
The Reckoner
Join Date: Sep 2007
Posts: 437
Thanks: 22
|
After a quick look through (literally 2 mins) I would guess that the error is coming from your mix of $pResult and $gResult, surely they are both supposed to be the same?
Another suggestion would be to take a look at Prototype JS it'll make things so much easier for you. With it, you could easily cut down your Ajax Search JavsScript file to a few lines of code split between a few functions. |
|
|
|
|
10-06-2007, 02:46 PM
|
#7 (permalink)
|
|
Moderateur
Join Date: Apr 2007
Posts: 1,393
Thanks: 5
|
Your SELECT queries are not valid SQL since the values used in the LIKE statements are not delimited by quotation marks. Both queries have the same issue which needs to be fixed.
For example:
PHP Code:
// Wrong: SELECT * FROM table WHERE name LIKE search_term
$gSql = sprintf("SELECT * FROM %s WHERE name LIKE %s",
// Right: SELECT * FROM table WHERE name LIKE 'search_term'
$gSql = sprintf("SELECT * FROM %s WHERE name LIKE '%s' ",
|
|
|
|
|
10-06-2007, 04:00 PM
|
#8 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
Quote:
Originally Posted by Karl
After a quick look through (literally 2 mins) I would guess that the error is coming from your mix of $pResult and $gResult, surely they are both supposed to be the same?
Another suggestion would be to take a look at Prototype JS it'll make things so much easier for you. With it, you could easily cut down your Ajax Search JavsScript file to a few lines of code split between a few functions. |
Yea, but sorry, I don't know anything about Prototype JS ://
Quote:
Originally Posted by Salathe
Your SELECT queries are not valid SQL since the values used in the LIKE statements are not delimited by quotation marks. Both queries have the same issue which needs to be fixed.
For example:
PHP Code:
// Wrong: SELECT * FROM table WHERE name LIKE search_term
$gSql = sprintf("SELECT * FROM %s WHERE name LIKE %s",
// Right: SELECT * FROM table WHERE name LIKE 'search_term'
$gSql = sprintf("SELECT * FROM %s WHERE name LIKE '%s' ",
|
Thanks! I got it to work now without any PHP errors.
However, I got this:
Quote:
Players:
Found 1 results.
Result Name Level Vocation Profile
1
Account Manager
1
0
Link
Guilds:
Found 1 results.
|
As you see, it finds 1 result from the players, and it prints the result in a table. But it also finds a result from the guilds, but it doesn't print it :S
And this is only got to do with the PHP script, so here it is:
PHP Code:
<?php
/**
||||||||||||||||||||||||||||||||||||||||||
|||| @author Tanax
|||| @copyright 2007
||||||||||||||||||||||||||||||||||||||||||
**/
include('config.php');
$get = $_GET["keyword"];
if (isset($get) && $get != "") {
// Make the searchvalue safe from injections
$search = urldecode($get);
$search = $system->db->makesafe($search);
// Search for players
$pSql = sprintf("SELECT * FROM %s WHERE name LIKE '%s' OR level LIKE '%s' OR vocation LIKE '%s'",
$table['players'],
'%'.$search.'%',
'%'.$search.'%',
'%'.$search.'%');
$pResult = $system->db->query($pSql);
// Search for guilds
$gSql = sprintf("SELECT * FROM %s WHERE name LIKE '%s'",
$table['guilds'],
'%'.$search.'%');
$gResult = $system->db->query($gSql);
// Check if the player search returned any results
if (mysql_num_rows($pResult) != 0) {
// Echoes the table
echo '<h1>Players:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
echo '<table border="1" width="500"><tr>';
echo '<th>Result</th>';
echo '<th>Name</th>';
echo '<th>Level</th>';
echo '<th>Vocation</th>';
echo '<th>Profile</th></tr>';
for ($i = 1; $player = mysql_fetch_object($pResult); $i++) {
echo '<tr>';
echo '<td><center>' .$i. '</center></td>';
echo '<td><center>' .$player->name. '</center></td>';
echo '<td><center>' .$player->level. '</center></td>';
echo '<td><center>' .$player->vocation. '</center></td>';
echo '<td><center><a href="account.php?name=' .$player->name. '">Link</a></center></td>';
echo '</tr>';
}
echo '</table>';
}
else {
echo '<h1>Players:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
}
// Check if the guild search returned any results
if (mysql_num_rows($gResult) != 0) {
// Echoes the table
echo '<h1>Guilds:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($gResult). ' results.</div><br />';
echo '<table border="1" width="500"><tr>';
echo '<th>Result</th>';
echo '<th>Name</th>';
echo '<th>Owner</th></tr>';
for ($i = 1; $guild = mysql_fetch_object($gResult); $i++) {
// Get the name of the owner
$system->player->load($guild->ownerid);
$name = $system->player->getName();
echo '<tr>';
echo '<td>' .$i. '</td>';
echo '<td>' .$guild->name. '</td>';
echo '<td>' .$name. '</td>';
echo '</tr>';
}
echo '</table>';
}
else {
echo '<h1>Guilds:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
}
}
// If the search is empty
else {
echo "<strong>Search again?</strong>";
}
?>
|
|
|
|
|
10-07-2007, 01:42 PM
|
#9 (permalink)
|
|
The Reckoner
Join Date: Sep 2007
Posts: 437
Thanks: 22
|
Hmm I personally can't see the problem. Is the query for searching guilds returning any records?
|
|
|
|
|
10-07-2007, 01:43 PM
|
#10 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
Yea, it says that it found 1 result(which I searched with mysql_num_rows to get that result), but it doesn't print it :S
|
|
|
|
|
10-07-2007, 03:20 PM
|
#11 (permalink)
|
|
Moderateur
Join Date: Apr 2007
Posts: 1,393
Thanks: 5
|
Actually, no Guilds are being found. There is an error in your code which prints out that one (actually prints however many players were found) has been found when in reality it should be printing zero.
Your code (~line 101)
PHP Code:
echo '<h1>Guilds:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($pResult). ' results.</div><br />';
Amended Code ( $pResult changed to $gResult)
PHP Code:
echo '<h1>Guilds:</h1>';
echo '<div id="smalltext">Found ' .mysql_num_rows($gResult). ' results.</div><br />';
It was just a simple typing mistake (not helped at all by the very similar variable names). |
|
|
|
|
10-07-2007, 03:29 PM
|
#12 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
HAHAHA, I'm such stupid :P
Thank you salathe!! :D
|
|
|
|
|
10-07-2007, 04:39 PM
|
#13 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
Okey, I got another issue regarding this script.
The server doesn't store the actual NAME of the vocation in the database, they store it as an integrer: 1 beeing knight for example, 2 sorcerer, 3 druid and 4 paladin. And if I just print the value from the row vocation, it prints 1 to 4. However, that's not a problem to fix.
PHP Code:
if($vocation == 1) {
echo 'knight';
}
elseif(....)
However, I want them to be able to SEARCH for vocations, so that if they type in paladin, the script search the database for everyone with vocation value of 4, get the idea?
How would I achieve this the best way possible? |
|
|
|
|
10-07-2007, 04:47 PM
|
#14 (permalink)
|
|
Moderateur
Join Date: Apr 2007
Posts: 1,393
Thanks: 5
|
Are the different vocations stored in their own table in the database?
|
|
|
|
|
10-07-2007, 05:03 PM
|
#15 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
No..
The vocation-info is stored in the c++ code in the server..
It's just a row, with a value from 1 to 4, defining what vocation the server should assign them. This row is stored in the player table..
|
|
|
|
|
10-08-2007, 10:34 AM
|
#16 (permalink)
|
|
The Reckoner
Join Date: Sep 2007
Posts: 437
Thanks: 22
|
You should sotore these vocations in a seperate table like Salathe has implied. You can then perform searches to your hearts content.
|
|
|
|
|
10-08-2007, 11:27 AM
|
#17 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
Yea, but I'm not the maker of this server script(it's a public download), so I don't really have anything to say to make them edit the db structure =//
|
|
|
|
|
10-09-2007, 10:42 AM
|
#18 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
Okey, I fixed so that if you search for the string "knight", the $search is equal to whatever number that is used to define knights.
However, it won't only list knights.. since it searched for lvl aswell, so it will search for players with level equal to the number of the vocation.
Is there any way to define search method?
Like, a checkbox.
Search by:
Vocations
Level
Name
Guild
And then the search form.
And it uses different querys depending on what box is checked?
|
|
|
|
|
10-09-2007, 11:03 AM
|
#19 (permalink)
|
|
The Reckoner
Join Date: Sep 2007
Posts: 437
Thanks: 22
|
We can't really help without seeing how the database is structured. Also, the fact that you can't search for vocations in the database is going to complicate the matter somewhat. If you can post a simplified database structure inicating what table and field each of your search criterias point to then that would help.
|
|
|
|
|
10-09-2007, 11:05 AM
|
#20 (permalink)
|
|
The Prestige
Join Date: Sep 2007
Location: Sweden, Stockholm
Posts: 1,080
Thanks: 115
|
Code:
CREATE TABLE `accounts` (
`id` INT NOT NULL AUTO_INCREMENT,
`password` VARCHAR(255) NOT NULL/* VARCHAR(32) for MD5*/,
`type` INT NOT NULL DEFAULT 1,
`premdays` INT NOT NULL DEFAULT 0,
`lastday` INT UNSIGNED NOT NULL DEFAULT 0,
`key` VARCHAR(20) NOT NULL DEFAULT '0',
`email` VARCHAR(255) NOT NULL DEFAULT '',
`blocked` TINYINT(1) NOT NULL DEFAULT FALSE,
`warnings` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB;
INSERT INTO `accounts` VALUES (1, '1', 1, 65535, 0, '0', '', 0, 0);
CREATE TABLE `players` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`group_id` INT NOT NULL DEFAULT 1,
`account_id` INT NOT NULL DEFAULT 0,
`level` INT NOT NULL DEFAULT 1,
`vocation` INT NOT NULL DEFAULT 0,
`health` INT NOT NULL DEFAULT 150,
`healthmax` INT NOT NULL DEFAULT 150,
`experience` INT NOT NULL DEFAULT 0,
`lookbody` INT NOT NULL DEFAULT 136,
`lookfeet` INT NOT NULL DEFAULT 0,
`lookhead` INT NOT NULL DEFAULT 0,
`looklegs` INT NOT NULL DEFAULT 0,
`looktype` INT NOT NULL DEFAULT 0,
`lookaddons` INT NOT NULL DEFAULT 0,
`maglevel` INT NOT NULL DEFAULT 0,
`mana` INT NOT NULL DEFAULT 0,
`manamax` INT NOT NULL DEFAULT 0,
`manaspent` INT NOT NULL DEFAULT 0,
`soul` INT UNSIGNED NOT NULL DEFAULT 0,
`town_id` INT NOT NULL DEFAULT 0,
`posx` INT NOT NULL DEFAULT 0,
`posy` INT NOT NULL DEFAULT 0,
`posz` INT NOT NULL DEFAULT 0,
`conditions` blob NOT NULL,
`cap` INT NOT NULL DEFAULT 0,
`sex` INT NOT NULL DEFAULT 0,
`lastlogin` INT UNSIGNED NOT NULL DEFAULT 0,
`lastip` INT UNSIGNED NOT NULL DEFAULT 0,
`save` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
`redskull` TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
`redskulltime` INT NOT NULL DEFAULT 0,
`rank_id` INT NOT NULL DEFAULT 0,
`guildnick` VARCHAR(255) NOT NULL DEFAULT '',
`lastlogout` INT UNSIGNED NOT NULL DEFAULT 0,
`blessings` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
`direction` INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY (`name`),
FOREIGN KEY (`account_id`) REFERENCES `accounts`(`id`) ON DELETE CASCADE
)
ENGINE = InnoDB;
INSERT INTO `players` VALUES (1, 'Account Manager', 1, 1, 1, 0, 150, 150, 0, 0, 0, 0, 0, 110, 0, 0, 0, 0, 0, 0, 0, 50, 50, 7, '', 400, 0, 0, 0, 0, 0, 0, 0, '', 0, 0, 0);
CREATE TABLE `bans` (
`type` INT NOT NULL COMMENT 'this field defines if its ip, account, player, or any else ban',
`ip` INT UNSIGNED NOT NULL DEFAULT 0,
`mask` INT UNSIGNED NOT NULL DEFAULT 4294967295,
`player` INT UNSIGNED NOT NULL DEFAULT 0,
`account` INT UNSIGNED NOT NULL DEFAULT 0,
`time` INT UNSIGNED NOT NULL DEFAULT 0,
`reason_id` INT NOT NULL DEFAULT 0,
`action_id` INT NOT NULL DEFAULT 0,
`comment` VARCHAR(60) NOT NULL DEFAULT '',
`banned_by` INT UNSIGNED NOT NULL DEFAULT 0
)
ENGINE = InnoDB;
CREATE TABLE `groups` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL COMMENT 'group name',
`flags` BIGINT UNSIGNED NOT NULL DEFAULT 0,
`access` INT NOT NULL,
`maxdepotitems` INT NOT NULL,
`maxviplist` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
INSERT INTO `groups` VALUES (2, 'Gamemaster', 137438953471, 1, 0, 0);
INSERT INTO `groups` VALUES (1, 'Player', 0, 0, 0, 0);
CREATE TABLE `guilds` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL COMMENT 'guild name - nothing else needed here',
`ownerid` INT NOT NULL,
`creationdata` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
CREATE TABLE `guild_invites` (
`player_id` INT UNSIGNED NOT NULL DEFAULT 0,
`guild_id` INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE = InnoDB;
CREATE TABLE `guild_ranks` (
`id` INT NOT NULL AUTO_INCREMENT,
`guild_id` INT NOT NULL COMMENT 'guild',
`name` VARCHAR(255) NOT NULL COMMENT 'rank name',
`level` INT NOT NULL COMMENT 'rank level - leader, vice, member, maybe something else',
PRIMARY KEY (`id`),
FOREIGN KEY (`guild_id`) REFERENCES `guilds`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE `house_lists` (
`house_id` INT NOT NULL,
`listid` INT NOT NULL,
`list` TEXT NOT NULL
) ENGINE = InnoDB;
CREATE TABLE `houses` (
`id` INT NOT NULL AUTO_INCREMENT,
`owner` INT NOT NULL,
`paid` INT UNSIGNED NOT NULL DEFAULT 0,
`warnings` TEXT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
CREATE TABLE `player_depotitems` (
`player_id` INT NOT NULL,
`depotid` INT NOT NULL DEFAULT 0,
`sid` INT NOT NULL COMMENT 'any given range eg 0-100 will be reserved for depot lockers and all > 100 will be then normal items inside depots',
`pid` INT NOT NULL DEFAULT 0,
`itemtype` INT NOT NULL,
`count` INT NOT NULL DEFAULT 0,
`attributes` BLOB,
`writer` TEXT NOT NULL DEFAULT '',
`date` INT UNSIGNED NOT NULL DEFAULT '0',
FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
KEY (`player_id`, `depotid`)
) ENGINE = InnoDB;
CREATE TABLE `player_items` (
`player_id` INT NOT NULL DEFAULT 0,
`pid` INT NOT NULL DEFAULT 0,
`sid` INT NOT NULL DEFAULT 0,
`itemtype` INT NOT NULL DEFAULT 0,
`count` INT NOT NULL DEFAULT 0,
`attributes` text NOT NULL,
`writer` text NOT NULL,
`date` INT UNSIGNED NOT NULL DEFAULT 0,
FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE `player_skills` (
`player_id` INT NOT NULL DEFAULT 0,
`skillid` tinyint(4) NOT NULL DEFAULT 0,
`value` INT UNSIGNED NOT NULL DEFAULT 0,
`count` INT UNSIGNED NOT NULL DEFAULT 0,
FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE `player_spells` (
`player_id` INT NOT NULL,
`name` VARCHAR(255) NOT NULL,
FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE `player_storage` (
`player_id` INT NOT NULL DEFAULT 0,
`key` INT UNSIGNED NOT NULL DEFAULT 0,
`value` INT NOT NULL DEFAULT 0,
FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE `player_viplist` (
`player_id` INT NOT NULL COMMENT 'id of player whose viplist entry it is',
`vip_id` INT NOT NULL COMMENT 'id of target player of viplist entry',
FOREIGN KEY (`player_id`) REFERENCES `players`(`id`) ON DELETE CASCADE,
FOREIGN KEY (`vip_id`) REFERENCES `players`(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE `tile_items` (
`tile_id` INT NOT NULL,
`sid` INT NOT NULL,
`pid` INT NOT NULL DEFAULT 0,
`itemtype` INT NOT NULL,
`count` INT NOT NULL DEFAULT 0,
`attributes` BLOB
) ENGINE = InnoDB;
CREATE TABLE `tiles` (
`id` INT NOT NULL AUTO_INCREMENT,
`x` INT NOT NULL,
`y` INT NOT NULL,
`z` INT NOT NULL,
PRIMARY KEY(`id`)
) ENGINE = InnoDB;
There's the db structure. |
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
Join the friendly bunch on IRC...
