   [web] MySQL or PHP Sessions ?
  Hello to the community!    I have started making a web based game using PHP, MySQL and of course the known client side scripting languages for setting up the page and receiving content without refreshing the page.    I've come across a situation where i don't know what should be used - retrieving data from MySQL or storing the data from MySQL in php sessions when the user logs in and then proceed using the sessions (saving them on log out).    I have a table in my database for quests where every quest is described by ID, name, energy cost, experience gain, % of mastery etc. And I have another table which has columns for user id, quest id and completion of the quest.    My question is: "What should have better performance - storing the tables in php session variables or connect to the database and alter it's contents every single time the user does a quest?" I ask this because I've heard and read that MySQL should be used as less as possible to achieve a higher performance, but I don't know whether such a great deal of information for every single user who logs in won't slow down the server.    Suggestions and thoughts about probable solutions or ways to optimize the database structure are welcome. I hope that somebody who has had the same problem in the early stages of his/hers web game creation could provide information about the problem as well.
  I doubt you'd come close to seeing any limiting performance from either PHP's disk-based session system or MySQL querying, unless your game is somehow landing 100+ simultaneous hits on your server.That being said, make it easy and just run requests to the database. No matter what, you're still reading from the disk. Since you're not using a memory-based caching system like Memcached, and PHP's session system storing data in files on disk, the next best thing is using MySQL. Reads from MySQL should be incredibly fast, as you should be indexing all columns you use in WHERE and JOIN queries.MySQL would also scale better if your game grows in popularity.   ;Quote:Original post by Alex131I ask this because I've heard and read that MySQL should be used as less as possible to achieve a higher performanceI highly doubt that, database engines are designed for (among other things) fast data access.Furthermore, you'll run into the problem of synchronization. Now the state stored in your session doesn't correspond to the one in your database:- data might be lost when the session for some reason ends unexpectedly- you can't easily put data constraints or enforce relationships in a session objects- shared state requested from the database by another session might be outdated- having data in several places might make it more difficult to scale- etc.Not that I've never run into situations where database performance became a bottleneck even after all possible optimizations, but the scale of those systems probably aren't comparable with your game. At least not yet, but let's hope it becomes so popular it will. [smile]   ;  Thank you for the thoughts and solution. Seeing that I shouldn't use sessions for such information I would like to ask whether I should use them for something else apart from user authentication?   ;  Just a note on sessions. Make sure that there completely secure and you have no opportunities for a hacker to create an XSS cookie-stealer...   ;  Happen to have a good article on this subject?   ;  Ahhh.... Not handy. I'll find one and post a link up here.Just validate ALL input before processing it.Google Poison Cookie/session and XSS you'll find an article easy enough!!!   ;  I validate all input before processing it via &#106avascript for the users which support it and strip all illegal characters in my php code before I put things into my database so I think this is enough, isn't it (I'm considering adding captcha once I write the main things considering the game play)? Anyway I'll google the things you said :)   ;Quote:Original post by Alex131I validate all input before processing it via &#106avascript for the users which support it and strip all illegal characters in my php code before I put things into my database so I think this is enough, isn't it (I'm considering adding captcha once I write the main things considering the game play)?Client validation is nice because it enables you to report errors without making a request to the server first. From a security point of view, it adds very little. As you already mentioned, scripting can be circumvented or might be turned off completely.It sounds like the server side validation does the bare minimum, but it might just suffice in your case. It's impossible to point out security flaws without actually seeing the code.Captchas have nothing to do with security. At best they prevent bot or script driven automatic requests due to the required human intervention.   ;  &#106avascript is a disaster in terms of security.Supposing you had a guestbook that validated input via &#106avascript and I was a hacker who wanted to hack it. (All hypothetical:P)I'd just view source and then save it to my disk. I'd then re-open it, remove the &#106avascript validation script and put in my malicous content. It would send this malicous content back to your server with you thinking it's all nice and safe.Use server-side validation ONLY! Try the php htmlentities() function.Captcha is very useful. I had a site and it got spammed so bad in the couple of days I had no Captcha...   