top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

mysql custom global defined variable

+1 vote
289 views

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.

posted Mar 13, 2013 by Salil

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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

answer Mar 13, 2013 by Salil Agrawal
Similar Questions
0 votes

Have two questions -
1. How to find type of a variable in PHP.
2. How to find the type of an array in PHP.

Please help.

–1 vote

I want to update my sql database record and want to populate the value of previous record. So anybody can help me to get the previous value of the record to show when the update require.

...