Page 1 of 1

inserting data from one table to another

Posted: Tue Jan 13, 2009 7:08 am
by russ72g
hello. in table "a" i have 100 rows of data. i want to take 10 of these rows at random (this part i can do) and put some of the data into table "b". my problem is if i do the while loop for going through the 10 rows from "a" ...... while($row = mysql_fetch_array($result)) ....... then how can i upload data from certain fields to "b" in the same while loop. it means not having finished with one $result="mysql_query" (for data retrieval from "a" before opening another for data insertion to "b", thus canceling the first. or am i misunderstanding that?

or is there a different way to do this? i thought about a way to convert the "a" data into variables but then i ran into problems with variables inside variables. if one field is called field_1 for example, i would need the variables to be called field_1_1, field_1_2, field_1_3 etc etc.

can anyone help?

p.s. it possibly doesn't complicate things but it's not always as stated above 10 results i need. the figure 10 is a variable called $maxwords and can be 5, 10, 20, 30 or 40.

Re: inserting data from one table to another

Posted: Tue Jan 13, 2009 8:07 am
by VladSun
No PHP needed (almost):
http://dev.mysql.com/doc/refman/5.0/en/ ... elect.html

[sql] INSERT INTO     table_b (field1, field2, ...)SELECT     {your SELECT query here} [/sql]

By the way - how do you get the random rows - it could be done in DB layer and no PHP involved.

Re: inserting data from one table to another

Posted: Tue Jan 13, 2009 8:52 am
by russ72g
ok, i see. i actually found a way round it with arrays. making an array for each set of field data from table_a and then "insert into" table_b using the array, that way the query was not needed.

you asked about random results. here's how i did that:

SELECT * FROM $usertable_a WHERE (group1 ='$group' OR group2 ='$group' OR group3 ='$group') ORDER BY RAND() LIMIT $maxwords"

so when a user enters 10 for $maxwords i get 10 random row results.

many thanks again.

Re: inserting data from one table to another

Posted: Tue Jan 13, 2009 12:28 pm
by VladSun
russ72g wrote:ok, i see. i actually found a way round it with arrays. making an array for each set of field data from table_a and then "insert into" table_b using the array, that way the query was not needed.
Using "PHP side" arrays is slower than using a single query to do all ;)

Your "randomize" query is OK - that was exactly what I meant.