Welcome to WebmasterWorld Guest from 54.226.32.234

Forum Moderators: open

Message Too Old, No Replies

Recommended MySQL data type for screen resolutions?

     

JAB Creations

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

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



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

brotherhood of LAN

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

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



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.

JAB Creations

3:13 am on Mar 11, 2010 (gmt 0)

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



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

brotherhood of LAN

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

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



I've used PostgreSQL sparingly, but AFAIK you'd be fine, as they have the INT datatype and >> and << are just basic mathematical functions.

JAB Creations

3:25 am on Mar 11, 2010 (gmt 0)

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



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

- John

JAB Creations

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

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



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

brotherhood of LAN

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

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



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...

JAB Creations

5:09 am on Mar 11, 2010 (gmt 0)

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



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

brotherhood of LAN

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

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



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

[dev.mysql.com...]
 

Featured Threads

Hot Threads This Week

Hot Threads This Month