Laminas Db Select

Questions Components & MVC
1

Hello Experts :slight_smile:

I am looking for a possibilty to use mysql regex in ‘SELECT’ statement.

Example 5.7: SELECT username regexp ‘Name[0-9]{0,5}’ FROM users
Example 8: SELECT REGEXP_LIKE( username, ‘Name[0-9]{0,5}’) from users ( or RLIKE)

It is important for ‘UNIQUE’ constraint and new usernames.

Greeting :slight_smile:

2

Yeah it’s easy enough:-

use Laminas\Db\Sql\Sql;
use Laminas\Db\Sql\Expression as Expression;

$dbAdapter = null // An instance of your database adapter

$sql = new Sql($dbAdapter);

$select = $sql->select()
    ->from('users')
    ->columns([
        'match' => new Expression('REGEXP_LIKE(username, "Name[0-9]{0,5}")')
    ]);

$results = $dbAdapter->query(
    $sql->buildSqlString($select),
    $dbAdapter::QUERY_MODE_EXECUTE
);

That will build a SQL statement of

SELECT REGEXP_LIKE(username, ‘Name[0-9]{0,5}’) as `match` from `users`
3

great!
thank you so much :slight_smile:

@btw: and for 5.7 or is it the same way?

4

You could just substitute the following for MySQL 5.7:

$select = $sql->select()
    ->from('users')
    ->columns([
        'match' => new Expression('username REGEXP "Name[0-9]{0,5}"')
    ]);
1 Like