1

I am trying to SELECT id, description, title FROM table1, table2, table100

Say I get this working, is it better for me to just combine all my tables in phpmyadmin?

The problem is I have around 100 tables all of different categories of books so I want to keep them seperated in their individual tables.

I am trying to make a search engine that searches all the books in the entire database. All tables have the same column names.

So really all I really am trying to do is search the entire database's tables for an id, description, title. My search works, just I can only search 1 table and every solution online I have found only really works efficiantly with 2 or 3 tables.

Thanks in advance.

Marc Delisle
  • 8,879
  • 3
  • 29
  • 29
user3521712
  • 15
  • 3
  • 13
    Your design is bad I think. Put all of them into 1 table, and add a new column called `category`. Your life will be easier. – vaso123 Dec 06 '18 at 06:36
  • Yes, you'rr right that makes sense thank you. – user3521712 Dec 06 '18 at 06:37
  • Found this answer https://stackoverflow.com/questions/14931001/select-from-all-tables-mysql – skinnedpanda Dec 06 '18 at 06:45
  • thanks, that is the solution i tried but i found once completed it hung up around the third table which is why i thought it would be better as a single table so categorizing the tables into one table makes a lot of sense. my design is the problem so i will start again. – user3521712 Dec 06 '18 at 06:47

4 Answers4

1

The best is to redesign your database, everything into a single table with an additional "category" column.

in the meantime, you can create a view which union the tables with an additional column for the category.

DJo
  • 2,133
  • 4
  • 30
  • 46
0

I recommend keeping one table A with id, description, title, category and create another table B with categories. Table A has to have a foreign key with table categories. Then create a query to retrieve the books with a specific category.

Example:

SELECT id, description, title, category FROM books WHERE category = "drama"
Jadder Moya
  • 72
  • 4
0

I think it speaks to the database design itself as mentioned by most here. You've a few options depending on how much time you have on your hands:

(Short Term / Quick Fix) Central table with all your current fields plus category as a flag to differentiate between the current tables you have. So your insert will be something like "INSERT INTO newtable (ID,AssetID,ServiceID,Category) SELECT id, description, title, 'Fiction' FROM table1 ;"

If you tables are incrementally named like table1, table2 upto table100, you could then maybe write a quick php script that will iterate through the insert loop while incrementing on table on each iteration until the last table.

In the long run, you could invest in a json field that will house all your other data excluding keys that pertaining to a single entry

Code Chef
  • 82
  • 3
0

I recommend redesign the model and unifique this 100 tables to 1, and add a new column with category but integer value, not string value. In this way, you can index the category column with the other fields (id, description, title) for speed up the query. This resolution is more easy for avoid pain later.

Mauricio Kenny
  • 93
  • 6