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
