• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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