SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Need help with a MySQL-problem (PHP)

    I'll try to explain my problem as good as possible.

    I'm writing a forum software using PHP/MySQL. I want to add a prune feature for administrators and this is where I'm stuck. I let the administrator enter how old threads have to be to be pruned and then I have to do the actual pruning.

    I have a table called messages. In this table both regular messages and messages that are topics exist. The rows in this table have a field called parentid and for topics this field is 0 and for regular messages it is the id of the topic in which they reside. The table also has a field called lastpost which is NULL for all regular messages, but is set for topics. Whenever someone posts to a thread I update the lastpost in the "parent message". This way, selecting the threads that are to be pruned is easy.

    SELECT id FROM messages WHERE lastpost < (UNIX_TIMESTAMP()-$pruneTime)

    This way I get a nice PHP array with the id's of the threads that are to be pruned, but this is as far as I get.

    I browsed the MySQL documentation and ran across subselects and considered using somethins like this:

    DELETE FROM messages WHERE id IN( <implode the array here (comma separated)> )

    and then something similar for the messages within those threads

    DELETE FROM messages WHERE parentid IN( <implode the array here (comma separated)> )

    (could these two queries be merged into one?)

    but then I realised that if there are a LOT of messages in the database the query would be very, very long (we're talking megabytes). I also considered the fact that this operation could be very demanding for the database and that it perhaps would "tie up" the forum completely for some time. Perhaps even so demanding that the PHP script would time out.

    Do you guys have any idea how this could be done in a smart way?

  2. #2
    SitePoint Evangelist
    Join Date
    Jul 2001
    Location
    UK
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Suppose you could try DELETE LOW_PRIORITY FROM. That would make the query wait a while (until other queries are complete).

    You could also split the array into chunks, and add something like an OR into the query to combine the two originals.

  3. #3
    SitePoint Member
    Join Date
    Jan 2002
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for you response. I split it up a bit. I don't know how the speed is since I only have a few hundred posts to try it on (on these posts it's instant though). This is the way I do it now. Any recommendations?

    PHP Code:
        // Set the board in maintenance mode
        
    mysql_query"UPDATE options SET maintenance='yes'" ) or errormysql_errno(), __LINE__mysql_error() );
        
        
    // Select the thread ID's (parentid=0)
        
    $result mysql_query'SELECT id FROM messages WHERE parentid=0 and lastpost < UNIX_TIMESTAMP()-('.intval$pruneseconds ).')' ) or errormysql_errno(), __LINE__mysql_error() );
        
    $numthreads mysql_num_rows$result );
        
    $threadcount 0;

        if( 
    $numthreads )
        {
            do
            {
                
    $threads NULL;
                for( 
    $tempcount 0$threadcount $numthreads$threadcount++ )
                {
                    if( 
    $tempcount == )    // We only do 8 threads at a time. Otherwise the query would be "too long".
                        
    break;
                    
    $threads[] = mysql_result$result$threadcount );
                    
    $tempcount++;
                }
        
                if( 
    $threads == NULL )    // We're done
                    
    break;

                
    $threadstring implode', '$threads );
                
    mysql_query'DELETE FROM messages WHERE id IN( '.$threadstring.' ) OR parentid IN( '.$threadstring.' )' ) or errormysql_errno(), __LINE__mysql_error() );
        
            } while( 
    mysql_affected_rows() > );

            
    mysql_query'OPTIMIZE TABLE messages' ) or errormysql_errno(), __LINE__mysql_error() );
        }
        else
            echo 
    'nothing to prune';

        
    mysql_query"UPDATE options SET maintenance='no'" ) or errormysql_errno(), __LINE__mysql_error() ); 
    Last edited by Kennel; Jan 24, 2002 at 19:38.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •