mattyvx Posted November 2, 2009 Share Posted November 2, 2009 All, Currently I have a list of tables within my database, within these tables is information about site members. The last column of every table is called "Featured" and will either have a y or n value in it. What i want is to run a query on all my tables (new tables added daily) and return only records which are featured (marked by a y in the featured feild). I can get this to work for searching one table but i want ALL tables to be searched (without manually coding seperate queries for each table). Maybe something like.... [sELECT * FROM TABLES WHERE ["Featured"] = y] Thanks in advance! Quote Link to comment Share on other sites More sharing options...
taquitosensei Posted November 2, 2009 Share Posted November 2, 2009 select * from table1 where featured='y' UNION select * from table2 where featured='y' UNION select * from table3 where featured='y' etc.. etc.. Quote Link to comment Share on other sites More sharing options...
mattyvx Posted November 2, 2009 Author Share Posted November 2, 2009 thanks, i was hoping i wouldn't have to do that.... Is there no way to code in such a way that the number of tables is a variable? So just search EVERY TABLE within my databse "customers". Im basically after an automated script so that regardless of how many tables i have it searches them all. The number of tables is always increasing and id rather not have to amend the script every time i add a table. *Lazy i know, but id rather the code do the work for me * Quote Link to comment Share on other sites More sharing options...
seanlim Posted November 2, 2009 Share Posted November 2, 2009 Perhaps "SHOW TABLES", and then using PHP to code the MySQL query the way taquitosensei mentioned? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted November 2, 2009 Share Posted November 2, 2009 The number of tables is always increasingThat indicates a bad design, as you just found out, because it makes it difficult to find the same type of information in more than a single table. There is no table 'wild card' that you can put into a single query. You must either loop through all the tables or form a UNION query as has been shown in the posts above. A correct database design would put the same type of data into one table. Whatever value that is different between that data now that you are using to cause separate tables would simply become a value in a 'catagory' column in a single table. Quote Link to comment Share on other sites More sharing options...
mattyvx Posted November 3, 2009 Author Share Posted November 3, 2009 Thanks for your advice, i have changed my database setup and so many things became easier Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.