Loop through all results in table and find out which ones are appropriate

[mds1256]

Hi

 

I am working on a little hobby and it requires a script to go through all results in a table (potentially 10's of thousands) and get the id of all rows that are relevant to that query. So for example get all ID's where the category is set to 'Computers'.

 

Once it has retrieved all the ID's for the suitable category it then needs to insert some data into a different table, e.g. the ID that it has previously retrieved along with some details (the same details for each ID, so many rows with the same details but the ID being different).

 

Now, which is the best way to achieve this. Should I do this in a PHP script, shell script or a database stored procedure?

 

Which way would be faster as I believe this could take a long time or am I wrong.

 

When a user submits a form that will insert these details into one table and then kick off a background task (as waiting for the above to run will take a while or am I wrong).

[Barand]

Use an INSERT query. I just experimented and 9000 records selected and inserted took 0.1 seconds.

 

INSERT INTO newtable (id, a, B)
SELECT id, 1, 2
FROM oldtable
WHERE category = 16;

[Barand]

However, instead of selecting all the ids for a category then writing the same info + id to another table (1000s of records) it would be far better to hold that category related info in the category table where you need only store it once.

[mds1256]

Hi Thanks!

 

So doesnt seem to take too long then.

 

Yeah I see what you mean by normalising the data as I have the data in another table any way, I guess just add the id of the row in the other table.

 

Thanks very much

[Barand]

I guess just add the id of the row in the other table.

 

 

Plus the category id