Solved

MySQL Change Datatype of Cell Based on another value

Posted on 2011-03-07
2
333 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Error 3 42
Install MySQL 5.6 and PHP on Centos Linux 6 119
mysql joining from the same table 6 42
UPDATE query not working in mysqli php 8 48
Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now