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.





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.


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;


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.


A naive implementation would be this:

preg_match("/\s+from\s+`?([a-z\d_]+)`?/i", $query, $match); echo $query . " => " . $match[1] . "\n";

This <strong>will</strong> 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:


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


  • Sympy: working with equalities manually
  • Winforms: Add a close “x” button in a UserControl
  • Receive list of elements in their visual order
  • Use default value of a column in stored procedures
  • conditions for accessors in Coldfusion ORM
  • Enumerating Controls on a Form
  • Monotouch crashes with NullReferenceException on non nullable object
  • Access object instance inside an event handler
  • Extract All Possible Paths from Expression-Tree and evaluate them to hold TRUE
  • How to get links to open in the native browser in iOS Meteor apps?
  • Basic many-to-many left join query
  • Why isn't obj.style.left = “200px”; working in this code?
  • jQuery: add elements until a particular height is reached
  • Javascript, Regex - I need to grab each section of a string contained in brackets
  • Floated image with variable width and heading with background image
  • Using Sax parsing to edit and write XML in VB6
  • Reduction and collapse clauses in OMP have some confusing points
  • Remove final comma from string in vb.net
  • Groovy: Unexpected token “:”
  • how to display data from 1st point on words on y axis for line chart in d3.js
  • Blackberry - Custom EditField Cursor
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Replace value with Factor in r data.table
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • Django: Count of Group Elements
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Projection media query: browser support and workarounds?
  • How to check if every primary key value is being referenced as foreign key in another table
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • How to handle AllServersUnavailable Exception
  • vba code to select only visible cells in specific column except heading
  • Do I've to free mysql result after storing it?
  • How to get next/previous record number?
  • R: gsub and capture
  • Transpose CSV data with awk (pivot transformation)
  • python regex in pyparsing
  • Sorting a 2D array using the second column C++