Re: mysql custom global defined variable

On Mar 13, 2013, at 10:35 AM, Kevin Peterson wrote: > In my database design, I tend to store some variable that is meant to be acting as a ROLE or > TYPE as SMALLINT. For example : > > CREATE TABLE house ( > id int(11) NOT NULL AUTO_INCREMENT, > type smallint(11) NOT NULL, > ) > > > And in php, I do > > define('HOUSE_SMALL_TYPE', '0'); > define('HOUSE_MEDIUM_TYPE', '1'); > > So in php, in SELECT queries I do : > > $this->db->query("SELECT * FROM house > WHERE type=?;", HOUSE_SMALL_TYPE); > > My questions are : > 1. In the php part, is there is a better way to do this ? > 2. In the mysql itself, does mysql also has global define functionality (like the define in > php) ? I also want to do kind of SELECT * FROM house WHERE type = HOUSE_SMALL_TYPE in mysql query. > Question 1: I see no possible improvements, you could however use an array with values instead of constants, but that's rather a personal choice as I don't like constants that much, unless you are on your own namespace. My example implementation: $houseTypes = array( 'house_small_type' => 0, 'house_medium_type' => 1, etc. ); Question 2: You could use ENUM data type, but it has quite a few disadvantages: 1- Translation could be tricky to implement 2- DDL shouldn't be used for data! 3- Updating or deleting values can leave your old records in an inconsistent state You can also use SET to set variables, I've never used them but I think they could work in your case: http://dev.mysql.com/doc/refman/5.5/en/user-variables.html Greetings.