[SOLVED] Problems with addition

[BoarderLine]

Hi all,

 

Am hoping someone can point me in the right direction with this.

 

I am having some problems with the simple addition of columns using the following:-

 

SELECT c1+c2+c3+c4 FROM t1

 

The problem is that the result I am getting is not actually adding the 'actual' figures in t1 columns c1-c4 it is taking there values as 1.  The values are set as c1 - ENUM ('1','0'), c2 - ENUM ('2','0'), c3 - ENUM ('4','0'), and c4 - ENUM ('8','0').  This is giving me table values 1,2,4,8 which I am wanting to add together and insert into another table column.  Why does the above SELECT function not take the 'actual' table values in the addition????

 

Please help! Thanks. - MySQL6

[Barand]

CREATE TABLE  `test`.`enumtest` (
  `idenumtest` int(10) unsigned NOT NULL auto_increment,
  `c1` enum('red','white') NOT NULL,
  `c2` enum('blue','white') NOT NULL,
  PRIMARY KEY  (`idenumtest`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

mysql> INSERT INTO enumtest(c1,c2)
    -> VALUES ('red', 'blue');


mysql> SELECT * FROM enumtest;
+------------+-----+------+
| idenumtest | c1  | c2   |
+------------+-----+------+
|          1 | red | blue |
+------------+-----+------+


mysql> SELECT c1 + c2 FROM enumtest;
+---------+
| c1 + c2 |
+---------+
|       2 |
+---------+

because red and blue each have value 1 in their enum cols.

 

 

[Barand]

To get yours to work, try

SELECT CAST(c1 AS CHAR) 
        + CAST(c2 AS CHAR)
        + CAST(c3 AS CHAR)
        + CAST(c4 AS CHAR)
FROM t1

 

Why are you using ENUM like this?

 

 

[BoarderLine]

Ok Barand, I think I understand now thanks.  How can the enum col values be given different values for different fields? Is ENUM not the correct field type to be using here for what I am trying to achieve?  My aim with this is to set up a Users Access level.  I have two table entries on login, one with user details and the other their account setup type.  The account setup type takes it values (1,0) from checkboxes, and from these values I am trying to set the users access level depending on the addition of the checkbox values. I appreciate your time and help, Thanks again.

[BoarderLine]

Ok Barand, that sorted it out thanks very much.