# mySQL 'dictionary' questions...

This topic is 2641 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

I'm working on a 'word' game where users can submit words for points, and I'm trying to think of how to create the database, award points etc.

I was hoping someone with experience in this sort of situation could help lead me in the right direction.

To start out, would I want to break up the entire dictionary of [font=sans-serif][size=2]267,751 words into different tables, perhaps 1 per letter? Or just stick with the one table?[/font]
[font=sans-serif][size=2]
[/font]
[font=sans-serif][size=2]Also, I was wondering what sort of 'hacker proof' type stuff I should plan ahead of time for, to save myself the headache of having to recreate the table(s).[/font]
[font=sans-serif][size=2]
[/font]
[font="sans-serif"]I know about using printf, but what else should I be worried about?[/font]
[font="sans-serif"]
[/font]
[font="sans-serif"]The basic game play will go something like... user creates a word, for example 'Denim'. I first want to verify the word is in the dictionary, and if it is, reward the user with some points.[/font]
[font="sans-serif"]
[/font]
[font="sans-serif"]At the end of the game, the user can submit their score to the 'high scores' lists, etc.[/font]
[font="sans-serif"]
[/font]
[font="sans-serif"]A couple issues I will need some ideas to surmount... How to keep the running total on the server? I guess I could create a session, then keep the tally that way. Is there a better option for this?[/font]
[font="sans-serif"]
[/font]
[font="sans-serif"]Seeing that each 'word' is unique, should I use the actual word as the KEY in the database? Is there anything 'wrong' or 'bad' about this? This might actually be effective on keeping the size of the table down, the key == the points awarded for the word... just a thought.[/font]
[font="sans-serif"]
[/font]
[font="sans-serif"]I'd really like some good feedback and ideas on this. I plan to distribute to pc / portables.[/font]
[font="sans-serif"]
[/font]

##### Share on other sites

To start out, would I want to break up the entire dictionary of [font="sans-serif"]267,751 words into different tables, perhaps 1 per letter? Or just stick with the one table?[/font]

1. no
2. yes
What is your intention in splitting it up ?

[font="sans-serif"]Seeing that each 'word' is unique, should I use the actual word as the KEY in the database? Is there anything 'wrong' or 'bad' about this? This might actually be effective on keeping the size of the table down, the key == the points awarded for the word... just a thought.[/font]

Always use artificial numeric ids as technical keys and create an additional uniqe key for the word column in this case.

##### Share on other sites
So something like...

id == int, unique
word == char(15) unique
points == point value

As far as separate tables, I thought perhaps the 'look up' of the word would be faster, but I guess I'm not really doing 'searches' its more of a direct look up, I didn't think about that.

Any other advice? thanks for those tips so far!

##### Share on other sites

So something like...

id == int, unique
word == char(15) unique
points == point value

As far as separate tables, I thought perhaps the 'look up' of the word would be faster, but I guess I'm not really doing 'searches' its more of a direct look up, I didn't think about that.

Any other advice? thanks for those tips so far!

If this is just static data look-up, why do you need MySQL? You could build a simple in-RAM service using C++ or Python or whatever that hard-codes the list, that could look it up 100x faster that way... Or you could use Redis; say "word:<word>" is the key, and point total is the value.
Anyway, if you want to do this with MySQL, it will probably work fine up to a point. Once you run out of performance in the MySQL server, you can start looking for better options -- either horizontal sharding, or key/value stores, or in-RAM service, or whatever.

Regarding keeping the "tally," that's no different from any other web application that needs state. You often need a user session, and the ability to accept requests that modify the session state. Popular ways of storing sessions include using ephemeral key/value stores like memcached, or on-disk key/value stores like memcachedb or couchdb or mongodb or Redis, or using a database like MySQL. Just make sure to clean out old, expired sessions after a while. Also, you may want to google "firesheep" to see an interesting session ID attack that you have to use HTTPS protocol to guard against.

##### Share on other sites
I'm pretty new to mySQL, and PHP, so I don't even know what an, 'in-RAM' service is. I also don't know if they even allow it on my server.

Back to the mySQL stuff though, I am almost done setting up the words and point values, but now I am having a hard time deciding how to handle the 'game board'. I had thought I should keep the actual mechanics on the server end, to prevent someone from just submitting a 'word' and getting the points.

The basic game board layout is a 5x5 grid. Each slot is assigned a random 'letter', and is indexed from 1-25.

On the client, they will be able to select slots adjacent to the previous slot, in order to form words. So I need a way to query the starting slot, then each slot after, to make sure they are truly adjacent. Then, if they all are, I look up the 'word' they form, and give out the points.

I would like some ideas on how to do this, but here is what I have so far...

SLOT:
id (1-25) for the 5x5 grid index
ltr_id (1-26) for each letter in the alphabet
n slot north of this one in the grid
ne slot north east of this one in the grid
e slot east of this one in the grid
se slot south east of this one in the grid
s slot south of this one in the grid
sw slot south west of this one in the grid
w slot west of this one in the grid
nw slot north west of this one in the grid
star (true / false) star slot doubles the word points

