   Vairous php and mySQL game questions
  My other post about php/mySQL seemed to morph into a Q & A session about everything I could think of, so I figured I should start a proper post more suited to that purpose.First of all, I am following this tutorial series pretty closely.  This is my first 'online' game attempt and I am not very familiar with php or mySQL, but I am making steady progress.Ok so the first question off the top of my head.  What is the best way to manage player and monster stats?According to the tutorial I am reading, I create a 'stats' table, which just houses the display_name and the short_name of the stat.  Also each stat is assigned the int id.Then as I create a new 'monster', I add the monster to the 'monsters' table.  This table just stores the monster name and the int id.Finally there is a third table, the 'monster_stats'.  For each monster in the monsters table, I add a stat from the stats table and then a value.So as a quick example:stats:id | display_name | short_name1 | Physical Defense | pdef2 | Magic Defense | mdefmonsters:id | name1 | Zombiemonster_stats:id | monster_id | value1 | 1 | 52 | 1 | 5So from this setup, you can see that the Zombie has 5 pdef and 5 mdef.So is this sort of system ok?  Is it more complex than I need for my first time around?  Is it pretty standard?   
  Why not store your monster data in one table? Normalizing your data like this means you have to perform two JOINs across three tables. It's fine if you want to use PHP and MySQL for this, but I don't think MySQL lends itself particularly well to this problem.   ;  I think your question falls into the category of design rather than implementation. You can do it any way you want even store everything in 1 table or every table in a separate database( not practical), but really it is just a matter of how you see it.;  Hey thanks for the response guys!  Ok so there is no real 'best practice' that everyone knows about (but of course I don't because I'm a noob)?Thanks for the feedback guys.  I'll stick with whatever the tutorial says for now, until I get my own feel for the grand scheme of the system.For the user information, I split it across a few tables:account:idusernamepasswordis_adminemaildate_createdactiveget_newssession_tokenlast_loginuser_data:idlast_login(for this character)namelvlacnt_id(belongs to account->id)ntr_zone(zone id to enter when character logs in)avatar(mesh to load for this character)cPosX(x position)cPosY(y position)cPosZ(z position)cRotY(y rotation)user_items// not fleshed out yetuser_stats// not fleshed out yetDoes this look about right?  I appreciate all your help and feedback!  Is this the wrong forum for this topic?BUnzaga   ;Quote:Original post by BUnzagaOk so there is no real 'best practice' that everyone knows about (but of course I don't because I'm a noob)?Best practice would be to go with your original idea, of breaking everything out into separate tables (normalizing your data) and JOINing them when necessary; this will allow for faster updates of your data, and help prevent problems with faulty updates (you only have to update in one place rather than in many places because you have duplicated data everywhere). Don't denormalize (put things back together in one wider table) unless it is for performance reasons, after you have everything working.However, I don't use MySQL so drr may have a point. I will refrain from getting religious about your choice of RDBMS.   ;  Either one works, as both have their advantages. Normalized like you have it, it is very easy to add and remove stats completely since your query to read it won't ever really have to change (it'd just end up with more rows). Denormalized, it requires a slight alteration to the query to read the new rows. Though I almost always go with the denormalized version myself (in this scenario) because:1. Stats don't change very often in comparison to most everything else. If you design the game before programming it, you may only need to change it once or twice, if at all.2. The denormalized version has a very poor key-to-property size ratio with a very high rows-per-foreign-key count. That is, each character has a lot of stats, and each stat is only 2 or 4 bytes but still requires 2 keys that together are probably 6 bytes.3. If you are updating a character, you want to update everything at once. That is, everything in the row. You may even update values that haven't changed just to avoid the overhead and time required to make a decent value change detection system. Then again, this does vary a bit depending on how you update persistent data.4. No need to JOIN.5. You get to assign a different value type for each stat if you want. This is a huge bonus for me. I don't want my level, which will never pass, say, 60, to have to take up 2 bytes. Or have every stat take up 4 bytes just because I thought it'd be nice to make sure people could have over 65535 HP.But you are correct, there is no real "best practice". The Guild Wars guys decided to just put the whole character into a single column as a giant BLOB if I remember correctly. There are also plenty of people out there who kick and scream when they even smell data being denormalized like this.Keep in mind that if you are making a game editor or something of that sort where you want people to be able to easily modify the stats, it is a LOT easier for your self to use normalized stats. Otherwise, adding/removing stats requires an editor that is a bit more intelligent and can handle performing queries with dynamic schemas. But it is very possible, and you really can query MySQL for just about any data on a table. You can even prefix your columns with something like "stat_" so to grab a query of all the stat columns, you do something like:mysql> SELECT `column_name` FROM information_schema.COLUMNS WHERE `table_name` = 'npc_character' AND `column_name` LIKE 'stat\_%' ORDER BY `column_name`;+--------------+| column_name  |+--------------+| stat_agi|| stat_defence || stat_int|| stat_maxhit  || stat_maxhp   || stat_maxmp   || stat_minhit  || stat_str|+--------------+8 rows in setStraight out of my engine. :)   ;  Are you going to change the available stats all that often? If not, just put them all in columns.create table monster(  id int not null autoincrement primary key,   name varchar not null unique,   str int not null,   dex int not null,   mind int not null,   pow int not null,   hp int not null,   loot_id int,   constraint foreign key(loot_id)references loot(id));Note that, this way, there's no way of forgetting to assign a stat to a monster. With the super-normalized triple table, you'd have to write special queries to find the ids of monster classes that don't have all the necessary stats assigned...If you really need a super-normalized table for some reason, you can still keep the id/name mapping in a map/array in your software, and hard-code the id used in the tables. That way, you can add stats without creating any "alter table" statements (although altering your tables isn't a big deal in tables with less than 10,000 rows or so).If you really need to put the stats themselves in a table and use foreign keys, you could just as well use the short name as a foreign key: it's unique, and it's short. As a bonus, it's human-readable! Which table dump would be easier to debug?Table 1:1  1  101  2  131  3  52  1  172  3  10Table 2:orc dex 10orc hp  13orc int 5rat dex 17rat int 10Note that you probably won't be able to measure any performance difference between those two schemas (int keys vs short string keys), even with tens of thousands of rows, because databases are generally bounded by disk seek, not the difference between integer compare and string compare.In my opinion, the extra indirection of "entity, property, value" that allows you to define new kinds of properties comes from the "super flexible" content management systems of the world, and only make sense when you don't know up front what the schema will be, because different users will have different schemas. However, you lose a lot of the power of a relational database if you do that, because you can't easily do things like enforcing presence for specific properties. Plus it runs slower, although for any normal RPG it wouldn't really mater.For your game, however, there is one user: your game. You know the schema. There's no reason to over-normalize, and many good reasons not to!   ;  Thanks guys, this is the exact information I was trying to get.  I think the creator of the tutorials I was following was trying to make an 'all inclusive' type tutorial which someone could follow and use, no matter what game they were making.Some of you ask 'why are you doing it like this', etc and the answer is, it is because I don't know better or any other way, so I am so glad you are taking the time to explain it to me and give me working examples like you are.I really like the idea of using the short_name for the foreign key.  Would I still use the int auto inc for the indexing, or could I just drop that and use the short_name if I make sure it is unique?Also, I have been using $_SESSION for storing user data, is there an easier way to write something like: '".$_SESSION['whatever']."'???  Is that why people use %s and mysql_real_escape_string ?Here is an example of when the user chooses a character and logs into the game:// above here I require_once config.php and loginCheck.php// for the database connection and session token / account id check// and I have a switch for... $_POST['cmd']case "ntr": // short for enter (game)if(isset($_POST['chrname'])){  $query = "SELECT name, lvl, ntr_zid, avatar, cPosX, cPosY, cPosZ, cRotY FROM user_data WHERE acnt_id = '".$_SESSION['acnt_id']."' AND name = '".$_POST['chrname']."' LIMIT 1;";  $result = mysql_query($query);    if($result){ $_SESSION['user'] = mysql_fetch_assoc($result); echo "<user_data name='".$_SESSION['user']['name']."' lvl='".$_SESSION['user']['lvl']."' zone='".$_SESSION['user']['ntr_zid']."' avatar='".$_SESSION['user']['avatar']."' cPosX='".$_SESSION['user']['cPosX']."' cPosY='".$_SESSION['user']['cPosY']."' cPosZ='".$_SESSION['user']['cPosZ']."' cRotY='".$_SESSION['user']['cRotY']."' />";    }}break;Does it look ok?  Does anything look alarmingly wrong?  In the engine I am using, I am parsing the response string into an XML file and loading the correct mesh/pos/rot from here.One more question.  I noticed that if a user create a character with the same name on the same account, and then try to delete one, there is no guarantee which one will remain and which will delete.  I realize that a lot of games will force users to create unique character names.  Is this something I should do to resolve this problem, or should I assign a unique token to each character and pass this back and forth when the user creates / deletes a character?Again, I appreciate the responses, opinions and feedback.  As this is my first online game, I am just learning as I go and can use any advice you are willing to offer.BUnzaga[Edited by - hplus0603 on January 14, 2010 11:23:59 AM];Quote:I think the creator of the tutorials I was following was trying to make an 'all inclusive' type tutorialOr the reason might be that the author of the tutorial hasn't learned better ways? I find that most tutorials are written as a way for the author to learn things for him/herself, which generally does not tend to expose best practices. I have no idea about the specific tutorial you're following, though.Quote:mysql_real_escape_stringThe reason to use mysql_real_escape_string or similar is to avoid SQL injection attacks. If you have a registration form:<form method='post'><input type='hidden' name='action' value='register'/>Name: <input type='text' name='name' /><br/>Password: <input type='password' name='password1' /><br/>Confirm: <input type='password' name='password2' /><br/></form>If you now handled that like:<?php  require_once("common.php");  if ($_POST['action'] == 'register') {    if ($_POST['password1'] != $_POST['password2']) { error("The two passwords entered were not the same.");    }    // WARNING! BADNESS ON THE FOLLOWING LINE!    $r = mysql_query("INSERT INTO users(name,password) VALUES('$_POST[name]', '$_POST[password1]')");  }I would probably try to register a name like ','x');DROP TABLE users;--I'll leave it to you to figure out what would happen if I did :-)You have to quote the input data that a user may provide. Always. Doing browser-side input format checking is not good enough, because users can (and will) construct form posts on their own (or just turn off &#106avascript).<br><br>And if your tutorial didn't tell you about this as soon as it introduced the concept of mysql_query(), then I'd suggest finding another tutorial :-)<br>   ;Quote:Original post by hplus0603','x');DROP TABLE users;--I'll leave it to you to figure out what would happen if I did :-)You have to quote the input data that a user may provide. Always. Doing browser-side input format checking is not good enough, because users can (and will) construct form posts on their own (or just turn off &#106avascript).And if your tutorial didn't tell you about this as soon as it introduced the concept of mysql_query(), then I'd suggest finding another tutorial :-)Actually this particular attack won't work because PHP doesn't accept multiple queries in one mysql_query call. But hplus's point is still valid, an attacker may use other attacks with unsanitized input. In your code snippet where you select the user info based on the POST of the character name, the user could supply a crafted name that returns a different user.   