Jump to content

Recommended Posts

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.

Link to comment
https://forums.phpfreaks.com/topic/251386-memory-issues/
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/251386-memory-issues/#findComment-1289456
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.