CODEWITHSUNDEEP
phpmysql
user1589406
user1589406

Reputation: 11

Insert table names into column of another table

I know this question has been answered before, but I am sorry I am not a coder, so I don't know what goes where.

I have a list of 100+ quizzes, all of which are in separate tables (id, question, answer). Now, what I want to do is to insert names of all tables into a column (quizname) of a table named 'quiz' which is sort of a master index of all the quizzes.

Next, I also want to insert questions and answers from each table into a master table containing all questions and answers (structure: id, quizid,question,answer).

I am trying to develop a quiz site but I am having to do all by myself. It is a long story. If I can get some help from the Stack Overflow community, it would be great.

I know I am a noobie so there will be snark, scold...etc... from experienced coders but I just need to get this done!

Thanks

Upvotes: 0

Views: 136

Answers (1)

Jakob Egger
Jakob Egger

Reputation: 12041

You can get a list of table names in the current database using the command

SHOW TABLES;

Alternatively, you can query the information_schema tables, for example:

SELECT table_name FROM information_schema WHERE table_schema = 'your-database-name';

To create a table with table names, use a query like the following:

CREATE TABLE quiz AS SELECT table_name AS quizname FROM information_schema.tables WHERE table_schema = 'test';

However, funnily enough, this will include the newly created table 'quiz'...

I can't think of a way to do the last part purely in MySQL, you will have to write a PHP script that reads the quiz table and then performs a lot of queries like this:

INSERT INTO master_table(quizid, question, answer) SELECT 'quiz1', question, answer FROM quiz1
INSERT INTO master_table(quizid, question, answer) SELECT 'quiz2', question, answer FROM quiz2
...

Upvotes: 1

Related Questions