CODEWITHSUNDEEP
phpregexsubstring
dokgu
dokgu

Reputation: 6070

PHP Regular Expression Substring

I have a very long query with a lot of sub-queries in it (almost a thousand lines long).

What I want to do is to get the names of all the tables that are being used in the query. So take the following example of a query just to get an idea:

SQL Query

$sql = "SELECT
    ...,
    ...,
    (
        SELECT COUNT(t.id) AS t_count
        FROM _user_just_a_table_db_ jat
        WHERE ...
        AND ...
        AND ...
    ),
    ...,
    ...,
    ...
    FROM _user_main_table_db_ tbl
    LEFT JOIN _user_joining_table_db_ jt
    ON jt.id = tbl.jt_id
    WHERE ...
    AND ...
    AND tbl.id IN (
        SELECT at.id
        FROM _user_another_table_db_ at
        WHERE at.active = 'Y'
    )";

In this particular situation, I want to get maybe an array or a string with the following results:

just_a_table
main_table
joining_table
another_table

Notes

  1. Please note that the names of the table can appear multiple times. You may or may not worry about that as I can easily remove duplicates after getting the resulting array or string.
  2. As you may have already noticed, the list of strings that I want to get will ALWAYS be in between _user_ and _db_.

Upvotes: 1

Views: 70

Answers (2)

rock321987
rock321987

Reputation: 11042

This should work

_user_(.*?)_db_

Regex Demo

PHP code

$match = array();
$re = "/_user_(.*?)_db_/"; 
$str = "SELECT\n    ...,\n    ...,\n    (\n        SELECT COUNT(t.id) AS t_count\n        FROM _user_just_a_table_db_ jat\n        WHERE ...\n        AND ...\n        AND ...\n    ),\n    ...,\n    ...,\n    ...\n    FROM _user_main_table_db_ tbl\n    LEFT JOIN _user_joining_table_db_ jt\n    ON jt.id = tbl.jt_id\n    WHERE ...\n    AND ...\n    AND tbl.id IN (\n        SELECT at.id\n        FROM _user_another_table_db_ at\n        WHERE at.active = 'Y'\n    )\""; 

$res = preg_match_all($re, $str, $match);
print_r($match[1]);

Ideone Demo

Upvotes: 2

Infor Mat
Infor Mat

Reputation: 117

$pattern = '/_user_(.*)_db_/';
preg_match_all($pattern, $sql, $match);
var_dump($match[0]);

Upvotes: 0

Related Questions