SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Regex in MySQL Query

    Can regular expressions be used in PHP/MySQL database queries? If so, where could they be used in a query like the one below?:

    1. In the SELECT clause (e.g. Use Regex to say "SELECT GG.Name OR GG.Type")

    2. Use Regex to select fields from table gw_geog OR table gw_topics

    3. Use Regex to join table gw_geog_par2 OR a different table

    4. Use Regex in the WHERE clause, either in lieu of GG.Name or "nat"

    PHP Code:
    $Geog mysql_fetch_assoc(mysql_query("SELECT GG.N, GG.IDArea MyID, GG.Name, GG.Type
      FROM gw_geog GG
      LEFT JOIN gw_geog_par2 GP ON GP.IDArea = GG.IDArea
      WHERE GG.Name = 'nat'"
    )); 
    If #4 is possible, can someone tell me how to do the following?:

    1. Replace nat with a regular expression that says "WHERE GG.Name = nat OR sta" (two values)

    3. Replace nat with a regular expression that says "WHERE GG.Name = nat, sta OR pro" (three values)

    Thanks.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Google "mysql regular expression" would bring you to the MySQL manual page for regular expressions:
    http://dev.mysql.com/doc/refman/5.1/en/regexp.html

    You can use the regular expression functions in SELECT and WHERE clauses only.

    But you don't need a regular expression for what you're trying to do.

    Code:
    WHERE GG.Name = 'nat' OR GG.Name = 'sta'
    WHERE GG.Name = 'nat' OR GG.Name = 'sta' OR GG.Name = 'pro'
    Similarly regular expressions are not useful for #1, #2 or #3. Join all the tables you may need, using outer joins, and use an IF or CASE conditional to create the columns that depends on some condition, such as some column having a value or not.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    5 Thread(s)
    Its best to avoid regular expressions whenever possible because they are increasingly slower then the alternatives most of the time. In this instance it seems like you can avoid their use easily. Therefore, I would recommend you consider Dan Grossman's solution over any using regular expressions.

  4. #4
    Non-Member
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    4,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    Its best to avoid regular expressions whenever possible because they are increasingly slower then the alternatives most of the time. In this instance it seems like you can avoid their use easily. Therefore, I would recommend you consider Dan Grossman's solution over any using regular expressions.
    It sounds like regex might not be the best choice for DB queries, but are you sure they're slower than alternatives - and what makes them "increasingly" slower? If anything, I thought regex would be faster than most programming languages.

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by geosite View Post
    It sounds like regex might not be the best choice for DB queries, but are you sure they're slower than alternatives - and what makes them "increasingly" slower? If anything, I thought regex would be faster than most programming languages.
    WHERE expressions based on regular expressions can't make use of indexes, which makes them orders of magnitude slower when an index would've been available.

    To satisfy this condition, assuming an index on the Name column:
    Code:
    WHERE GG.Name = 'nat' OR GG.Name = 'sta' OR GG.Name = 'pro'
    If you wrote an equivalent expression matching nat/sta/pro and used it as the WHERE clause here, MySQL has to compile the expression, then apply it against every row in the entire table. This means loading the entire table from disk into memory, instead of just examining the index which is probably already in memory due to its small size, as well as the CPU cycles involved in matching every row against the expression.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •