PHP: using REGEX to get the tablename from a mysql query

Consider these three mysql statements:

select * from Users;
select id, title, value from Blogs;
select id, feelURL, feelTitle from Feeds where id = 1; 

Now im not very good at REGEX, but i want to get the table name from the mysql query. Could someone possibly create one for me with a little explanation.

Thanks,

You can actually use MySQL as the parser and get the tablenames in your query no matter how complex your SQL syntax.

(Sorry that this is a late response to your question - I just had the same problem today and found this solution.)

Simply prefix your query with the word EXPLAIN and the results set returned to PHP will include id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra. The third column is the name of each table in your query.

For example, if your query was:

select count(*) from ey_def left join ey_rels on def_id=item_id;

Use:

explain select count(*) from ey_def left join ey_rels on def_id=item_id;

And MySQL will return this to PHP:

+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | ey_def  | index | NULL          | PRIMARY | 4       | NULL |   87 | Using index |
|  1 | SIMPLE      | ey_rels | ALL   | NULL          | NULL    | NULL    | NULL |  123 |             |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+

Now you can simply process the results like any other query.

Try:

preg_match('/\bfrom\b\s*(\w+)/i',$query,$matches)

This will not work if the query has more than 1 table.

Basically the regex searchs for the complete word FROM in the query and picks the following word as the table name.

A naive implementation would be this:

preg_match("/\s+from\s+`?([a-z\d_]+)`?/i", $query, $match);

echo $query . " => " . $match[1] . "\n";

This will break when you have a subquery in your SELECT field list (and probably in a few other cases). Or when your table name contains characters beside a-z, numbers and underscores.

Parsing SQL correctly isn't trivial.

For the query string you gave, the following should do:

preg_match_all('/from (\w+)/', $query, $tables);

print_r($tables[1]);

[0] => Users
[1] => Blogs
[2] => Feeds

But like pointed out in a comment already, creating a full fledged SQL parser is a non-trivial task. Don't expect this to be usable on any and all queries you throw against it.

Wish I would have seen this earlier... Like the people above me stated, it's non-trivial parsing sql statements. To pick out the table names from a sql string, it would be a better idea to get all the table names first, then find matches in the sql (providing you don't have a million tables in your database). I just so happen to have a function on hand that does just that:

/*
  Takes a sql statement and attempts to get a table name from it.
  This assumes a database is already specified in the connection.

  [$sql]: string; SQL statement that was executed
  [$conn]: resource; MySQLi connection resource

  returns table name string
 */  
function get_table_names($sql,$conn){
    //declare variables
    $table_array = array();
    $table_string = "";

    //get all the table names in the selected database
    $sql2 = "SHOW TABLES";
    $result = mysqli_query($conn, $sql2);

    //display an error if something went wrong
    if (!$result) {
        echo "DB Error, could not list tables\n";
        echo 'MySQL Error: ' . mysqli_error($conn);
        exit;
    }

    //fetch the rows and push the table names into $table_array
    while ($row = mysqli_fetch_row($result)) {
        array_push($table_array, $row[0]);
    }

    //loop through all the tables in the database
    foreach($table_array as $table){
        if(strpos($sql,$table)){ //if match is found append to string
            $table_string .= " $table ";
        }
    }

    //return a string of table name matches
    return $table_string;
}

Hope that helps someone...

This should do it:

(SELECT|DELETE|UPDATE|INSERT INTO) (\*|[A-Z0-9_]+)(FROM)?([A-Z0-9_, ]+)

It will works with select delete update and insert. If you use tablename1, tablename2 it will return it as a array