Hola a todos!
Mi problema es el siguiente, cuando exporto datos de mi base de datos a un archivo excel obtengo este error:
" Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 24 bytes) in C:\xampp\htdocs\yii\framework\db\CDbCommand.php on line 516[/b] "
He estado investigando y la solución es añadir la linea "memory_limit = 32M;", con los M que tu quieras, al archivo php.ini. Bien, lo que necesito es solucionar este problema pero sin usar "memory_limit = 32M;", hay alguna manera? Quizas alguna forma de no almacenar la informacion cuando se esta exportando y hacerlo directamente u optimizar el codigo. Este es el codigo que exporta:
public function actionExport()
{
function cleanData($str)
{
if($str == 't') $str = 'TRUE';
if($str == 'f') $str = 'FALSE';
/*if(preg_match("/^0/", $str) || preg_match("/^\+?\d{8,}$/", $str) || preg_match("/^\d{4}.\d{1,2}.\d{1,2}/", $str)) {
$str = "'$str";
}*/
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
$str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
return $str;
}
function formatPostalcode($postalcode) {
$return = preg_replace("/\D/","",$postalcode); //strreplace('-', '', strreplace(' ', '', trim($postalcode)));
if (strlen($return) == <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />
$return = substr($return, 0, 5) ."-". substr($return, 5, 3);
return $return;
}
if(isset($_POST['show']))
{
// Default values
$fromuserid = 0;
$touserid = 9999999999;
$show = 'all';
$email = false;
$price = false;
$coupon = false;
$coupononly = false;
$exist = false;
// Get form data
if ((isset($_POST['fromuserid']) && (is_numeric($_POST['fromuserid'])))) $fromuserid = $_POST['fromuserid'];
if ((isset($_POST['touserid']) && (is_numeric($_POST['touserid'])))) $touserid = $_POST['touserid'];
if (isset($_POST['show'])) $show = $_POST['show'];
if (isset($_POST['email'])) $email = $_POST['email'];
if (isset($_POST['price'])) $price = $_POST['price'];
if (isset($_POST['coupon'])) $coupon = $_POST['coupon'];
if (isset($_POST['coupononly'])) $coupononly = $_POST['coupononly'];
// Headlines
$contents = "Serial\tExpires\tPayment Date\tFirst Name\tLast Name\tLogradouro Number\tComplement\tBairro City\tEstate\tCEP";
// Include email, coupon code, price paid
if ($email) $contents .= "\tEmail";
if ($coupon) $contents .= "\tCoupon";
if (($price) && ($show == 'paid')) $contents .= "\tPrice";
$contents .= "\n";
$filename ="userdata-". date("d-m-Y") .".xls";
// Get users
if ($coupononly) {
$users = User::model()->findAll(array('order'=>'userid', 'condition'=>'language = "pt_br" AND userid >= '. $fromuserid .' AND userid <= '. $touserid .' AND length(couponcode) > 0'));
} else {
$users = User::model()->findAll(array('order'=>'userid', 'condition'=>'language = "pt_br" AND userid >= '. $fromuserid .' AND userid <= '. $touserid));
}
foreach($users as $user):
// Get payment
if ($show == 'pending') {
$payment = Payment::model()->findBySql('SELECT * FROM payment WHERE userid = '. $user->userid .' AND (status = "Pending") ORDER BY paymentid DESC LIMIT 1');
// Check for doublicate paid or free sign-ups by email if exporting pending
if (isset($payment)) {
$existcheck = Payment::model()->findBySql('SELECT payment.userid FROM user, payment WHERE user.email = "'. $user->email .'" AND user.userid <> '. $user->userid .' AND user.userid = payment.userid AND (payment.status = "Paid" OR payment.status = "Free") ORDER BY payment.paymentid DESC LIMIT 1');
if (isset($existcheck)) {
$exist = true;
} else {
$exist = false;
}
} else {
$exist = false;
}
} else {
$payment = Payment::model()->findBySql('SELECT * FROM payment WHERE userid = '. $user->userid .' AND (status = "Paid" OR status = "Free") ORDER BY paymentid DESC LIMIT 1');
}
$expires = '';
$paymentDate = '';
// Brazil date format
setlocale(LC_ALL, 'pt_BR');
if (isset($payment)) $expires = mb_strtoupper(strftime('%b-%y', strtotime($payment->expires)));
//here
if (isset($payment)) $paymentDate = mb_strtoupper(strftime('%d-%m-%Y', strtotime($payment->timemade)));
if ($user->userid <= 5304) $expires = 'NOV-12';
if (isset($payment)) {
if ($payment->status == 'Pending') $expires = 'NA';
}
if (($show == 'all') || (($show == 'pending') && (isset($payment)) && ($exist == false)) || (($show == 'paid') && (isset($payment)) && (($payment->status == 'Paid') || ($payment->status == 'Free')))) {
$contents .=
"No. ". Yii::app()->myformat->encSerial($user->userid) ."\t".
cleanData("Válido até ". $expires) ."\t".
//here
cleanData($paymentDate) ."\t".
mb_strtoupper(cleanData($user->firstname)) ."\t".
mb_strtoupper(cleanData($user->lastname)) ."\t".
mb_strtoupper(cleanData($user->address1 ." ". $user->address2)) .",\t".
mb_strtoupper(cleanData($user->address3)) .".\t".
mb_strtoupper(cleanData($user->address4 ." ". $user->city)) ." -\t".
mb_strtoupper(cleanData($user->state)) ."\t".
mb_strtoupper(cleanData(formatPostalcode($user->postalcode1))) ."\t";
// Include email, price paid
if ($email) $contents .= mb_strtoupper(cleanData($user->email)) ."\t";
if ($coupon) $contents .= mb_strtoupper(cleanData($user->couponcode)) ."\t";
if (($price) && ($show == 'paid')) $contents .= mb_strtoupper(cleanData($payment->amount)) ."\t";
$contents .= "\n";
}
//Yii::app()->cache->flush();
endforeach;
// Return data as excel
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel; charset=UTF-16LE");
//header("Content-Type: text/plain; charset=UTF-16LE");
echo $contents;
} else {
$this->render('export');
}
}
Espero que se haya entendido mi pregunta, un saludo y gracias de antemano !! ^^