Cutting down memory usage

[jlm699]

Greetings all,
I'm using a PHP-based web page to access an SQL database containing logs which has grown quite large over time. A few weeks ago I recieved the following error:
"Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 11 bytes) in /location_of_script/database.php on line 75"

Now I know basically what's causing this problem I'm just not sure exactly how I should go about fixing it... See after I got this error I went into php.ini and changed the memory_limit from 128M to 256M (the max amt of memory a script may consume). I did this so that I could still access the database via the webpage, but I know that as the database continues to grow this temporary fix will eventually break too.

My database.php script is as follows...

<?php
class Database
{
var $database_name;
var $database_user;
var $database_pass;
var $database_host;
var $database_link;
// This associative array will be used for Table headings in display()
var $key_lookup = array("rn"=>"Road Number", "date_time"=>"Date & Time", "date"=>"Date","time"=>"Time", "usr"=>"User", "sw_ver"=>"Software Version");
 
function Database($name)
{
    if (strlen($name)) $this->database_name = $name;
    else $this->database_name = "Testing";
    $this->database_user = "user";
    $this->database_pass = "passwd";
    $this->database_host = "localhost";
}
 
function changeName($name) { $tihs->database_name = $name; }
function changeUser($user) { $this->database_user = $user; }
function changePass($pass) { $this->database_pass = $pass; }
function changeHost($host) { $this->database_host = $host; }
 
function changeOpts($user, $pass, $host, $name)
{
    $this->database_name = $user;
    $this->database_user = $user;
    $this->database_pass = $pass;
    $this->database_host = $host;
}
 
function connect()
{
    $connStr = "host=" . $this->database_host .
        " dbname=" . $this->database_name .
        " user=" . $this->database_user    .
        " password=" . $this->database_pass;
    $this->database_link = pg_connect($connStr) 
        or die('Could not connect: ' . pg_last_error());
}
 
function disconnect()
{
    if (isset($this->database_link)) pg_close($this->database_link);
    else pg_close();
}
 
/*    This function will allow us to run queries that produce no output
      such as INSERT, DROP, UPDATE, etc.
    We first need to make sure there is a valid connection however.    */
function iquery($qry)
{
    if(!isset($this->database_link)) $this->connect();
    $temp = pg_query($this->database_link, $qry)
        or die("Error: " . pg_last_error());
}
 
/*    This function will allow us to run queries that do produce output
      (ie, SELECT)
    We first must verify that we are connected to a database.    */
function query($qry)
{
    if(!isset($this->database_link)) $this->connect();
    $result = pg_query($this->database_link, $qry)
        or die("Error: " . pg_last_error());
    $returnArray = array();
    $i=0;
    while ($row = pg_fetch_array($result, NULL, PGSQL_BOTH))
        if ($row)
            $returnArray[$i++]=$row;
    pg_free_result($result);
 
    return $returnArray;
}
 
/*    This function allows us to display any and all types of queries,
      including cross-queries.  The only complication arises when
      querying the snapshot table, which stores the snapshot as a
      large chunk of raw text.  Special processing is required    */
function display($results)
{
    $keys = array_filter(array_keys($results[0]), is_string);
    echo '<table border=1 cellpadding=2>';
    echo '<tr>';
    foreach($keys as $key) {
        echo '<th>';
        if (array_key_exists($key, $this->key_lookup))
            echo $this->key_lookup[$key];
        else
            echo $key;
        echo '</th>';
    }
    echo '</tr>';
    foreach($results as $result)
    {
        echo '<tr>';
        foreach($keys as $key) {
            if (!strcmp($key, "rn") or !strcmp($key, "name") or !strcmp($key, "usr") or
                !strcmp($key, "log_msg") or !strcmp($key, "type"))
                echo '<td nowrap>';
            else
                echo '<td nowrap align=right>';
            /* In the case of snapshot log we must process for readability */
            if (!strcmp($key, "text")) {
                echo '<table border=1 bordercolor=gray>';
                echo '<tr>';
                $text=explode("\n", $result[$key]);
                for ($cnt = 0; $cnt < count($text); $cnt += 1) {
                    $text[$cnt] = explode(" ", $text[$cnt]);
                }
                foreach($text as $line) {
                    foreach($line as $word) {
                        if (strcmp(substr($word,-1), ',')) {
                            echo '<td nowrap align=right>'.$word;
                            echo '</td></tr><tr>';
                        }
                        else {
                            echo '<td nowrap align=right>';
                            echo substr($word,0,-1).'</td>';
                        }
                    }
                }
                echo '</table>';
            }
            else
                echo $result[$key];
            echo '</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
}
}
?>
 

So I assume that in order to cut down on the memory size that each query() call consumes I need to generate the html table as I'm recieving the data... does anybody have an idea of how I would go about this?

Any help would be appreciated

[code green]

This is not unusual. You may have to reconsider how you want to handle the data.
This line is probably the most memory intensive, (but it may not fail here)
[PHP]while ($row = pg_fetch_array($result, NULL, PGSQL_BOTH))
if ($row)
$returnArray[$i++]=$row;[/PHP]By the way, the if() condition is simply duplicating the while() condition here.
You are collecting the results in one big array. Nothing wrong with that, I have a similar class.
When my scripts cause memory problems I submit multiple queries, process the data then concatenate the output into one big HTML string.
At the moment you have 2-3 arrays handling a lot of data.
Suprisingly, using the big string idea consumes less memory.

[jlm699]

This is not unusual. You may have to reconsider how you want to handle the data.
This line is probably the most memory intensive, (but it may not fail here)
[PHP]while ($row = pg_fetch_array($result, NULL, PGSQL_BOTH))
if ($row)
$returnArray[$i++]=$row;[/PHP]By the way, the if() condition is simply duplicating the while() condition here.
You are collecting the results in one big array. Nothing wrong with that, I have a similar class.
When my scripts cause memory problems I submit multiple queries, process the data then concatenate the output into one big HTML string.
At the moment you have 2-3 arrays handling a lot of data.
Suprisingly, using the big string idea consumes less memory.

You're right... I went ahead and merged the above function with the display() function. In this solution, instead of adding onto the returnArray I simply pump the data to output. This has greatly cut down on the memory usage of this script.

Thanks!