Yii Framework Forum: Memoria Limitada Al Exportar Datos - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Memoria Limitada Al Exportar Datos Rate Topic: -----

#1 User is offline   Chema 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 30-January 13
  • Location:Madrid, Spain

Posted 07 February 2013 - 09:05 AM

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) == 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 !! ^^
0

#2 User is offline   bachem 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 50
  • Joined: 17-January 12
  • Location:Costa Rica

Posted 08 February 2013 - 04:54 PM

La línea "memory_limit" es el espacio en memoria del sistema operativo destinado para el trabajo que realiza el interprete de php, por lo que si no le aumentas el espacio no podrás solucionar nada, ahora lo que podrias hacer es reducir la cantidad de registros que obtienes de la consulta.

Saludos
0

#3 User is offline   Chema 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 30-January 13
  • Location:Madrid, Spain

Posted 12 February 2013 - 10:46 AM

View Postbachem, on 08 February 2013 - 04:54 PM, said:

La línea "memory_limit" es el espacio en memoria del sistema operativo destinado para el trabajo que realiza el interprete de php, por lo que si no le aumentas el espacio no podrás solucionar nada, ahora lo que podrias hacer es reducir la cantidad de registros que obtienes de la consulta.

Saludos

Hola Bachem, gracias por contestar!
Se podrian generar varios archivos excel sabiendo con cuantos registros te da el error , y generar un archivo cuando llegues a ese nuemero de registros, hasta llegar al numero total?
Un saludo!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users