Welcome to WebmasterWorld Guest from 54.159.50.111

Forum Moderators: open

Message Too Old, No Replies

Recommended MySQL data type for screen resolutions?

     
2:12 am on Mar 11, 2010 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member jab_creations is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 26, 2004
posts: 3145
votes: 12


Does any one recommended MySQL data type for screen resolutions besides varchar? Basically entries would be no larger then 2560x1600 or nine characters. I could use INT however removing the separator would require processing the number in PHP later unless someone has something I'm not aware of to recommend?

- John
2:26 am on Mar 11, 2010 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4842
votes: 1


You could create a MySQL function that could do the stuff you thought PHP would have to do.

Or two small int's would save you creating the function (Googling shows an pre-written function for splitting a string)

Otherwise, you could use an ENUM field, however that would require you to know a list of existing screen resolutions and would limit you to 255 combinations.

I'd go with the INT and use 16 bits for each dimension.

Selecting data...
SELECT res>>16 AS x_dimension,res & 65535 AS y_dimension FROM table

Inserting 1280x800 resolution
INSERT INTO table (res) VALUES ((1280<<16)+800)

If you are doing lots of SELECTs then it may be more efficient just to have two SMALLINT columns, to spare the processing on the INT column.
3:13 am on Mar 11, 2010 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member jab_creations is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 26, 2004
posts: 3145
votes: 12


Thanks for your reply. I setup a column and tested out the code you posted successfully! :)

Essentially I've created an authentication log to deal with any potential social related issues so I will be seeing let's say about 100 entries per person per incident when there is an incident as the admin, no one else will be accessing this MySQL in any way.

The largest concern I have (due to the Oracle envelopment...err I mean acquisition of Sun) is being able to port to a different flavor of SQL. I've only been working with MySQL for a couple years so my question is simply this: would this require much if any significant changes if/when I switched to a different SQL database such as PostgreSQL?

- John
3:23 am on Mar 11, 2010 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4842
votes: 1


I've used PostgreSQL sparingly, but AFAIK you'd be fine, as they have the INT datatype and >> and << are just basic mathematical functions.
3:25 am on Mar 11, 2010 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member jab_creations is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 26, 2004
posts:3145
votes: 12


I figured as much, I just want to avoid a potentially nasty scenario in say ten years. Thanks again! :)

- John
3:31 am on Mar 11, 2010 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member jab_creations is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 26, 2004
posts:3145
votes: 12


Oh a quick related question; with 2560x1600 I get 167773760, so wouldn't it be best to set the column's length to 9?

- John
3:48 am on Mar 11, 2010 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4842
votes: 1


An INT column uses 4 bytes, that's 2 to the power of 32.... so you don't have to worry about column length.

Since you're using the column to store two discreet values and halving it, you can store up to 2 to the power of 16 for each value, which is a maximum of 65535.

Maybe in ten years screen resolutions will be that high, perhaps not...
5:09 am on Mar 11, 2010 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member jab_creations is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 26, 2004
posts:3145
votes: 12


Okay so INT nine then? I presume if I had to increase the length from nine eventually that it wouldn't kill the database?

- John
5:27 pm on Mar 11, 2010 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4842
votes: 1


INT is fixed length, the interface you're using may ask for a length of INT, but it doesn't apply.

[dev.mysql.com...]