Inserting Excel sheet in database with more than 1500 records using php reader gives blank page

Question

   <?php 
include("../config/dbcon.php"); 


    if(isset($_FILES['clients'])  )
    {
        extract($_POST); 
        $prod=$_FILES['clients']['name'];
        move_uploaded_file($_FILES['clients']['tmp_name'],'dbxsl/'.$prod);
        $exttt= explode(".",$prod);

 if($exttt[1]=="xls")
{
    include 'Excel/reader.php';
        $data = new Spreadsheet_Excel_Reader();
        $data->setOutputEncoding('CP1251');

        $data->read('dbxsl/'.$prod);

                $result = mysql_query("SHOW TABLE STATUS LIKE 'clients'");
                 $row = mysql_fetch_array($result); 
                 $nextId = $row['Auto_increment'];
                 $nextId1 = $row['Auto_increment'];  
    $wRow=array();
    $valid="";
    //echo 'hieee';
    for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++)
    {
                    if( $data->sheets[0]['cells'][$i][3] != "")
                    {           if (filter_var($data->sheets[0]['cells'][$i][3],FILTER_VALIDATE_EMAIL) === false)
                                {
                                    if (!in_array($i,$wRow ))
                                    {
                                    $validate.="\n  email id in xl sheet for ".$i."th client is not valid  \r\n ";
                                    $validate.="</br>";
                                    array_push($wRow,$i);
                                    continue 1 ;
                                    }
                                //echo $i;
                                }
                                $email=$edata[$k];                  
                    }
                    $GLOBALS[r_name]=addslashes($data->sheets[0]['cells'][$i][1]);
                    $GLOBALS[area]=addslashes($data->sheets[0]['cells'][$i][6]);
                    $GLOBALS[address]=addslashes($data->sheets[0]['cells'][$i][4]);
                    $sqlval123="select * from clients where Restaurant_Name='$r_name' and area='$area' and address='$address'";
                    $retval = mysql_query( $sqlval123);
                    //echo $sqlval123;
                    if(mysql_num_rows($retval)!=0)
                    {
                        if($r_name!="" and $area!="")
                        {
                            if (!in_array($i,$wRow ))
                             {
            $validate.="Client " .$i." in xl sheet is already exist with name =".strtoupper ($r_name)." and at area ='".$area."' with  address ='".$address. "'</br>";
                                array_push($wRow,$i);
                            }
                        }

                    }

                 if(!in_array($i, $wRow))
                {
                        $sql = "INSERT INTO `clients` (countryid,stateid,cityid,mainid,submainid,Restaurant_Name,contact,email,address,timing,area,hd,dinein,svegnn,bar,aircond,cuisine,knownfor,order_sugest,payment,bank,cost,login,password) VALUES\r\n";

                        $insertrate=mysql_query("Insert into avgrate(client_id,avgrat)values($nextId,'0') ");

                            $sql .= "($count,$state,$city,'','',";
                    $sql .= "'" .  addslashes(trim($data->sheets[0]['cells'][$i][1])) . "','" . addslashes(trim( $data->sheets[0]['cells'][$i][2])) . "','" .addslashes(trim(  $data->sheets[0]['cells'][$i][3])) . "','" .  addslashes(trim($data->sheets[0]['cells'][$i][4] )). "','" . addslashes(trim( $data->sheets[0]['cells'][$i][5] )). "','" . addslashes(trim(  $data->sheets[0]['cells'][$i][6])) . "','" . addslashes(trim(  $data->sheets[0]['cells'][$i][7] )). "','" .   addslashes(trim($data->sheets[0]['cells'][$i][8])) . "','" .  addslashes(trim( $data->sheets[0]['cells'][$i][9])) . "','" . addslashes(trim( $data->sheets[0]['cells'][$i][10])) . "','" . addslashes(trim( $data->sheets[0]['cells'][$i][11])) . "','" . addslashes(trim( $data->sheets[0]['cells'][$i][12] )). "','" . addslashes(trim( $data->sheets[0]['cells'][$i][13])) . "','" . addslashes(trim( $data->sheets[0]['cells'][$i][14])) . "','" .  addslashes(trim($data->sheets[0]['cells'][$i][15] )). "','" . addslashes(trim( $data->sheets[0]['cells'][$i][16])). "','" . addslashes(trim( $data->sheets[0]['cells'][$i][17] )). "',";
                            // INSERT INTO TIMINGS TABLE............................................................................
                            $time_arr=split(",", $data->sheets[0]['cells'][$i][5]);

                        $mon="";
                                    $tue="";$wed="";$thu="";$fri="";$sat="";$sun="";
                            for($k=0;$k<=sizeof($time_arr);$k++)
                            {
                                $time=$time_arr[$k];
                                //echo $time."</br>";
                                if($time!="")
                                    {
                                    //echo "helll";
                                    $time=trim($time);
                                    //echo $time."<br>";
                                        if(strpos(strtolower($time),'mon')!== false)
                                        {
                                                $mon=$time;
                                                //echo $mon;
                                        } if(strpos(strtolower($time),'tue')!== false)
                                        {
                                                $tue=$time;
                                                //echo $tue;
                                        } if(strpos(strtolower($time),'wed')!== false)
                                        {
                                                $wed=$time;
                                        } if(strpos(strtolower($time),'thu')!== false)
                                        {
                                                $thu=$time;
                                        } if(strpos(strtolower($time),'fri')!== false)
                                        {
                                                $fri=$time;
                                        } if(strpos(strtolower($time),'sat')!== false)
                                        {
                                                $sat=$time;
                                        } if(strpos(strtolower($time),'sun')!== false)
                                        {
                                                $sun=$time;
                                        }if($mon=="" && $tue=="" && $wed=="" && $thu=="" && $fri=="" && $sat=="" && $sun=="" )
                                         {
                                                $all=$time;
                                        }
                                    }
                            }
                            if($all=="")
                            {
                $insert21="Insert into timings(client_id,mon,tue,wed,thu,fri,sat,sun,cityid)values($nextId-1,'$mon','$tue','$wed','$thu','$fri','$sat','$sun',$city) ";
                            }
                            else
                            {
                $insert21="Insert into timings(client_id,mon,tue,wed,thu,fri,sat,sun,cityid)values($nextId-1,'$all','$all','$all','$all','$all','$all','$all',$city) ";
                            $all="";
                            }
                            //TIMINGS LOGIC OVER........................................................

                            //BANKS,DISCOUNTS,BANK_CLIENT STARTED...........................................


                            $city_arr=split(",",$data->sheets[0]['cells'][$i][16]);
                        for($k=0;$k<sizeof($city_arr);$k++)
                        {
                            $disc_arr=split("-",$city_arr[$k]);
                            $bank=$disc_arr[0];
                            $disc=$disc_arr[1];
                            if($bank!="")
                            {
                                    $sql2="select * from banks where bank=trim('$bank') and cityid=$city";
                                    $res12 = mysql_query($sql2);
                                    if(mysql_num_rows($res12)==0)
                                    {
                                    $insert1="Insert into banks(bank_id,status,bank,cityid)values('','1',trim('$bank'),$city) ";
                                    if(mysql_query($insert1))
                                    {
                                        echo '<script>alert(trim("$bank"));</script>';
                                    }
                                    //echo $insert1;
                                    }
                                    $sql11="select bank_id from banks where bank='$bank' and cityid=$city";
                                    $res11 = mysql_query($sql11);
                                    $row11= mysql_fetch_array($res11);
                                    $bid=$row11['bank_id'];
                                    $sel=mysql_query("select * from bank_client where bank_id=$bid and client_id=$nextId ");
                                    if(mysql_num_rows($sel)==0)
                                    {$insert2=mysql_query("Insert into bank_client(bank_id,client_id)values($bid,$nextId)");
                                    }
                                    $sql1="select bank_id from banks where bank like '$bank' and cityid = $city ";
                                    //echo $sql1;
                                    $res=mysql_query($sql1);
                                    $datares=mysql_fetch_assoc($res);
                                     $value = array_shift( $datares );
                                    $insert=mysql_query("Insert into discounts (client_id,bank_id,discount)values($nextId,$value,'$disc') ");

                            }
                            //echo $insert2;
                        }
                        $nextId=$nextId+1;

                    //BANKS,DISCOUNTS,BANK_CLIENT OVER...........................................

                    // HOME DELIVERY.......................................................
                    if(strtolower($data->sheets[0]['cells'][$i][7])==strtolower("Home Delivery"))
                    {
                    $data->sheets[0]['cells'][$i][7]=1;
                    }
                    else
                    {
                    $data->sheets[0]['cells'][$i][7]=0;
                    }

                    // DINE IN.......................................................
                    if(strtolower($data->sheets[0]['cells'][$i][8])==strtolower("Dine-In Available"))
                    {
                    $data->sheets[0]['cells'][$i][8]=1;
                    }
                    else
                    {
                    $data->sheets[0]['cells'][$i][8]=0;
                    }


                    // VEG NVEG.......................................................
                    if(strtolower($data->sheets[0]['cells'][$i][9])==strtolower("Serves Veg/Non Veg"))
                    {
                    $data->sheets[0]['cells'][$i][9]=1;
                    }
                    else 
                    {
                    $data->sheets[0]['cells'][$i][9]=0;
                    }


                    // BAR.......................................................
                    if(strtolower($data->sheets[0]['cells'][$i][10])==strtolower("Bar"))
                    {
                    $data->sheets[0]['cells'][$i][10]=1;
                    }
                    else 
                    {
                    $data->sheets[0]['cells'][$i][10]=0;
                    }

                    // AC.......................................................
                    if(strtolower($data->sheets[0]['cells'][$i][11])==strtolower("Air Conditioned"))
                    {
                    $data->sheets[0]['cells'][$i][11]=1;
                    }
                    else 
                    {
                    $data->sheets[0]['cells'][$i][11]=0;
                    }


                    // COUSINES.......................................................
                    $cousines_arr=split(",",$data->sheets[0]['cells'][$i][12]);
                    for($k=0;$k<=sizeof($cousines_arr);$k++)
                        {
                            $cousin=$cousines_arr[$k];
                            if($cousin!="")
                            {
                            $cousin=trim($cousin);
                                $insert1="Insert into cousines(client_id,cousine,cityid)values($nextId,'$cousin',$city) ";
                                //echo $insert1;
                                if(mysql_query($insert1))
                                {
                                    /*echo '<script>alert("cousines Updated.");</script>';*/
                                }
                            }
                        }

                    //MAILING CODE......................................................................................
                    $email_name=$data->sheets[0]['cells'][$i][3];
                            $restaurent_name=$edata[0];
                            $login_name=uniqid();   
                            $password=rand(0,10000);
                        $sql .= "'" . mysql_escape_string($login_name) . "',";                  
                        $sql .= "'" . mysql_escape_string($password) . "',"; 
                        $sql = substr($sql, 0, -1) . ");";
                        //echo $sql;

                        $subject = "Dear ".$restaurent_name.", Here Are your Logon Credentials for Wallet Gravity";
                                    $headers = 'From: WalletGravityLoginCredentials@walletgravity.com' . "\r\n" .
                                                'Reply-To: WalletGravityLoginCredentials@walletgravity.com' . "\r\n" .
                                                'X-Mailer: PHP/' . phpversion();
                                    $headers .= "MIME-Version: 1.0\r\n";
                                    $headers .= "Content-Type: text/html; charset=ISO-8859-1\r\n";
                                    $message='<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Register</title>
<body>
You have been registered successfully
</body>
</html>';
            $message=stripslashes($message);
$e=mail($email_name,$subject,$message,$headers);
//echo $sql; exit;
$getdata=mysql_query($sql);
$num=$num+1;            

                }

    }

}
    }
