3

I wanna detect all tables in an mysql query with php. I saw mysql_field_table() function that is work for normal queries. but when you use alternative name for a table (table_name AS new_name) it return alternative name not the real name, how ever I fix it by a regex. but now my problem is now with advanced queries like this:

SELECT mail_id, mail_date, mail_from, mail_to, mail_subject, (
            (
            SELECT COUNT(*)
            FROM `sys_messagecenter_qmails`
            WHERE qmail_mail_id = mail_id
            ) + (
            SELECT COUNT(*)
            FROM `sys_messagecenter_rels`
            WHERE rel_mail_id = mail_id
            )
            ) AS email_total, (
            SELECT COUNT(*)
            FROM `sys_messagecenter_rels`
            WHERE rel_mail_id = mail_id
            ) AS email_sent, (
            SELECT COUNT(*)
            FROM `sys_messagecenter_rels`
            INNER JOIN `sys_email_receives` ON receive_reply_to = rel_sent_id
            WHERE rel_mail_id = mail_id
            ) AS email_reply FROM `sys_messagecenter_emails` WHERE mail_draft='No' 
ORDER BY mail_id ASC LIMIT 0,10

now I think I need some regex for detect all real table names in a query. or any other perfect solution. how can I do this?

Improve this question
5
  • I don't think you can do that. Don't you already know the table names if you build the query? You need to find out the table names after the query is executed or you want to extract the tables used in the query variable? Commented Jun 13, 2012 at 8:31
  • must queries will wrote by developers in their modules. this is a part of an CMS. so no I don't know table names. I need a way to define table names before or after query runs Commented Jun 13, 2012 at 8:36
  • OMFG, if this is a query from a CMS I hope it will never see the sunlight... Commented Jun 13, 2012 at 9:00
  • @shadyyx :D it is only a query that runs in an admin page, so don't worry this CMS didn't crash mysql server :P Commented Jun 13, 2012 at 9:08
  • @shadyyx and absolutely this is an example (not an ordinary query) cause I don't know, developers may do something like this and I must render all kind queries Commented Jun 13, 2012 at 9:10

4 Answers 4

Reset to default
3

sorry... here a solution to extract it (if your query in $query)

if(preg_match_all('/((FROM|JOIN) `(.*)`)/', $query, $matches)) {
    $tables = array_unique($matches[3]);
    print_r($tables);
}
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

basically a wanna extract table names from a QUERY NOT extract all tables in a DATABASE
@IVIR3zaM i changed my answer
When I ran this, I got a huge range between the first and the last instead of selecting each individual table. This is the line I used to get each table on its own /((from|join) `(.*?)`)/
3

I also need to extract table name from queries.Then I write my own script which works on almost every basic query.

$tables = array();
$sql ="SELECT m.`id` FROM my_table m INNER JOIN mytable_2 m2 ON(m.`id` = m2.`m_id`) ";

    $query_structure = explode( ' ', strtolower( preg_replace('!\s+!', ' ', $sql) ) );

    $searches_from = array_keys( $query_structure , 'from');
    $searches_join = array_keys( $query_structure , 'join');
    $searches_update = array_keys( $query_structure , 'update');
    $searches_into = array_keys( $query_structure , 'into');
    $searches = array_merge($searches_join , $searches_from , $searches_update , $searches_into );
    foreach($searches as $search ){
        if(isset($query_structure[$search+1])){
            $tables[] = trim( $query_structure[$search+1] , '` ');
        }
    }
    print_r($tables);

Hope this will help

Improve this answer

Comments

1

I use the code below (a is a var_dump equivalent):

/**
 * Only works if the table names and database names don't have a space or a dot in them.
 */

$queries = [
    "select * from my_table",
    "select * from `my_table`",
    "select * from my_db.my_table",
    "select * from my_db.`my_table`",
    "select id from my_table where x=2",
    "select id from my_table mt inner join other_table ot on ot.my_table_id=mt.id where mt.x=2",
    "SELECT mail_id, mail_date, mail_from, mail_to, mail_subject, (
            (
            SELECT COUNT(*)
            FROM `sys_messagecenter_qmails`
            WHERE qmail_mail_id = mail_id
            ) + (
            SELECT COUNT(*)
            FROM `sys_messagecenter_rels`
            WHERE rel_mail_id = mail_id
            )
            ) AS email_total, (
            SELECT COUNT(*)
            FROM `sys_messagecenter_rels`
            WHERE rel_mail_id = mail_id
            ) AS email_sent, (
            SELECT COUNT(*)
            FROM `sys_messagecenter_rels`
            INNER JOIN `sys_email_receives` ON receive_reply_to = rel_sent_id
            WHERE rel_mail_id = mail_id
            ) AS email_reply FROM `sys_messagecenter_emails` WHERE mail_draft='No' 
ORDER BY mail_id ASC LIMIT 0,10 ",
      "select count(*) as count from (
select 
user_id,
permission_group_id,
concat(uhpg.user_id, '. ', u.pseudo) as user_id_plus,
concat(uhpg.permission_group_id, '. ', pg.name) as permission_group_id_plus
from lud_user_has_permission_group uhpg
inner join lud_user u on uhpg.user_id=u.id
inner join lud_permission_group pg on uhpg.permission_group_id=pg.id
) as ttt",
];


