Get mysql tables in a query

Question

There is any way to get the tables I'm using in a query?

The first method I was using was with regular expressions:

// result[1] = "SELECT"
// result[3] = "All between FROM and WHERE, ex: `users`, ex: `users`, `test`

if($result[1] == "SELECT" && !preg_match('/FROM\s*(.*?,.*?)\s*WHERE/i', $query, $res))
{
    $tables = preg_replace('/`|\'|\s/i', '', $result[3]); // strip ` or ' or spaces
    $tables = explode(",", $tables);
    sort($tables);
}

But there are complex mysql queries, so the next method I used is:

EXPLAIN SELECT...

and get the tables from the result array.

The problem comes with counts, I know that in MyISAM db's the number of rows is stored, so if you do the next query:

SELECT COUNT(*) FROM users

You don't get the table that is used in the query, you get "Select tables optimized away" because any table is used.

So, there is another method to get tables used in a query? Thanks

Answer 1

What I'm doing is using both method's.

private function get_tables($query)
{
    $res = preg_match('/^(SELECT|UPDATE|INSERT|DELETE)(.+?FROM| INTO)?\\s*`?(.+?)($|`| |\\()/i', $query, $result);

    if(!$res)
        return false;

    $tmp_tables = array();

    // Get array tables using EXPLAIN
    if($result[1] == "SELECT")
        $tmp_tables = $this->get_select_query_tables($query);

    // Get array tables using REGEX
    if($result[1] == "SELECT" && !preg_match('/FROM\s*(.*?,.*?)\s*WHERE/i', $query, $res))
    {
        $tables = preg_replace('/`|\'|\s/i', '', $result[3]);
        $tables = explode(",", $tables);
        sort($tables);

        $tmp = $tables;

        // Check if all REGEX tables are in EXPLAIN tables, if not, add the missed
        foreach($tmp as $tmp_table)
            if(!in_array($tmp_table, $tmp_tables))
                $tmp_tables[] = $tmp_table;
    }

    return $tmp_tables;
}

Answer 2

If you explicitly prefix your tables with the database name every time they're mentioned, it'll make your RegEx a whole heck of a lot easier. :-) Can you edit your queries?