On Thu, Oct 27, 2011 at 8:56 PM, tamouse mailing lists
<tamouse.lists@xxxxxxxxx> wrote:
> On Thu, Oct 27, 2011 at 6:59 PM, <php@xxxxxxxxxxxxx> wrote:
>> Running PHP 5.3.5 on FreeBSD 8.2 connecting to a MySQL 5.1.55
>> server.
>>
>> Why does this code (below) run out of memory? It queries
>> test_table for all rows, all fields and sorts them by the numeric
>> 'contract' field. It then iterates through the rows, and tallies
>> the number of rows per contract. That's not the end goal of the
>> processing, but the processing does not require storing multiple
>> rows in memory (except in the MySQL result buffer). One row at a
>> time in memory is all I need.
>>
>> The schema is not huge:
>>
>> +--------------+-----------------+------+-----+---------+-------+
>> | Field | Type | Null | Key | Default | Extra |
>> +--------------+-----------------+------+-----+---------+-------+
>> | contract | int(11) | YES | | NULL | |
>> | A | int(8) unsigned | NO | | 0 | |
>> | B | datetime | YES | | NULL | |
>> | C | int(8) unsigned | YES | | 0 | |
>> | D | char(8) | YES | | NULL | |
>> | E | char(8) | YES | | 0000 | |
>> | F | int(4) | YES | | 0 | |
>> | G | int(1) | YES | | 0 | |
>> | H | char(8) | YES | | 00:00 | |
>> | I | varchar(100) | YES | | XXX | |
>> +--------------+-----------------+------+-----+---------+-------+
>>
>>
>> <?php
>>
>> require_once( '../include/mysql_funcs.php' );
>>
>> $db_host = $test_db_host;
>> $db_user = $test_db_user;
>> $db_name = $test_db_name;
>> $db_pwd = $test_db_pwd;
>>
>> if (!($db_conn = mysql_connect( $db_host, $db_user, $db_pwd )))
>> die( "Can't connect to MySQL server\n" );
>>
>> if (!mysql_select_db( $db_name, $db_conn ))
>> die( "Can't connect to database $db_name\n" );
>>
>> $qry = "select * from test_table order by contract";
>>
>> if ($result = mysql_query( $qry, $db_conn )) {
>>
>> $row = mysql_fetch_array( $result );
>> while ($row) {
>> $c = $row[ 'contract' ];
>> $n = 0;
>> while ($row && ($c == $row[ 'contract' ])) {
>> $n++;
>> $row = mysql_fetch_array( $result );
>> }
>> echo sprintf( "|%13d |%7d |\n", $c, $n );
>> } // while
>>
>> } else {
>>
>> die( mysql_error() . "\n" );
>>
>> }
>>
>> ?>
>>
>>
>> The output ends with:
>> ...
>> | 39582 | 518 |
>> | 39583 | 384 |
>> | 39584 | 429 |
>> | 39585 | 433 |
>> | 39586 | 359 |
>> PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 20 bytes) in xx2.php on line 26
>>
>> Line 26 seems to be the innermost "mysql_fetch_array" call.
>>
>> The sum of the right-hand column for all the output produced
>> prior to running out of memory is 274547. There are 295287 total
>> rows in the table.
>>
>> What can I change to prevent this code from running out of memory
>> even with an arbitrarily large number of rows returned from the
>> 'select' query?
>>
>> Thank you for your time!
>>
>>
>>
>> --
>> PHP Database Mailing List (http://www.php.net/)
>> To unsubscribe, visit: http://www.php.net/unsub.php
>>
>>
>
> I don't use the mysql functions, but rather the mysqli functions,
> where you have to free resources (query results) when you're done with
> them. Do you have to do the same thing with msql ?
>
No, sorry, forget that, I think I see what's going on now.
Your inner loop is unnecessary. Instead, try:
$contracts = array();
while ($row = mysql_fetch_assoc($result) {
$contracts[$row['contract']]++;
}
foreach ($contracts as $contract => $count) {
echo sprintf( "|%13d |%7d |\n", $contract, $count );
}
That said, I think there must be a way to do this in SQL.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
