Re: [PHP] mysql custom global defined variable

[Camilo Sperberg]

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.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP] mysql custom global defined variable

[Marco Behnke]

Am 13.03.13 10:35, schrieb Kevin Peterson:
 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 ? 

I stopped using define in favor of somehow namespaced constants as
const in classes. But basically there is no difference.
Advantages of using constants (which ever) are code completion to avoid
spelling errors. I see no possible improvements here.

 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.

Maybe this is what you are looking for?
http://forums.mysql.com/read.php?98,273432,273432


-- 
Marco Behnke
Dipl. Informatiker (FH), SAE Audio Engineer Diploma
Zend Certified Engineer PHP 5.3

Tel.: 0174 / 9722336
e-Mail: ma...@behnke.biz

Softwaretechnik Behnke
Heinrich-Heine-Str. 7D
21218 Seevetal

http://www.behnke.biz




signature.asc
Description: OpenPGP digital signature