Need Quick MySQL Advice

JonD · 18 Oct 2006 at 13:48

I been asked to design a database and Im not sure what datatype to use for a price field and a date field.

Should the price by a int, float or a currency. (If currency how *** u process it with PHP?)

For a date should I be using a unix timestamp and storing it as int(10) or should I be using the DATETIME MySQL datatype? (Again how would I process it via PHP?)

Also a user can sign up to the site, whats the best datatype to use for an md5 encryption? char(32)?

Thx
JD

HTMLHugo · 18 Oct 2006 at 13:51

i would use a float (there is a money type within sql but havent had much experience with it, if its just for storage then its not an issue but if you want to do some serious maths id check a bit further) for price and datetime for date & time oh and a varchar for the final one

cant help with the php though never done it

JonD said:
I been asked to design a database and Im not sure what datatype to use for a price field and a date field.

Should the price by a int, float or a currency. (If currency how *** u process it with PHP?)

For a date should I be using a unix timestamp and storing it as int(10) or should I be using the DATETIME MySQL datatype? (Again how would I process it via PHP?)

Also a user can sign up to the site, whats the best datatype to use for an md5 encryption? char(32)?

Thx
JD

JonD · 18 Oct 2006 at 13:57

why varchar(32) over char(32)? Isnt an md5 encryption always 32 characters long?

HTMLHugo · 18 Oct 2006 at 14:00

your right of course my bad char

JonD said:
why varchar(32) over char(32)? Isnt an md5 encryption always 32 characters long?

paulsheff · 18 Oct 2006 at 14:13

My host said I should use double(10,2) for prices, dunno why.

HTMLHugo · 18 Oct 2006 at 14:18

yes just checked with our resident sql guru and he said "I tend to use decimal(10,2). Float gives excellent decimal precision, but can give you some strange calculation results when it rounds the figures off, money expects to see a currency sign in the data (e.g. £100) and a varchar would need converting to something else if you need to do any calculations with it…"

just so everybody is now aware of what to use and why to use it lol

paulsheff said:
My host said I should use double(10,2) for prices, dunno why.