• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

MySQL Change Datatype of Cell Based on another value

I have a mysql database (obviously). Within that database I have a single table - 'settings' with columns 'SettingName' and 'SettingValue'. The individual setting values may be a number, text, date or whatever.

e.g.
SettingName - Setting Value
Name - 'Product A'
StartDate - '2011-03-20 00:00:00'
ID - 567
Cost - 12.34

There will only ever be a few settings (there are only a couple of dozen settings so the table will never go over that probably). Is there a way to enforce a datatype on SettingValue based on the value of SettingName? So the datatype is timestamp when SettingName = StartDate and datatype is int when SettingName = ID etc

I can change the datatype from one to another when I extract them using php but only if the value is incorrect (StartDate - 'Cheese') this may fall down.

Thanks

Mike
0
hungoveragain
Asked:
hungoveragain
2 Solutions
 
honestman31Commented:
There is no way to do that ,  as datatype  is per column and it will affect the whole column so you can not  make the datatype per cell
0
 
Lukasz ChmielewskiCommented:
True, but you can give them all a varchar or text type and add another column indicating the setting type or just use your only settingname to discover what type OF would you convert.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now