1

How can rows starting with non alphabetic characters (all "special" characters, including spaces and underscores) be selected?

I don't know if it changes anything, but my_table will also contain values starting with Greek letters.

$query = $db->getQuery(true);
$query->clear();
$query
    ->select(array('id', 'last_name', 'first_name'))
    ->from($db->qn('#__my_table'))
    ->where($db->qn('last_name').' LIKE '. $db->q('[^a-zA-Z%]'))
    ->order('last_name, first_name ASC')
    ->setLimit($startrow, '100');
$db->setQuery($query);

The query form is correct and will return the correct results if I set it as: $db->q('A%') so my mistake must be with the regex, but I can't see where?

EDIT AFTER QUESTION

I am trying to create a letter menu like this A B C D...Α Β Γ Δ...#. When you click a letter it shows all lastnames starting from that letter when you click the # it should return all other "lastnames" (users imports the lastnames so it could be any special character or number, space, underscores).

This code is for when clicking # so it should filter out all lastnames starting with English and all lastnames starting with Greek characters. If there is any other way to categorize what I need please advice.

I found NOT REGEXP '. $db->q('[A-Za-z%]')) (UPDATED CODE BELOW) that will do most of the job, but it won't filter the Greek letters.

$query = $db->getQuery(true);
    $query->clear();
    $query
        ->select(array('id', 'last_name', 'first_name'))
        ->from($db->qn('#__my_table'))
        ->where($db->qn('last_name').' NOT REGEXP '. $db->q('[A-Za-zα-ωΑ-Ω%]'))
        ->order('last_name, first_name ASC')
        ->setLimit($startrow, '100');
    $db->setQuery($query);

SOLVED

I was able to find the correct regex

$query = $db->getQuery(true);
$query->clear();
$query
    ->select(array('id', 'last_name', 'first_name'))
    ->from($db->qn('#__my_table'))
    ->where($db->qn('last_name').' NOT REGEXP '. $db->q('^[a-z|α-ω]'))
    ->order('last_name, first_name ASC')
    ->setLimit($startrow, '100');
$db->setQuery($query);
  • first of all, i believe that you shouldn't have changed the initial code snippet in your question, as this makes my answer somewhat irrelevant - they can't be connected that well anymore, and it won't be helpful for others in the future. Secondly, I provided you with links to MySQL regex documentation which you should study a bit. – FFrewin Jun 27 '17 at 10:18
  • 1
    I recreated the question as it was and pasted my new code under it – John Jun 27 '17 at 10:23
1

First of all, do you actually need a where clause? Are you really trying to exclude any names? What are the actual exclusions you want?

If you don't mean to filter any names, then you can simply remove the WHERE clause in your query and have all the users/usernames.

Secondly, you can't pass such regular expressions in LIKE. I bet your above query isn't returning anything at all. That is because your WHERE is searching for names that start with [^a-zA-Z] and after that can have anything.

In contrast the LIKE 'A%' is searching for all names that start with A and have anything after that.

If you need to do regex, then MySQL provides REGEXP & NOT REGEXP & RLIKE.

MySQL Documentation:

It's a bit vague what you are trying to filter... Is it names that start with numbers, or what?

If so, this is a sample MySQL query with regex to filter out those:

$query
    ->select(array('id', 'username'))
    ->from($db->qn('#__users'))
    ->where($db->qn('username').' NOT REGEXP '. $db->q('^[0-9]'))
    ->order('username ASC')
    ->setLimit($startrow, '100');
0

I would like to refine the OP's solution edited into the question. So that researchers can compare and understand what is happening.

$query = $db->getQuery(true)
    ->select($db->qn(["id", "last_name", "first_name"])
    ->from($db->qn("#__my_table"))
    ->where($db->qn("last_name") . " REGEXP " . $db->q("^[^a-zα-ω]"))
    ->order($db->qn(["last_name", "first_name"]))
    ->setLimit((int)$limit, (int)$offset);
$db->setQuery($query);
  • I don't know that you need clear() so I'm removing it.
  • I have added all of the unnecessary qn() calls that Joomla Standards demands.
  • Your regex logic seeks rows that do not start with your two ranges of letters. To write a "negated character class, use ^ just after the [. Inside of a character class you do not use pipes to separate character ranges, just put them side by side.
  • By using a negated character class, you can drop the NOT from before REGEXP.
  • The default sorting direction is ASC, so you can omit that declaration from your query.
  • You have your start and limit parameters reversed. The limit should come before the offset. https://api.joomla.org/cms-3/classes/JDatabaseQueryLimitable.html#method_setLimit

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.