if($num>=1)
{
    if($e="true"){echo '<script>alert("Resturant credentials sent to their email id");</script>';}
    echo '<script> var c='.$num.'; alert(c+" Clients Updated.");</script>';
} 
else
{
    echo "";
}
?>

I want to add excel sheet data in my database it can contain 10000 records too. I have written a code for it but it only inserts upto 1500 records and then gives blank page remaining records are not getting inserted in database. I have used reader.php for reading the excel sheet.Only one for loop I have used to read all rows. I have tried to execute in localhost by increasing max_upload size and max execution time tooo... then also same problem it gives blank page while inserting records. so how can i resolve this problem?

Please help me out. As i want to add upto 10000 records via excel sheet in my database

when i added display_error to my code it shows in error log:

[Wed Jul 15 10:55:03.796940 2015] [ssl:warn] [pid 4000:tid 284] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name [Wed Jul 15 10:55:03.857943 2015] [core:warn] [pid 4000:tid 284] AH00098: pid file D:/xampp/apache/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run? [Wed Jul 15 10:55:04.077956 2015] [ssl:warn] [pid 4000:tid 284] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name [Wed Jul 15 10:55:04.110958 2015] [mpm_winnt:notice] [pid 4000:tid 284] AH00455: Apache/2.4.10 (Win32) OpenSSL/1.0.1i PHP/5.5.19 configured -- resuming normal operations [Wed Jul 15 10:55:04.110958 2015] [mpm_winnt:notice] [pid 4000:tid 284] AH00456: Apache Lounge VC11 Server built: Jul 17 2014 11:50:08 [Wed Jul 15 10:55:04.110958 2015] [core:notice] [pid 4000:tid 284] AH00094: Command line: 'd:\xampp\apache\bin\httpd.exe -d D:/xampp/apache' [Wed Jul 15 10:55:04.113958 2015] [mpm_winnt:notice] [pid 4000:tid 284] AH00418: Parent: Created child process 4576 [Wed Jul 15 10:55:04.695991 2015] [ssl:warn] [pid 4576:tid 296] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name [Wed Jul 15 10:55:04.979007 2015] [ssl:warn] [pid 4576:tid 296] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name [Wed Jul 15 10:55:05.009009 2015] [mpm_winnt:notice] [pid 4576:tid 296] AH00354: Child: Starting 150 worker threads.