$tables = [];

foreach ($queries as $query) {
    a($query);
    if (preg_match_all('!((FROM|JOIN)\s([\S]+))!i', $query, $matches)) {
        $tables = array_unique($matches[3]);
        array_walk($tables, function (&$v) {
            $p = explode('.', $v, 2);
            $v = array_pop($p);
            $v = trim($v, '`');
        });
        $tables = array_filter($tables, function ($v) {
            return ('(' !== $v);
        });
        a($tables);
    }
}

Which gives the following output:

string(22) "select * from my_table"

array(1) {
  [0] => string(8) "my_table"
}

string(24) "select * from `my_table`"

array(1) {
  [0] => string(8) "my_table"
}

string(28) "select * from my_db.my_table"

array(1) {
  [0] => string(8) "my_table"
}

string(30) "select * from my_db.`my_table`"

array(1) {
  [0] => string(8) "my_table"
}

string(33) "select id from my_table where x=2"

array(1) {
  [0] => string(8) "my_table"
}

string(89) "select id from my_table mt inner join other_table ot on ot.my_table_id=mt.id where mt.x=2"

array(2) {
  [0] => string(8) "my_table"
  [1] => string(11) "other_table"
}

string(810) "SELECT mail_id, mail_date, mail_from, mail_to, mail_subject, (
            (
            SELECT COUNT(*)
            FROM `sys_messagecenter_qmails`
            WHERE qmail_mail_id = mail_id
            ) + (
            SELECT COUNT(*)
            FROM `sys_messagecenter_rels`
            WHERE rel_mail_id = mail_id
            )
            ) AS email_total, (
            SELECT COUNT(*)
            FROM `sys_messagecenter_rels`
            WHERE rel_mail_id = mail_id
            ) AS email_sent, (
            SELECT COUNT(*)
            FROM `sys_messagecenter_rels`
            INNER JOIN `sys_email_receives` ON receive_reply_to = rel_sent_id
            WHERE rel_mail_id = mail_id
            ) AS email_reply FROM `sys_messagecenter_emails` WHERE mail_draft='No' 
ORDER BY mail_id ASC LIMIT 0,10 "

array(4) {
  [0] => string(24) "sys_messagecenter_qmails"
  [1] => string(22) "sys_messagecenter_rels"
  [4] => string(18) "sys_email_receives"
  [5] => string(24) "sys_messagecenter_emails"
}

string(360) "select count(*) as count from (
select 
user_id,
permission_group_id,
concat(uhpg.user_id, '. ', u.pseudo) as user_id_plus,
concat(uhpg.permission_group_id, '. ', pg.name) as permission_group_id_plus
from lud_user_has_permission_group uhpg
inner join lud_user u on uhpg.user_id=u.id
inner join lud_permission_group pg on uhpg.permission_group_id=pg.id
) as ttt"

array(3) {
  [1] => string(29) "lud_user_has_permission_group"
  [2] => string(8) "lud_user"
  [3] => string(20) "lud_permission_group"
}
Improve this answer

Comments

0

This isn't the most elegant of solutions however has passed all of the tests I've run so far:

function get_table_name($query) {

    $query = trim(str_replace(PHP_EOL, ' ', $query));

    $table = '';

    if(strtolower(substr($query, 0, 12)) == 'create table') {
        $start = stripos($query, 'CREATE TABLE') + 12;
        $end = strpos($query, '(');
        $length = $end - $start;
        $table = substr($query, $start, $length);
    }
    elseif(strtolower(substr($query, 0, 6)) == 'update') {
        $end = stripos($query, 'SET');
        $table = substr($query, 6, $end);
    }
    elseif(strtolower(substr($query, 0, 11)) == 'alter table') {
        $parts = explode(' ', $query);
        $table = $parts[2];
    }
    elseif(strtolower(substr($query, 0, 11)) == 'insert into') {
        $parts = explode(' ', $query);
        $table = $parts[2];
    }
    elseif(strtolower(substr($query, 0, 12)) == 'create index') {
        $parts = explode(' ', $query);
        $table = $parts[4];
    }
    elseif(strtolower(substr($query, 0, 6)) == 'select') {
        $parts = explode(' ', $query);
        foreach($parts as $i => $part) {
            if(trim(strtolower($part)) == 'from') {
                $table = $parts[$i + 1];
                break;
            }
        }
    }
    elseif(strtolower(substr($query, 0, 29)) == 'create unique clustered index') {
        $parts = explode(' ', $query);
        $table = $parts[6];
    }
    elseif(strtolower(substr($query, 0, 22)) == 'create clustered index') {
        $parts = explode(' ', $query);
        $table = $parts[5];
    }
    elseif(strtolower(substr($query, 0, 15)) == 'exec sp_columns') {
        $parts = explode(' ', $query);
        $table = str_replace("'", '', $parts[2]);
    }
    elseif(strtolower(substr($query, 0, 11)) == 'delete from') {
        $parts = explode(' ', $query);
        $table = str_replace("'", '', $parts[2]);
    }

    return trim(str_replace(['`', '[', ']'], ['', '', ''], $table));

}
Improve this answer

1 Comment

SHOW and DESCRIBE, while likely less often to be used, are missing

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.