TalkPHP - Need your feedback

TalkPHP (http://www.talkphp.com/forums.php)

- Absolute Beginners (http://www.talkphp.com/absolute-beginners/)

- - Need your feedback (http://www.talkphp.com/absolute-beginners/1271-need-your-feedback.html)

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.

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.

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

Give your search function a different name. That, along with making the argument str instead of "string", should help.

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.

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.

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' ",

Quote:

Originally Posted by Karl (Post 2892)

Yea, but sorry, I don't know anything about Prototype JS ://

Quote:

Originally Posted by Salathe (Post 2895)

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>";
        
    } 
    
?>

Hmm I personally can't see the problem. Is the query for searching guilds returning any records?

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

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).

HAHAHA, I'm such stupid :P

Thank you salathe!! :D

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?

Are the different vocations stored in their own table in the database?

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..

You should sotore these vocations in a seperate table like Salathe has implied. You can then perform searches to your hearts content.

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 =//

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?

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.

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.