Memory Issues...

[Ninjakreborn]

This does not make any sense...

On the backend, I run the following script

SELECT * 
FROM jp_transaction_history
WHERE transaction_type =  'ss'
AND order_date
BETWEEN  '2011-11-01'
AND  '2011-11-04'
LIMIT 30 , 621

 

In PHPMYAdmin with no issues.

 

On the front end...I can't get the query to run right.

<?php
require_once('functions.php');

$account_types = array('first', 'second');

$start_date = '2011-11-01';
$end_date   = '2011-11-04';

foreach($account_types as $key_account => $value_account) {
if ($value_account == 'ss') {
	$sql = "SELECT * FROM jp_transaction_history WHERE transaction_type = 'ss' AND order_date BETWEEN '" . $start_date . "' AND '" . $end_date . "'";
}else {
	$sql = "SELECT * FROM jp_transaction_history WHERE transaction_type = 'fp' AND order_date BETWEEN '" . $start_date . "' AND '" . $end_date . "'";
}
echo $sql;
$query = mysql_query($sql);
echo mysql_num_rows($query);

$sales_data = array();

while ($row = mysql_fetch_object($query)) {	

	$action = unserialize($row->actions);
	$amount = $action[0]['amount'];

	if ($amount >= 1.95 && $amount <= 5.95) {
		// Extract dates from order ID
		$pointer = $row->order_date;
		// Setup defaults
		if (empty($sales_data[$pointer]['sent'])) {
			$sales_data[$pointer]['sent']       = 0;
		}
		if (empty($sales_data[$pointer]['declined'])) {
			$sales_data[$pointer]['declined']   = 0;		
		}
		if (empty($sales_data[$pointer]['authorized'])) {
			$sales_data[$pointer]['authorized'] = 0;	
		}
		if (empty($sales_data[$pointer]['qc_fail'])) {
			$sales_data[$pointer]['qc_fail']    = 0;
		}
		if (empty($sales_data[$pointer]['pp_cancels'])) {
			$sales_data[$pointer]['pp_cancels'] = 0;
		}
		if (empty($sales_data[$pointer]['apprd'])) {
			$sales_data[$pointer]['apprd'] = 0;
		}
		if (empty($sales_data[$pointer]['captured'])) {
			$sales_data[$pointer]['captured'] = 0;
		}

		// Populate values
		$sales_data[$pointer]['sent']++;
		$sales_data[$pointer]['authorized']++;
		$sales_data[$pointer]['apprd']++;

		if ($row->condition == 'complete') {
			$sales_data[$pointer]['captured']++;
		}else if ($row->condition == 'failed') {
			$sales_data[$pointer]['declined']++;
			$sales_data[$pointer]['authorized']--;
			$sales_data[$pointer]['apprd']--;
		}else if ($row->condition == 'canceled') {
			// Check second condition for PP Cancellations.
			$at_capture  = 0;
			$at_complete = 0;
			$at_void     = 0;

			foreach ($action as $k_capture=>$v_capture) {
				if ($v_capture['action_type'] == 'capture') {
					$at_capture++;
				}else if ($v_capture['action_type'] == 'complete') {
					$at_complete++;
				}else if ($v_capture['action_type'] == 'void') {
					$at_void++;
				}
			}

			if ($at_capture == 0 && $at_complete == 0 && $at_void == 1) {					
				$sql2 = "SELECT * FROM callcenter WHERE orderid = '" . $row->order_id . "'";
				$query2 = mysql_query($sql2);
				while ($row2 = mysql_fetch_array($query2, MYSQL_ASSOC)) {
					if ($value_account == 'ss') {
						if ($row2['status'] == 1) {
							$sales_data[$pointer]['pp_cancels']++;
							$sales_data[$pointer]['apprd']--;
							$temp_ppcancel = 1;
						}
					}else {
						if ($row2['second_status'] == 1) {
							$sales_data[$pointer]['pp_cancels']++;
							$sales_data[$pointer]['apprd']--;
							$temp_ppcancel = 1;
						}						
					} // End value account if
				} // End while loop for row 2
			} // End if for at capture
		}// End else if for row condition

		// Quality Assurance
		if ($temp_ppcancel != 1) {
			$sql3 = "SELECT * FROM callcenter WHERE orderid = '" . $value->order_id . "'";
			$query3 = mysql_query($sql3);
			while ($row2 = mysql_fetch_array($query3, MYSQL_ASSOC)) {
				if ($value_account == 'ss') {
					if ($row2['status'] != 1) {
						$sales_data[$pointer]['qc_fail']++;
						$sales_data[$pointer]['apprd']--;
					}
				}else {
					if ($row2['second_status'] != 1) {
						$sales_data[$pointer]['qc_fail']++;
						$sales_data[$pointer]['apprd']--;
					}
				}
			}
		}
		unset($temp_ppcancel);
	}
}

// Sort Values
ksort($sales_data);
?>
<table class="tablesorter">
<thead>
<tr>
  	<th>DOWNLOAD</th>
  	<th>DATE</th>
    <th>SENT</th>
    <th>DCLND</th>
    <th>AUTHD</th>
    <th>PRE-PRD</th>
    <th>QCFAIL</th>  
    <th>APPRD</th>  
    <th>CAPTURED</th>
  </tr>
  </thead>
  <tbody>
<?php
foreach($sales_data as $key=>$value) {
echo '<tr>';
if ($value_account == 'ss') {
	echo '<td><a href="csv_download.php?type=ss&date=' . $key . '">Download CSV</a></td>';
}else {
	echo '<td><a href="csv_download.php?type=fp&date=' . $key . '">Download CSV</a></td>';		
}
echo '<td>' . $key                 . '</td>';
echo '<td>' . $value['sent']       . '</td>';
echo '<td>' . $value['declined']   . '</td>';
echo '<td>' . $value['authorized'] . '</td>';
echo '<td>' . $value['pp_cancels'] . '</td>';
echo '<td>' . $value['qc_fail']    . '</td>';
echo '<td>' . $value['apprd']      . '</td>';
echo '<td>' . $value['captured']   . '</td>';
echo '</tr>';
}
?>
</tbody>
</table>

<br /><hr /><br />
<?php	
}
?>

 

My code in PHP throws...

SELECT * FROM jp_transaction_history WHERE transaction_type = 'ss' AND order_date BETWEEN '2011-11-01' AND '2011-11-04'

Warning: mysql_query() [function.mysql-query]: Unable to save result set in /path/tofile on line 18

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /path/to/file on line 19

 

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /path/to/file on line 23

 

 

If I cut the days down to just 2...or 1, it works fine. But for some reason these number of records throw memory errors..I am only running 2 queries to get data..one returns like 600, and the other one, less than that.

 

Any feedback is appreciated, thanks.

[ManiacDan]

Googling for your error messages produces a few potential fixes

[QuickOldCar]

Basically you should try doing a repair on the table through phpmyadmin.

 

I feel you should also paginate your results on display. Less results per page.

I'm guessing you are outputting roughly 1,000 results on a single page.

 

You could also try raising your memory limit in php.ini, but that's not the better solution.