So the idea is, the user clicks the 'start' slot this is recorded as 'start'. Then as they click the next adjacent slot, it records the direction, and the slot number. After they are done, they click 'send', and it sends the list to the php script.

In the php script, I will somehow break the directions / slots into an array or something, and then I will check for each 'slot', check the direction, and see if the associated index is correct. If it is, I move to the next one, then the next one, and so on until the word is finished. Once it completes successfully, I will look up the word and reward the points.

So as an example, lets say I start on grid index 7. Now I click the slot 'north' of it, which would be 2. Next I click 'east' of 2, which is 3. Then 'south east', which is 8.

On the client I would send... 'strt=7&adj=n|2|e|3|se|8'

So in php, I would check the strt slot, look at the 'n' value, see if it is 2. if it is, look up the '2' slot, see if the 'e' value is 3. If it is, look up the '3' slot, and see if the 'se' value is 8. Finally, if it all returns properly, assign the points for the word to the users score.

As I said above, I'm still pretty new to PHP / mySQL, so I don't know if this is the best way to do this or not. Id appreciate any better methods for something like this. I basically don't want the user to just be able to hack it and send some word that isn't possible inside of the games rules.

##### Share on other sites

[color=#1C2837][size=2] I also don't know if they even allow it on my server.

If you're using a shared web host, you will *have* to make a number of decisions that nobody who builds a game for profit would make.

I recommend still keeping the game state in session state of some sort on the server, but it probably has to be in a MySQL database. Encode it to JSON, or use PHP serialization, and store it into a TEXT or BLOB field with a key that's your unique game/session ID.
Each time a player request comes in, update the game state if the request is OK, and send back the new state of the game to the players.
Keep a timestamp for each game state, and update it each time the data is updated.
Use a cron or other repeating function to delete game state data older than, say, 1 hour.

##### Share on other sites
Well the engine I use (DX Studio) allows 'postToURL' from within the engine, so it won't actually be using a web page.

Basically the user will run the game client, and as they click the letters to form words, it will do all this locally. Then when they actually 'submit' a word, it will send it as a postToURL, where I can send a string to a php file on my web site.

Once there, I plan to use the system I described above to traverse the word 'path', to make sure it is a valid sequence of letters.

I have most of the back end stuff done. I worked on it most of the day, but I am thinking of making the grid 6x6 instead of 5x5 (my wifes opinion of course).

Here is a screen shot of the grid.

I use a daily 'seed' to get 'random' letters, this way all users have the same grid of letters:
 <?php session_start(); require_once "config.php"; require_once "opendb.php"; srand(floor(time()/86400)); $letter_count = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);$letter_max = array(9, 2, 2, 4, 12, 2, 3, 2, 9, 1, 1, 4, 2, 6, 8, 2, 1, 6, 4, 6, 4, 2, 2, 1, 2, 1); $grid = array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);$g_count = count($grid);$l_count = count($letter_count); for($i=0; $i <$g_count; $i++){$ltr_id = -1; do{ $max = 0;$rnd_int = rand()%98; $min= 1; for($j = 0; $j <$l_count; $j++){$max = (($min-1)+$letter_max[$j]); if($rnd_int <= $max){ if($letter_count[$j] <$letter_max[$j]){$letter_count[$j]+=1;$ltr_id = ($j+1);$j = $l_count; } else{$j = $l_count; } } else{$min = $max; } } } while($ltr_id == -1); $grid[$i] = $ltr_id; } for($i=0; $i <$g_count; $i++){ echo$grid[\$i]."|"; } require_once "closedb.php"; ?> 

So far it seems ok. The echo is just for my testing purposes. In the final script, I plan to have it actually update the 'grid' table. Then when users connect, they download it from mySQL.

Any more advice is welcome. For now, this is mainly a 'proof of concept' type thing. If / when I move it to 'commercial', I will keep a list in memory, and just handle it as you suggested.

##### Share on other sites

Basically the user will run the game client, and as they click the letters to form words, it will do all this locally. Then when they actually 'submit' a word, it will send it as a postToURL, where I can send a string to a php file on my web site.

Once there, I plan to use the system I described above to traverse the word 'path', to make sure it is a valid sequence of letters.

When you do things on the client, it will be trivially spoofable by the user, as you can generate the URL in question using curl or wget or whatever.
However, perhaps that doesn't matter in this case.

##### Share on other sites
I figured out a few things today...

For one, I generated a char(36) string for today+100 years into the future (optimistic i know). When the user requests the daily grid, it just checks the current UTC date, and returns the string of characters. Then on my client, I parse them into the grid buttons.

Second, I finally thought of a way to handle the 'words'. I created a 'salt' and then did sha1(word+salt), and I'm about to upload it to the word table. This way, only words created 'properly' will have the salt, and it will just check sha1 agains sha1.

This will make it much harder for the casual gamer to hack IMO, and makes it much easier on my end.

I also made the grid 6x6.

Thanks for helping me thing of this stuff hplus0603

1. 1
2. 2
Rutin
19
3. 3
khawk
18
4. 4
A4L
14
5. 5

• 12
• 16
• 26
• 10
• 44
• ### Forum Statistics

• Total Topics
633768
• Total Posts
3013736
×