Ninjakreborn Posted November 18, 2011 Share Posted November 18, 2011 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. Quote Link to comment Share on other sites More sharing options...
ManiacDan Posted November 18, 2011 Share Posted November 18, 2011 Googling for your error messages produces a few potential fixes Quote Link to comment Share on other sites More sharing options...
QuickOldCar Posted November 19, 2011 Share Posted November 19, 2011 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.