Extract original tables from MySQL query using PHP?

Question

I am trying to find a way to get a list of all of the tables referenced by the fields that are returned in a MySQL query using PHP. I know that EXPLAIN gives table information, but it returns the table alias if one is used.

i.e. EXPLAIN will return "tablea", "tableb" and "test" from:

SELECT test.* FROM t1
JOIN t2 ON t2.id = t1.id
JOIN alias AS test ON test.eid = t2.eid

However, in the example above, I would only like to get "alias".

With simple queries, I can interrogate the string and pull out the information, but this gets quite complicated when taking into account subqueries, field aliases and the various joins that could be included.

Is there an easy way to do this? If not, I would appreciate any pointers on how this can be done!

O. Jones · Accepted Answer · 2014-06-11 20:09:58Z

1

You truly can't do what you're asking in pure SQL.

Why not? Conceptually, SQL SELECT queries work by declaring new virtual tables based upon the content of other tables (virtual and physical) and views. So, when your SQL server software presents a result set, it's presenting the virtual table of your query. That virtual table's metadata (column names and so forth) honors the intent of the query, including the assignment of alias names to tables.

answered Jun 11, 2014 at 20:09

O. Jones

110k17 gold badges135 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thebronsonite Over a year ago

Thank you, that makes sense. It would be quite nice if there was a "trace" feature, which listed how it got to the result set, but that's something for another time! For now, I will have to interrogate the query itself. Luckily, I can make it simpler, albeit with less functionality.

Mimouni · Accepted Answer · 2014-06-11 18:57:11Z

-1

this is a simple way to generate tables in a file :

<?php
//ENTER THE RELEVANT INFO BELOW
$mysqlDatabaseName ='db123456789';
$mysqlUserName ='dbo123456789';
$mysqlPassword ='myPassword';
$mysqlHostName ='db1234.perfora.net';
$mysqlExportPath ='chooseFilenameForBackup.sql';

//DO NOT EDIT BELOW THIS LINE
//Export the tables and output the status to the page
$command='mysqldump --opt -h' .$mysqlHostName .' -u' .$mysqlUserName .' -p' .$mysqlPassword .' ' .$mysqlDatabaseName .' > ~/' .$mysqlExportPath;
exec($command,$output=array(),$worked);
switch($worked){
case 0:
echo 'Database <b>' .$mysqlDatabaseName .'</b> successfully exported to <b>~/' .$mysqlExportPath .'</b>';
break;
case 1:
echo 'There was a warning during the export of <b>' .$mysqlDatabaseName .'</b> to <b>~/' .$mysqlExportPath .'</b>';
break;
case 2:
echo 'There was an error during export. Please check your values:<br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$mysqlDatabaseName .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$mysqlUserName .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$mysqlHostName .'</b></td></tr></table>';
break;
}
?>

//NOTE: this is not my code, but I tested it and works perfectly.

answered Jun 11, 2014 at 18:57

Mimouni

3,6483 gold badges32 silver badges37 bronze badges

2 Comments

VMai Over a year ago

I think you misunderstood the question. OP wants to know the list of tables that are referenced in an arbitrary query. He doesn't want to make a dump of the database.

Mimouni Over a year ago

@VMai: aah ok, I misunderstood the question.

Collectives™ on Stack Overflow

Extract original tables from MySQL query using PHP?

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related