In some cases, `PHPExcel` consumes a huge amount of memory. Please enable detailled logging and (if you are able to do on your host) increase `max_execution_time` and `memory_limit`. — dhh, Jul 15 '15 at 05:18
I have already increased both the parameters- execution time n memory limit too..but again same problem — Patel, Jul 15 '15 at 05:22
Please increase `error_reporting` and enable `display_errors` like here: http://stackoverflow.com/questions/6587515/how-to-display-php-errors-in-code-output — dhh, Jul 15 '15 at 05:25
for sheet of 8000 records it behaved like before...(blank page) and for 4000 or 5000 records sheets it was ok now — Patel, Jul 15 '15 at 06:12
If the amount of inserted rows increased, please try increasing the `max_execution_time` even more (600 seconds or something). If you did not fiy any errors in your script, then it must have been the `max_execution_time`. Please increase `max_execution_time` on your remote host, too. Should work the same parameter, if not you might contact your provider / hoster to fix that setting in your php.ini. — dhh, Jul 15 '15 at 06:15
Enabling `display_errors` should really show some errors helping us to understand the problem. — dhh, Jul 15 '15 at 06:18
for sheet of 8000 records it behaved like before...(blank page) on localhost even if i increased "max_execution_time=1000" this is not enough or what? — Patel, Jul 15 '15 at 06:18
Please enable `display_errors` and provide the error message from your server - otherwise we will not be able to help you. — dhh, Jul 15 '15 at 06:19
I have added these lines at start to my code in php....before but then also for big data sheets its giving blank page without any error display ini_set('display_errors', 1); error_reporting(E_ALL ^ E_NOTICE); — Patel, Jul 15 '15 at 06:24
Then please look into the error logs stored on the webserver - maybe you are not allowed to overwrite those settings. — dhh, Jul 15 '15 at 06:34
I am working on localhost does error log exist in localhost? — Patel, Jul 15 '15 at 06:41
If it is enabled in your php configuration - yes. If you are using WAMPP, normally it is located at `\xampp\apache\logs\error.log` (http://stackoverflow.com/questions/3719549/where-does-phps-error-log-reside-in-xampp). — dhh, Jul 15 '15 at 06:49
Error log in my localhost is showing these lines when I added sheet with 8000 records : — Patel, Jul 15 '15 at 07:00
>[Wed Jul 15 10:55:03.796940 2015] [ssl:warn] [pid 4000:tid 284] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name — Patel, Jul 15 '15 at 07:00
>[Wed Jul 15 10:55:03.857943 2015] [core:warn] [pid 4000:tid 284] AH00098: pid file D:/xampp/apache/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run? >[Wed Jul 15 10:55:04.077956 2015] [ssl:warn] [pid 4000:tid 284] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name — Patel, Jul 15 '15 at 07:01
>[Wed Jul 15 10:55:04.110958 2015] [mpm_winnt:notice] [pid 4000:tid 284] AH00455: Apache/2.4.10 (Win32) OpenSSL/1.0.1i PHP/5.5.19 configured -- resuming normal operations >[Wed Jul 15 10:55:04.110958 2015] [mpm_winnt:notice] [pid 4000:tid 284] AH00456: Apache Lounge VC11 Server built: Jul 17 2014 11:50:08 >[Wed Jul 15 10:55:04.110958 2015] [core:notice] [pid 4000:tid 284] AH00094: Command line: 'd:\\xampp\\apache\\bin\\httpd.exe -d D:/xampp/apache' — Patel, Jul 15 '15 at 07:02
>[Wed Jul 15 10:55:04.113958 2015] [mpm_winnt:notice] [pid 4000:tid 284] AH00418: Parent: Created child process 4576 >[Wed Jul 15 10:55:04.695991 2015] [ssl:warn] [pid 4576:tid 296] AH01909: www.example.com:443:0 server certificate does NOT include an ID which matches the server name — Patel, Jul 15 '15 at 07:02
No, does not seem so. Please check your `php.ini` if error logging / reporting is enabled there. It must show some error at least. — dhh, Jul 15 '15 at 07:04

Inserting Excel sheet in database with more than 1500 records using php reader gives blank page

0 Answers0