Extract table names from a SQL query with multiple tables using PHP

PHP API to return table names from a given SQL statement with multiple tables

Input

 $query = "SELECT `main_categories`.*, `categories_views`.`view` AS view_ctg, (SELECT COUNT(id_geo) FROM main_geo WHERE id_geo=geo_filter_ctg AND FIND_IN_SET('PK', `countries_geo` )) as countryCount
 FROM (`main_categories`)
 JOIN `categories_parents` ON `main_categories`.`id_ctg` = `categories_parents`.`id_ctg`
 JOIN `categories_views` ON `main_categories`.`id_ctg` = `categories_views`.`id_ctg`
 WHERE `categories_parents`.`parent_ctg` =  '8701'
 AND `categories_views`.`device` =  '3'
 AND `status_ctg` != 0
 HAVING `countryCount` > 0
 ORDER BY `priority_ctg` asc
 LIMIT 18";

API Call

 table_names($query);

API Implementation

 function table_names($query) {
  preg_match_all("/((?:^select .+?(?:FROM|INTO))|^UPDATE|^TABLE|JOIN|ON|FROM) (`?\w+`?)\s/", $query, $matches);
  preg_match_all("/((FROM|JOIN|ON) `(.*?)`)/", $query, $matches2);
  $matches = array_unique($matches);
  $matches2 = array_unique($matches2);
  $merged_array = array_merge($matches, $matches2);
  $output=array();
   foreach ($merged_array as $key => $ind_array) {
     foreach ($ind_array as $key => $value) {
        $value = preg_replace('/[``"\']/', '', $value);
        $value = preg_replace('/[FROM]/', '', $value);
        $value = preg_replace('/[JOIN]/', '', $value);
        $value = preg_replace('/[ON]/', '', $value);
        $output[] = trim($value);		
        } 
   }
   return print_r(array_unique($output));
 }

Output (w.r.t above mentioned Input)

 Array
 (
 [0] => main_geo
 [1] => categories_parents
 [2] => categories_views
 [4] => main_categories
 )

nadeem-khan/Extract table names from a SQL query with multiple tables using PHP.md