inserting data from one table to another

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
russ72g
Forum Newbie
Posts: 8
Joined: Tue Jan 13, 2009 2:55 am

inserting data from one table to another

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: inserting data from one table to another

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
russ72g
Forum Newbie
Posts: 8
Joined: Tue Jan 13, 2009 2:55 am

Re: inserting data from one table to another

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: inserting data from one table to another

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply