bulkinsert - Inserting Excel sheet in database with more than 1500 records using php reader gives blank page -
<?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 'clients'"); $row = mysql_fetch_array($result); $nextid = $row['auto_increment']; $nextid1 = $row['auto_increment']; $wrow=array(); $valid=""; //echo 'hieee'; ($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 ".$i."th client 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 * clients restaurant_name='$r_name' , area='$area' , address='$address'"; $retval = mysql_query( $sqlval123); //echo $sqlval123; if(mysql_num_rows($retval)!=0) { if($r_name!="" , $area!="") { if (!in_array($i,$wrow )) { $validate.="client " .$i." in xl sheet exist name =".strtoupper ($r_name)." , @ area ='".$area."' address ='".$address. "'</br>"; array_push($wrow,$i); } } } if(!in_array($i, $wrow)) { $sql = "insert `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 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 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 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 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 * banks bank=trim('$bank') , cityid=$city"; $res12 = mysql_query($sql2); if(mysql_num_rows($res12)==0) { $insert1="insert 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 banks bank='$bank' , cityid=$city"; $res11 = mysql_query($sql11); $row11= mysql_fetch_array($res11); $bid=$row11['bank_id']; $sel=mysql_query("select * bank_client bank_id=$bid , client_id=$nextid "); if(mysql_num_rows($sel)==0) {$insert2=mysql_query("insert bank_client(bank_id,client_id)values($bid,$nextid)"); } $sql1="select bank_id banks bank '$bank' , cityid = $city "; //echo $sql1; $res=mysql_query($sql1); $datares=mysql_fetch_assoc($res); $value = array_shift( $datares ); $insert=mysql_query("insert 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 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 logon credentials 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> have been registered </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 email id");</script>';} echo '<script> var c='.$num.'; alert(c+" clients updated.");</script>'; } else { echo ""; } ?> i want add excel sheet data in database can contain 10000 records too. have written code inserts upto 1500 records , gives blank page remaining records not getting inserted in database. have used reader.php reading excel sheet.only 1 loop have used read rows. have tried execute in localhost increasing max_upload size , max execution time tooo... same problem gives blank page while inserting records. how can resolve problem?
please me out. want add upto 10000 records via excel sheet in database
when added display_error code 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 not include id matches 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 not include id matches 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 not include id matches server name [wed jul 15 10:55:04.979007 2015] [ssl:warn] [pid 4576:tid 296] ah01909: www.example.com:443:0 server certificate not include id matches server name [wed jul 15 10:55:05.009009 2015] [mpm_winnt:notice] [pid 4576:tid 296] ah00354: child: starting 150 worker threads.
Comments
Post a Comment