A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearEnumerating tables used in mysql query? - page 1
User InfoPosts
Enumerating tables used in mysql query?#1
Is there any way to enumerate tables used in mysql query?

Lets say I have query :

SELECT * FROM   db_people.people_facts pf
INNER JOIN db_system.connections sm ON sm.source_id = pf.object_id
INNER JOIN db_people.people p ON sm.target_id = p.object_id
ORDER BY pf.object_id DESC


And i want in return array:

$tables = array(
[0] => (db_people.people_facts(,
[1] => (db_system.connections(,
[2] => (db_people.people(,
);


PS.
Thanks everyone for answers :)

posted date: 2009-04-11 07:37:00


Re: Enumerating tables used in mysql query?#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-11 07:37:01


Re: Enumerating tables used in mysql query?#3
Depending on what you(re using it for, MySQL(s EXPLAIN could do the trick for you:http://dev.mysql.com/doc/refman/5.0/en/explain.html

posted date: 2009-04-11 07:43:00


Re: Enumerating tables used in mysql query?#4
Yes, you can get information about tables and columns that are part of a query result. This is called result set metadata. The only PHP solution for MySQL result set metadata is to use the MySQLi extension and the mysqli_stmt::result_metadata() function.$stmt = $mysqli->prepare("SELECT * FROM db_people.people_facts pf INNER JOIN db_system.connections sm ON sm.source_id = pf.object_id INNER JOIN db_people.people p ON sm.target_id = p.object_id ORDER BY pf.object_id DESC");$meta = $stmt->result_metadata();$field1 = $meta->fetch_field();echo "Table for field " . $field1->name . " is " . $field1->table . "\n";You(ll have to build the array of distinct tables used in the query yourself, by looping over the fields.

posted date: 2009-04-11 09:01:00


Re: Enumerating tables used in mysql query?#5
Nice. Learn something new everyday.

posted date: 2009-04-11 10:51:00


Re: Enumerating tables used in mysql query?#6
The solution marked as good will return only the result tables. But if you do the next query it will fail:SELECT users.* FROM users, cats, dogs WHERE users.id = cats.user_idWill return only users and not cats and dogs tables.The best solution is find a good parser, another solution is using REGEX and EXPLAIN query (more info in the next link):http://www.momige.com/3397568/get-mysql-tables-in-a-queryBut I think that another good solution is list all tables and search them inside the query, you can cache the list of tables.EDIT: When searching for tables, better use a preg like:// (`|(|"| )table_name(\1|$)if(preg_match((/(`|\(|"| )table_name(\1|$)/i(, $query)) // foundIf not, it can return false positives with for example "table_name2", "table_name3"... table_name will return FOUND two times.

posted date: 2010-08-03 14:47:00


select page: « 1 »
Copyright ©2008-2017 www.momige.com, all rights reserved.