CODEWITHSUNDEEP
phpmysqlsql
Stefano Radaelli
Stefano Radaelli

Reputation: 1118

php & mysql: Get the list of database, tables and fields extracted by a query

I need to extract the list of databases, tables and fields extracted by a query like this having 2 separate databse DataBase 'customer' containing a table 'register' with list of customers DataBase 'supplier' containing a table 'register' with list of suppliers

Certain customers are suppliers also and I need to extract 'name' + money in & money out. In this case (if a customer is a supplier also) they would share the same 'id' key.

select
   C.name, C.surname,
   C.balance as MONEY_IN, S.balance as MONEY_OUT
from
   customer.register C, supplier.register S
where
   C.id = S.id

what I would like to obtain is a list with database_name . table_name . field_name extracted by the query

ie:

customer.register.name
customer.register.surname
customer.register.balance
supplier.register.balance

Is there a way to get the above list through a php script?

Through (deprecated) mysql_field_table() and mysql_field_name() I can get the table and field name, but how I can extract the database name also?

Upvotes: 0

Views: 177

Answers (1)

O. Jones
O. Jones

Reputation: 108676

MySQL's information_schema database (a fake database in that you can't insert, update, or delete directly) holds this sort of data.

For example

 SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
   FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = database()

retrieves all the names of tables and columns in the current database.

You can get the current database (the subject of the most resent USE whatever operation with SELECT database().

The trouble with getting the column names making up a SELECT query is this: The SELECT query itself generates a virtual table. So if the columns coming out of the SELECT query have the same names as those going into it, you can definitely get their column names. SELECT * FROM table generates that kind of result set.

In PDO, getColumnMeta(column_number) gives you back some metadata for a result set. In mysqli_, it's fetch_fields().

In your specific case you join two tables from different databases. The metadata can give back table and column names for the columns in the join. But MySQL does not pass along the database name in the result set.

You could use aliases something like this.

select  a.field_1 AS db_1_field_1, 
        a.field_2 AS db_1_field_2, 
        b.field_3 AS db_2_field_3
  from  db_1.table_1 a 
  join  db_2.table_2 b ON a.field_9 = b.field_9

This has the effect of annotating your column names with the source database. (But it's a hack.)

Upvotes: 1

Related Questions