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

Very 开发者_运维百科weird problem. Saving email addresses to my database, but when I query those emailaddresses it shows up as name@ instead of name@example.com. 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 ('name@example.com', 'xxxx', 'ouder', '2010-06-25 15:16:40', '2010-06-25 15:16:40');

SELECT * FROM users WHERE gebruikersnaam = 'name@example.com';

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

Thanx, Chris

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.

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 
    ('name@example.com', '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 = 'name@example.com';

+--------+------------------+------------+-------+---------------------+---------------------+
| userID | gebruikersnaam   | wachtwoord | type  | aanmaakDatum        | wijzigingsDatum     |
+--------+------------------+------------+-------+---------------------+---------------------+
|     15 | name@example.com | xxxx       | ouder | 2010-06-25 15:16:40 | 2010-06-25 15:16:40 |
+--------+------------------+------------+-------+---------------------+---------------------+
1 row in set (0.00 sec)

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