CODEWITHSUNDEEP
phpmysql
AGK
AGK

Reputation: 86

Extract table names from any select query using PHP

I tried to get the all the table names from a query using PHP, using this function I got all my conditions except JOINS with multiple tables, please help me to extract JOIN query also, using this same function.

private function get_tables($query)
{
    $arr        =   explode("FROM", $query);
    $second     =   $arr[1];

    if($second==""){
        $arr    =   explode("from", $query);
        $second =   $arr[1];
    }

    $consts =   array('where','order', 'group', 'limit');
    $check=1;

    for($i=0;$i<count($consts); $i++)
    {
      if((stristr($second,$consts[$i])  !=  '')&&($check==1))
      {
          $where    =   explode($consts[$i],$second);
          if($check == 1)
          {
              $check=2;
              $tables= $where[0];
          }
      }
    }
    if($check == 1)
    {
      $tables= $arr[1]; 
    }
    $tab_arr    =   explode(',',$tables);
    $name_arr   =   array();

    foreach($tab_arr as $name)
    {
      if($name != '')
      { 
          $name =   trim($name);
          $narr =   explode(' ',$name);     
          $name_arr[]   =   $narr[0];
      }

    }
    $name_arr   =   array_unique($name_arr);
    return $name_arr;
}

Upvotes: 4

Views: 1664

Answers (2)

Mahmut Esinti
Mahmut Esinti

Reputation: 98

Maybe you can select all tables and find in query

in mysql:
SQL for one database:

SHOW TABLES FROM {database_name};

SQL for all databse:

SELECT * FROM information_schema.tables 
WHERE TABLE_TYPE = 'BASE TABLE';

and find in query:

$find_in_this_query = "your query";

$result = mysql_query("SHOW TABLES FROM sample_database;");

while ($row = mysql_fetch_row($result)) {
    if (strpos($find_in_this_query, $row[0]) !== false) {
        $found[] = $row[0];
    }
}

print_r($found);

Upvotes: 2

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

You can't parse SQL with simplistic regular expression matching... What happens when the tables are aliased? When there are subqueries? Cross joins? Unions? etc. The SQL grammar is quite rich.

Look for an SQL parser. e.g.:

https://code.google.com/p/php-sql-parser/

Upvotes: 2

Related Questions