Tag Archives: Programming
How to validate e-mail address in MySQL
Most people validate e-mail addresses in a normal programming language, such as PHP and that is a very good idea when you process user input and such, however sometimes you want to validate data already entered in a database.
Take this example: you have a comments table where each person can enter an e-mail address, but since it’s an optional field you don’t even validate the input. However when you need to send a newsletter you don’t want to process all fields and validate one by one, you want to use a single query and just get all e-mail addresses (eventually by adding additional rules as well), so here’s how you can do it in MySQL:
SELECT *
FROM `m_comment`
WHERE `comment_url`
REGEXP ‘(.*)@(.*)\.(.*)’
Sure, you can optimize the query, but it’s a simple regular expression which .. well, in most cases is good enough.
How to make multiple updates using a single query in MySQL
As you might know it’s quite easy to make multiple INSERTs in a single query, like this:
INSERT INTO mytable
(id, title)
VALUES
('1', 'Lord of the Rings'),
('2', 'Harry Potter');
However, for some strange reason you can’t do multiple changes to a table in a single UPDATE query like this:
UPDATE mytable
SET (title='Great Expectations' WHERE id='1'),
(title='War and Peace' WHERE id='2');
Continue reading How to make multiple updates using a single query in MySQL
How to crash IE6 with one line
Hamachiya2 found out a way to crash Internet Explorer 6 (and on some systems even the entire operating system).
<style>*{position:relative}</style><table><input></table>
A simple line (which you can test it here) – no one figured out why caused an exception in mshtml.dll (and knowing Microsoft we’re most likely never going to find out the answer to that).
The code doesn’t have any problems on Firefox, IE7 or other browsers. Well, I hope you’re not still using IE6 – although according to these browser statistics 37.3% were still using it in June 2007.