What are the best practices for managing memory usage in PHP scripts that interact with databases like PostgreSQL and MySQL?

When interacting with databases like PostgreSQL and MySQL in PHP scripts, it is important to manage memory usage efficiently to prevent performance issues and potential crashes. One way to do this is by fetching data in chunks rather than loading the entire result set into memory at once. This can be achieved using techniques like pagination or limit-offset queries. 

// Connect to the database
$pdo = new PDO("mysql:host=localhost;dbname=mydatabase", "username", "password");

// Fetch data in chunks using a limit-offset query
$limit = 1000; // Number of rows to fetch at a time
$offset = 0; // Initial offset
while (true) {
    $stmt = $pdo->prepare("SELECT * FROM mytable LIMIT $limit OFFSET $offset");
    $stmt->execute();
    
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($rows)) {
        break;
    }
    
    // Process the fetched data
    
    $offset += $limit;
}

Keywords

PHP memory management PostgreSQL MySQL database interaction

Related Questions