PHP Script to populate one table with data from others

[markos]

Hi,
I've got a table with about 10,000 rows in it.

I need to go through each row, manipulate the data, and then insert it into another table.

I'm simply doing the typical:


$query = "SELECT * FROM table";
$result = mysql_query($query,$db);
while ($myrow = mysql_fetch_array($result)) {

(Process it and insert it into the new table)

}


and reading all the rows in the $myrow array, then going through each one and INSERTing it into the new table.

Problem is that it's taking around 10 minutes to run (browser page goes white and won't flush() anything to the screen during this time).

Note I'm running it on a local installation of Appache/mysql/php on XP, and yes I've really had to whack up the timeout values.

Am I doing something intrinsically wrong? Should I be tackling this in an entirely different manner?

Your advice appreciated!

[chazzy]

why don't you just write a single query that does the whole thing?


INSERT INTO someOtherTable
select colA,colB, etc from firstTable


What exactly does "process it" do and is it something that is easily rewritten as SQL? Do you have access to stored procedures?

[markos]

There's a lot of processing involved (some 400 lines of PHP), e.g. EAN and UPC barcodes are formed, certain criteria determines if other tables are accessed for additional info, basically a fair bit before I can output to the other table. I'm not as familar with SQL other than the basic "insert into" and "select * from" etc. Stored-procedures... I'll have a look at these - they're not something I've used in the past?

[markos]

bump! just wondered if a fresh set of eyes might help me with this issue - which still exists!