Storing numbers in a database, separated by commas

[slyte33]

What I'm trying to achieve is:

 

I create a new announcement for my game. When players login the game they are right on the announcement page. The new announcements I want displayed in red, and once they've read it, the announcement would go back to normal color.

 

Each player is specified by an ID. I want the database to update a TEXT with their ID.

Like so:

 

Player 1 reads it

Player 2 doesn't

Player 3 reads it

 

so in the database the the "has_read" value would update the type "TEXT" to this:

1,3

 

After it's stored, how would I display it correctly, for example, (completely incorrect since the players ID isn't 1,3)

 

if (announcements->has_read == players_id)

{

display red announcement

}else{

display normal since they've read it

}

 

 

Any help would be greatly appreciated, I hope I made this easy to understand

[Jessica]

Google "normalization". You don't want to store the data as a delimited list.

[slyte33]

Google "normalization". You don't want to store the data as a delimited list.

 

I'm not too sure what that means :X

If it means don't store it with a comma, I could do (1)(3) as my values being set.

But if it means not to store it the way I wanted, how could I have each individual player be stored as if they read the announcement or not?

[stijn0713]

i would retrieve the has_read string and explode() it. Then you can grab a player and loop through the array to see if his id is stored in it.

[Christian F.]

Slyte: It means exactly what she said: "Google (database) normalization". It will give you lots and lots of guides on how to properly structure your data, to accomplish what you want.

 

stijn: I think you might want to do the same. Will give you lots of useful information.

Edited January 15, 2013 by Christian F.

[slyte33]

Ok I did figure out a way to fetch the data and update the database with each players ID that viewed the new announcement.

Now how would I check to see if 1 player's ID has been stored?

[Jessica]

By normalizing your data. Seriously. I'm not saying it for the fun of it, the problems you are running into stem from bad design. Fix it now, it'll be easier to fix it now than later.

[JonnoTheDev]

Let me elaborate. You should not store delimited ids in a text field as you will end up with a mass of issues down the line. To normalize your database you need to do the following (I am guessing your table / field names)

 

players

=======

player_id (int)

name (vc 50)

 

announcements

=============

an_id (int)

content (vc 255)

 

players_to_announcements

======================

id (int)

player_id (int)

an_id (int)

date_read (datetime)

 

So if you have 3 players in the players db table with ids 1, 2, and 3 and you have an announcement in the announcements table with an id of lets say 456, if a player reads an announcement you record this by storing a record in the players_to_announcements table. If player id 2 & 3 have read the announcement the data will look as follows:

 

1 | 2 | 456 | 2013-01-01 01:00:00

2 | 3 | 456 | 2013-01-01 02:00:00

 

You can then easily query this data to find what players have read each announcement