To collect files (images) matching a specific pattern (containing year 1900-2000) I set up different queries which both are working on mysql command line:

SELECT fname from (
        select filename fname
                from files f
                join content_field_attach_image a on (f.fid=a.field_attach_image_fid)
                join node n on (a.vid=n.vid)
        UNION
        select substring_index(filepath,'/',-1) as fname
                from files f
                join file_revisions r on (r.fid=f.fid and filename='_original')
                join node n on (r.vid=n.vid)
        ) as fnames
where fname REGEXP '^([a-zA-Z]{2,}[^0-9]*)([1-2][0,9][0-9]{2})(.*)'

select filename fname
        from files f
        join content_field_attach_image a on (f.fid=a.field_attach_image_fid)
        join node n on (a.vid=n.vid)
        where filename REGEXP '^([a-zA-Z]{2,}[^0-9]*)([1-2][0,9][0-9]{2})(.*)'
UNION
select substring_index(filepath,'/',-1) as fname
        from files f
        join file_revisions r on (r.fid=f.fid and filename='_original')
        join node n on (r.vid=n.vid)
        where substring_index(filepath,'/',-1) REGEXP '^([a-zA-Z]{2,}[^0-9]*)([1-2][0,9][0-9]{2})(.*)'

No errors reported by db_error.
db_fetch gets no records.

Comments

malcolmp’s picture

malcolmp commented

I just had the same issue. It looks like the { and } get removed by db_prefix_tables which is called by db_query (to insert the table prefix) . I got round the issue by modifying my regexp, to use no {}'s

Perhaps db_prefix_tables could be modified so that you can tell it to use a character other than {}, or maybe temporarily turn off this behavior. or another function like db_query but without the db_prefix_tables call could be created.

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented
Status: Active » Postponed (maintainer needs more info)

Were you executing the query on a database used from Drupal.org?

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented
Category: bug » support
Status: Postponed (maintainer needs more info) » Fixed

As also reported by malcolmp, the curly brackets are used from db_prefix_tables() to distinguish the database table names, which will be prefixed from the database prefix name.
There isn't an option to change the characters used, and they cannot be changed.

schildi’s picture

schildi commented

If it is a side effect of removing the curly braces then the fix would be to not modify strings marked by tickles in a query. That means, the REGEX '....' must not be changed by a function.

avpaderno’s picture

avpaderno
he/him
Primary language Italian
Location Brescia, 🇮🇹 🇪🇺
commented

It is enough that you use

$query = "SELECT fname FROM (
        SELECT filename fname
                FROM {files} f
                JOIN {content_field_attach_image} a ON (f.fid=a.field_attach_image_fid)
                JOIN {node} n ON (a.vid=n.vid)
        UNION
        SELECT substring_index(filepath,'/',-1) as fname
                FROM {files} f
                JOIN {file_revisions} r on (r.fid=f.fid and filename='_original')
                JOIN {node} n on (r.vid=n.vid)
        ) as fnames
WHERE fname REGEXP '%s'";

$result = db_query($query, "^([a-zA-Z]{2,}[^0-9]*)([1-2][0,9][0-9]{2})(.*)");

This is not an issue queue to ask about your own site; if you wanted to report a bug in Drupal, then you should have used http://drupal.org/project/issues/drupal.
It is rather difficult the characters used will be changed; it will not changed in Drupal 6 because the change would break all the existing code, and it will not changed in Drupal 7 because it is too late for such changed in the code. Maybe you can report a feature request for Drupal 8, but there will not be any settings to change the characters used because they need to always be the same characters for all the web sites.

schildi’s picture

schildi commented

interesting hint to insert the regexp as a parameter.
I will check this out.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Component: Database » Servers