Ok, this question is related to mysql and php.,
Let me state the current situation
I have a db, say "gd".
That db has several tables all with same columns.(i.e same fields for different manufacturers such as product name, cost , stock etc.)
Now i want to know how many tables have the product 'a' in them and what's the cost of 'a' in each of those table.(tables are generated dynamically so I'll never now how many tables are there, well ofcourse i can refer information schema, but just wanted to highlight this fact). So is there a way to achieve this?.. excluding "dump the whole data and then search it" solution.
Plz help, .. sorry for my bad English, .

More than one 😬

You can get all tables that has column "product_name" from information_Schema.columns

SELECT DISTINCT TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME IN ('product_name')

AND TABLE_SCHEMA='YOUR_DB';

Then you have to run a cursor on these tables so you select one, each time:

SELECT *

FROM OneTable

WHERE product_name LIKE '%XYZ%'

The results should be entered into a 3rd table or view, take a look at this: https://stackoverflow.com/questions...

Notice: This can work only if the structure of all table is similar, otherwise you will have to see which columns are united for all these tables and create your result table / View to contain only these columns.

You can check more info on cursors here: http://dev.mysql.com/doc/refman/...

Im not sure what you're trying to accomplish.

Sounds like you're building a multi-tenant app?
- but instead of multiple identical databases, you're generating tables ad-hoc.

If that's the case, then you could store the generated table names in a "clients" and pivot join through. :)

@HelloBaze oh okay, but one more change, i have a composite primary key (which is similar too in all tables) based on which i want my results,

@lotd unfortunately it's a lil more complicated than that and i won't be able to explain it all on text. But i can't pivot join them,.😅

@spdhiraj99 as I've told you in my answer if the tables contain similar columns there should be no problem, no sure tho what you mean by the primary keys.

@HelloBaze in your where clause, u check for product name alone, but what if there exists two products with same name but their category is entirely different, for example audi can either be a car brand name or a shop's name, so in our case i have to retrieve the cost only if they share same name and same category, this name and category have been set to primary key

@spdhiraj99 That where clause was just to get all tables that have a column "product_name", you can remove it if all of your tables contains "product_name" or whichever column you need.

@HelloBaze oh okay, i understand what u r doing, thanks for the info