MySQL Forums
Forum List  »  PHP

Re: automatic entry of a number into a field based on another field in table
Posted by: Jonathan Stephens
Date: June 30, 2005 09:19PM

You can use CASE for this: http://dev.mysql.com/doc/mysql/en/control-flow-functions.html

For example:

INSERT INTO student (gcse, pts) VALUES ('$gcse', CASE '$gcse' WHEN 'D' THEN 5 WHEN 'C' THEN 4 ELSE 3 END);

In this case (heh), you might also be able to use ASCII() or ORD() in the query:

INSERT INTO student (gcse, pts) VALUES ('$gcse', ASCII('$gcse')-63);

See http://dev.mysql.com/doc/mysql/en/string-functions.htm

But this usually isn't a good idea. It goes against one of the basic principles of database design, which says the value of one table column shouldn't depend on the value of another.

Some reasons why this is so: You have to update two columns rather than one whenever you need to change that piece of data for that particular record; It's possible to get the columns out of synch with queries that change one column but not the other; It's a waste of space in the DB.

Instead, you should merely store the letters, then use CASE (or an expression involving ASCII()) in a SELECT query to obtain the numbers when needed. Or do it the other way round, storing just the numbers, and use a CASE expression (or an expression using CHAR()) to get the letters.

Jon Stephens
MySQL Documentation Team @ Oracle

MySQL Dev Zone
MySQL Server Documentation
Oracle

Options: ReplyQuote


Subject
Written By
Posted
Re: automatic entry of a number into a field based on another field in table
June 30, 2005 09:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.