Re: [PHP] 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.