Thanks alot. You really gave me some good ideas. Lordo "Richard Lynch" <ceo@xxxxxxxxx> wrote in message news:1428.66.99.91.45.1102012231.squirrel@xxxxxxxxxxxxxxxx > news.php.net wrote: > > I have 160,000+ members in my site. 40,000 of them have not logged in > > since > > 2003 and they should be deleted. A member is not deleted from only one > > table. Based on user id, he should be deleted from 5 tables and also his > > photo, if any, should be unlink(ed). > > > > I tried to do that 10 by 10 using: > > Ah. Don't do that. > > Do this: > > $query = "delete from table1 where userid in (select userid from > login_table where last_login <= '2003-12-31')"; > > ANY time you are using PHP to loop through record after record after > record in the database, and then you are sending a new query for every > record you find, you DOING IT WRONG. :-) > > SQL is *VERY* good at describing exactly which records should have > something done to them, and doing it, or finding them, or whatever. > > PHP is not so fast at that. > > Oooooh. If your version of MySQL doesn't do sub-selects, you'll want to do: > > $query = "select userid from login_table where last_login <= '2003-12-31'"; > $goners = mysql_query($query, $link) or trigger_error(@mysql_error($link) > . " $query", E_USER_ERROR); > $ids = array(); > while (list($userid) = @mysql_fetch_row($goners)){ > $ids[] = $userid; > } > $ids_sql = implode(", ", $ids); > > $query = "delete from table1 where userid in ($ids_sql)"; > mysql_query($query, $link) or trigger_error(@mysql_error($link) . " > $query", E_USER_ERROR); > > You can repeat that for each table. > > If it turns out that having 40K IDs in the array/string is too much, just > add a LIMIT clause to the first query: > > $query = "select userid from login_table where last_login <= '2003-12-31' > limit 100"; > > You'll have to reload the page 40 times. Or, better yet, once you're > comfy with the page working for 100 peeps, just wrap a for($i = 0; $i < > 40; $i++) around the whole script. > > Needless to say, if you *DO* use the sub-select, you'll have to delete the > records from the table that keeps track of last_login *LAST* :-) > > You may also want to archive the 40,000 users somewhere, just in case... > > Or even put them into a "user_dormant" table or something, so you can pull > them back from the grave quickly if they want to re-activate their > account. > > -- > Like Music? > http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
