using JSON

[Dayo]

i was thinking of using JSON to store the users game data (not login/personal data) in my database so the table would be set up like

id, password, email, data

any thoughts on this, Its for a game script im working on i want to keep the DB as small as i can

[Djkanna]

I'd say not using JSON in this way will be more efficient.

The database engine, is a *lot* faster and efficient, over PHP to handle your data.

Not to mention just imagine the trouble you'll have to go through just to do something as trivial as say get a users bank money, where the amount surpasses a certain amount.

//--First Query
//SELECT `userid`, `money` FROM `users` WHERE (`money` > 200 );


//--Second Query. (JSON)
//SELECT `userid`, `data` FROM `users`;

/* ----------------------------------------------
   Imagine:
       --First Query.
       $data = array ( `userid` => `money` );
       --Second Query.
       $data = array ( `userid` => `data` );
   ;
-----------------------------------------------*/

//Loop over dataset from first query.
foreach ( $data as $userid => $money ) {
   //Do something with the data.
}

//Loop over dataset from second query.
foreach ( $data as $userid => $d ) {
   //First check if you can do something with the data.
   $d = json_decode ( $d );
   if ( $d['money'] > 200 ) {
       //Then do something with data.
   }
}

[Dayo]

also ive just thought of another problem, ill just use 2/3 tables instead of 1. I just dont want the login database to end up like mccodes lol

[Someone]

Dont know how familiar you are with NWE. I find the users and stats table structure to be excellent.

Users table only have some basic details, like id,username,password,email,online

Then there is a table for user_stat_types and user_stats

user_stat_types has id, name, description

user_stats has user_id, stat_type, value

You can then add as many stats as you want without adding columns to the users table.

Retrieval is also quick, as it looks up using unique key number based indexes. The values are then set a session variable, so they are only retried from the database when logging in or changed.

This is a simplification, the tables has more columns that are useful in a game setting, but would be overkill if just making a simple system.

Edited January 30, 2013 by Someone

[C.B]

JSON won't be your best bet but you can just serialise an array of values and store it in the database, thats probably your best bet

[a_bertrand]

If you store the stats as JSON or using serialize of PHP or any other mean, sure you would maybe have some "quick" way to access the whole while loading a single column, the issue is that you cannot let the DB do any processing on it. Think for example, what if you want to make the "top 10" list? Well, no chances, you need to scan ALL the texts, parse, and then spit the best in an array. So basically that would be not only slow but tedious. So leave the DB do it's job, and store it in a smart way. You may of course take NWE free and have a look at how I approached the problem. It's not the only solution of course.