Solved

MySQL Change Datatype of Cell Based on another value

Posted on 2011-03-07
2
335 Views
Last Modified: 2012-05-11
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
Comment
Question by:hungoveragain
2 Comments
 
LVL 10

Assisted Solution

by:honestman31
honestman31 earned 250 total points
ID: 35055521
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
 
LVL 27

Accepted Solution

by:
Lukasz Chmielewski earned 250 total points
ID: 35055719
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MySQL Grouping 2 53
category table 2 35
Using coldfusion <cfexecute> to restore mysql database 7 64
Complex MySQL Query 2 38
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question