Hostname/IP regexp matching

[Atanos]

This is more of a SQL question, but anyway, how can accomplish the following in MySQL?

<?php

$hostname = "example.com";
$remote_host = "sub.example.com";


if (eregi("$hostname", "($remote_host|$_SERVER[REMOTE_ADDR])")) {
 echo "Matches!";
} else {
 echo "No match.";
}

?>

[xblue]

mysql does support regular expressions (see http://www.mysql.com/doc/en/Regexp.html), but I think

SELECT * FROM table WHERE field='example.com' OR field='sub.example.com'

could be closer to what you want?

[Atanos]

Yeah, I know, I just couldn't find a way to do the same kind of matching that PHP does, ie. I want to be able to match partial hostnames, in this case.

[xblue]

does LIKE do it the way you want?

SELECT * FROM table WHERE field LIKE '%example.com'

should match both "example.com" and "sub.example.com"

[Atanos]

No quite. You see, I would like to be able to filter out users by their hostnames like this;

If I insert "example.com" to the database, I want "anything.example.com" to match this. Just adding a % in front of the query does not accomplish this, as the "anything." part is not in the database.

Hope that makes sense 🙂

[Sxooter]

FYI, Postgresql does support regex matching, and I use it a lot. One of the many features I've gotten quite used to. Is switching a possibility for you? Or is this one of those things where someone else hosts it so you can use mysql or nothing?

[Atanos]

Actually, switching is a possibility for me (I host everything myself), and I've been meaning to give postgres a try, but never getting around to actually do it.

It looks like MySQL's FULLTEXT boolean search is what could do the trick, but that would require an update to MySQL 4.x.x.

Edit: typos.