PDO not closing database

Question

I have having some small problems with a PDO database connection remaining connected.

The code i am running is making some automated posts to twitter. I run the code via CRON once an hour, but to save posting all messages to twitter in one go, i user sleep() after each loop.

This (sleep()) is resulting in keeping the database connection open until the script finished running. To try and stop this, i have added in variouse methods to "force close" the connection, but none of them seem to be working. The connection is not required once the results are returned.

Below is the code i am using (simplified a little for this post)

$escort_obj = new MLE_Escort();
if($result = $escort_obj->getRandomEscortsSearch($limit = 5)){

    foreach ($result AS $row) {

        // set the data we need //

        // assign image
        $image = '..'.$row->PhotoURL;

        // build tags
        $tags = '#Escorts ';
        if(!empty($row->InCallLocation)) {
            $tags .=  '#'.str_replace(' ', '', $row->InCallLocation).' ';
        }

        // join the data into the $tweet
        $tweet =  strip_tags($row->Description).' '.$tags;

        // call function to post to twitter
        postToTwitter($image, $tweet, $tmhOAuth);

        sleep(500); // sleep a while
    }
}

Class file (extract) with query

    /*
     * get random profiles for posting to twitter
     * @return result of the mySQL query
     */
    public function getRandomEscortsSearch($limit)
    {
        try
        {

            $query = "SELECT 
                        e.EscortID,
                        e.EscortName,
                        e.DateModified,         
                        LEFT(e.InCallLocation, 25) AS InCallLocation,
                        e.InCallLocation AS FULLInCallLocation,
                        e.HairColour,
                        LEFT(e.EscortProfile, 85) AS Description,
                        p.PhotoURL
                        FROM tEscort e
                        INNER JOIN (tEscortPhoto ep INNER JOIN tPhoto p ON (ep.tPhoto_PhotoID = p.PhotoID AND p.Enabled=1)) 
                            ON e.EscortID = ep.tEscort_EscortID AND ep.ProfilePhoto = 1
                        INNER JOIN tUser u ON u.UserName = e.PrivateEmail
                        INNER JOIN tmembers m ON m.tUser_UserID = u.UserID
                        WHERE e.Enabled = 1
                        AND e.Active = 1
                        AND m.tMemberStatuses_MemberStatusID = 2
                        AND e.tEscortMembership_MembershipID != 6
                        ORDER BY rand()
                        LIMIT ?";       
            $stmt = $this->conn->prepare($query);
            $stmt->execute(array($limit));
            $result = $stmt->FetchAll(PDO::FETCH_OBJ);
            $this->conn = NULL; // force close DB
            $stmt = NULL; // empty $stmt
            $dbPDO = NULL; // force close this too in case it was open
            return $result; // return reults
        }
        catch (PDOException $ex) {
            // do some stuff (removed to keep this question clean)
            return false;
        }
    }

Any advice on how i can do what i want but also close the connection once the query has ran, would be much appreciated ! Thanks!

Answer 1

This isn't that serious if you don't. Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

http://php.net/manual/en/pdo.connections.php

So the answer is no, you don't need to do anything unless you need to explicitly close the connection during the script execution for whatever reason, in which case just set your PDO object to null.