21413

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,

Answer1:

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.

Answer2:

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.

Answer3:

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.

Answer4:

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.

Answer5:

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...

Answer6:

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

Recommend

  • 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++