Select statement does not see characters after @ in a row value

Question

Hi!

Very weird problem. Saving email addresses to my database, but when I query those emailaddresses it shows up as name@ instead of [email protected]. I suspect some internal filtering going on but have no idea how to solve it.

CREATE TABLE  `bredeschool`.`users` (
  `userID` smallint(6) NOT NULL AUTO_INCREMENT,
  `gebruikersnaam` varchar(10) NOT NULL,
  `wachtwoord` varchar(10) NOT NULL,
  `type` varchar(20) NOT NULL,
  `aanmaakDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `wijzigingsDatum` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`userID`),
  UNIQUE KEY `userID` (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

INSERT INTO users (gebruikersnaam, wachtwoord, type, aanmaakdatum, wijzigingsdatum) VALUES ('[email protected]', 'xxxx', 'ouder', '2010-06-25 15:16:40', '2010-06-25 15:16:40');

SELECT * FROM users WHERE gebruikersnaam = '[email protected]';

The last query does not yield a result. Executing without WHERE shows the emailaddress as name@.

Thanx, Chris

Answer 1

Hi, not sure about the insert issue, but the email address you're searching for is 16 characters, and the varchar field 'gebruikersnaam' is only 10 characters wide. It may be just that your table definition isn't wide enough to store the data you are trying to put in it.

Answer 2

The problem appears to be related to the size of the varchar. Try using varchar(100) for the gebruikersnaam field.

CREATE TABLE `users` (
  `userID` smallint(6) NOT NULL AUTO_INCREMENT,
  `gebruikersnaam` varchar(100) NOT NULL,
  `wachtwoord` varchar(10) NOT NULL,
  `type` varchar(20) NOT NULL,
  `aanmaakDatum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `wijzigingsDatum` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`userID`),
  UNIQUE KEY `userID` (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.05 sec)


INSERT INTO users 
    (gebruikersnaam, wachtwoord, type, aanmaakdatum, wijzigingsdatum) 
VALUES 
    ('[email protected]', 'xxxx', 'ouder', '2010-06-25 15:16:40', '2010-06-25 15:16:40');

Query OK, 1 row affected (0.00 sec)

The last query yields a result now:

SELECT * FROM users WHERE gebruikersnaam = '[email protected]';

+--------+------------------+------------+-------+---------------------+---------------------+
| userID | gebruikersnaam   | wachtwoord | type  | aanmaakDatum        | wijzigingsDatum     |
+--------+------------------+------------+-------+---------------------+---------------------+
|     15 | [email protected] | xxxx       | ouder | 2010-06-25 15:16:40 | 2010-06-25 15:16:40 |
+--------+------------------+------------+-------+---------------------+---------------------+
1 row in set (0.00 sec)

Answer 3

That is happening probably because your gebruikersnaam column is too short in length. I think you should change it to VarChar (50)