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_3
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. 🙂
I’m trying to combine user added data from php along wtih data from an existing table into an existing table if a criteria is met. . . for example:
I have parts 100-110 in box 20
I have table “Current_Box” with columns ID, timestamp,serial number, and box number
I have another table “Shipped” with columns ID, timestamp, serial number, box number and tracking number
The tracking number is a user input from a php form along with a user specified box number.
I’m trying to insert into table “Shipped” the 10 serial numbers in box 20 along with the box number and tracking number. My existing query is as follows:
INSERT INTO Shipped (”,now(),’SN’,’BN’,’$TN’) SELECT Current_Box.SN as SN, Current_Box.BN as BN FROM Current_Box WHERE Current_Box.BN = ‘$BN’