CODEWITHSUNDEEP
phpmysqlregex
Luke Pittman
Luke Pittman

Reputation: 843

Regex/SQL - Find "WHERE" but not between parentheses

I am trying to work out the regex (which I have very little experience with) to check for the presence of one string ("WHERE") within the sql statement, but only if it does not exist between parentheses.

I am using PHP and would appreciate the code or help in that.

I would post code that I have tried, but actually have no idea even where to start. I've attempted to use other methods to achieve the desired effect, but keep running into one issue or another.

I am trying to build an SQL statement based on a bunch of search criteria, and in each possible search field I need to determine weather to place "WHERE" or "&&" in the sql statement before the where comparison.

I did this by using strstr for awhile, but then had to add a subquery to the "SELECT" portion of the sql which required the use of a "WHERE", so that obviously messed up my method.

Any tips would be excellent.

EDIT:

Beginning of statement:

SELECT `t1`.*, (SELECT COUNT(`eta`.`time_away`) FROM `table2` WHERE `table2`.`field` = '1') as `time_away`
FROM `table1` `t1`
LEFT JOIN `table3` `t3` ON `t1`.`id` = `t3`.`t3id`

Then I have various conditions that will add a WHERE statement, for example:

if ($this === true) {
  ### CHECK HERE TO SEE IF WHERE EXISTS, BUT NOT THE WHERE WITHIN THE SUBQUERY
  if ($sql does not contain WHERE outside of subqueries) {
    $sql .= " WHERE ";
  } else {
    $sql .= " && ";
  }
  $sql .= ""; // add my condition here
}

This obviously its excessive for only one condition, however my script will have many and in the ones after the first one it will be very necessary.

Upvotes: 1

Views: 634

Answers (2)

Casimir et Hippolyte
Casimir et Hippolyte

Reputation: 89629

You can try this test:

if (preg_match('~(?>(?>[^()w]+|\Bw+|\bw(?!here\b))+|(\((?>[^()]++|(?-1))*\)))*\bwhere\b~i',
               $string, $match)) {
    /* what you have to do */
}

The advantage of this solution is that it can deal with nested parenthesis and avoid to miss some content, when other ways will fail, example:

xxxxxxxxxxxxx (sdfs(df df)g df) WHERE (sdfsdfdsfsdfsdf) xxxxxxxxxxxxxxx

Pattern details:

(?>                    # open the first non capturing group (* atomic)
    (?>                # open the second non capturing group
        [^()w]+        # all characters except ( ) w, one or more times 
      |                # OR
        \Bw+           # some w preceded by a word character (ie: a-zA-Z0-9_)
      |                # OR
        \bw(?!here\b) # some w not preceded by a word character
                       # and not followed by "here"
    )+                 # close the second group and repeat 1 or more times
  |                    # OR
    (                  # open the first capturing group
        \(             # literal (
        (?>            # open the third non capturing group
            [^()]++    # all characters except ( ), one or more times (* possessive)
          |            # OR
            (?-1)      # repeat the last capturing group
        )*             # close the third non capturing group
                       # and repeat it zero or more times
        \)             # literal )
    )                  # close the thirst capturing group
)*                     # close the first non capturing group
                       # and repeat 0 or more times
\bwhere\b              # "where" not followed and not preceded by a word character

The goal of this pattern is to match all that is possible until the word "where" outside parenthesis.

Upvotes: 1

Daniel Gabado
Daniel Gabado

Reputation: 284

Remove everything that is within () and search WHERE clause in what is left of the sql command:

<?php
$string = 'select * from (select * from a where a.id=x) where id=y';
$pattern = '/\(.*\)/';
$replacement = '';
echo preg_replace($pattern, $replacement, $string);
?>

Will print:

select * from where id=y

And now you can do your normal 'where search'.

Upvotes: 2

Related Questions