While you can always fulfill tasks such as creating a new table out of existing ones in MySQL by the help of PHP, it’d be much faster and way more efficient with native SQL queries.
Consider when you need to combine a few tables into one, more precisely, you need just certain fields / columns from each of the table and then piece them together into a new one. You can write a PHP script to do that: 1) read the original records from the tables, 2) insert them one by one with a loop. However the insanely better way is to utilize INSERT INTO … SELECT … FROM … a little more sophisticated as:
INSERT INTO new_table (col1, col2, col3) SELECT old_table_1.col2 as col1, old_table_2.col3 as col2, old_table_3.col1 as col3 FROM old_table_1, old_table_2, old_table_3You should also read:
- SQL: INSERT INTO … SELECT … to generate or combine records from existing ones and insert into an existing table
- Using SQL to Find records existing in one table but not in another
- SQL: CREATE TABLE … SELECT … to generate new table from existing tables
- SQL: Randomly Shuffle Rows or Records – Reorder them in a random order
- MySQL: Check if a table exists
Facebook
Twitter
Google Plus
{ 5 comments… read them below or add one }
This looks so promising, but for me this syntax results in my two 362 row tables creating a 362×362 (131,000+ rows!) table.
My syntax differs from your example only in that I’m creating 5 columns from 2 different tables. An ID field is the key so I can pull it from either of my two tables. If I pull 4 fields from one table, the new_table is created with the expected 362. If I add the 5th field from old_table_2, it adds 362 rows for every existing row. I get the same 362×362 tables if I only pull 3 columns from old_table_1 and 2 columns from old_table2 so the exponentiality seems to occur once per extra table pulled into the query.
Is there a slight tweak that would fix this problem? (I’m running MySQL 4.1)
Perhaps with a conditional clause of “WHERE old_table_1.ID = old_table_2.ID”?
That’s it! Just needed to let SQL know that two fields were common between tables. Thanks kindly! :)
No problem. Glad it helped!
Thanks a lot for the donation. Very kind of you, Chris. :)