0
require_once 'Excel/reader.php';
$data = new Spreadsheet_Excel_Reader();
$data->setUTFEncoder('iconv');
$data->setOutputEncoding('utf-8');
$data->read('Sample.xls');
error_reporting(E_ALL ^ E_NOTICE);
$mynull=NULL;
for ($i = 2;  $i <= $data->sheets[0]['numRows']; $i++) { 
    $param=$data->sheets[0]['cells'][$i][5];
    $query=$dbpdo->prepare("INSERT INTO table (field) VALUES(?)");
    $sorgu->bindParam(1,$param,PDO::PARAM_STR);
    $sorgu->execute();
}

I have 2 problems. I am using this code for export fields from Excel to MySql. When i run this codes on local server it is working perfectly. But when I run it on web server it isn't get all data in Excel. For example, in Excel I have 5000 data but it is saving 1000 data to MySql. When I want to UPDATE fields from another Excel file, it is working on local server but in the web server it isn't working. A different problem is that, I am using $data->read('C:/Files/Sample.xls'); path in local server. How can i use this in web server? I readed a lot of things about it but didn't find any solution. Can you help me?

Caconde
  • 4,177
  • 7
  • 35
  • 32
Hüseyin Çiftçi
  • 15
  • 5
  • well you have to change the path to the location of the file on your server. If the file isn't on your server, it needs to be. – ADyson Feb 27 '18 at 16:46
  • My file is in the my server. I will change the location ok. – Hüseyin Çiftçi Feb 27 '18 at 19:50
  • ok so when you do that, does it work? If not, what error do you get? – ADyson Feb 27 '18 at 19:56
  • I changed the location of the Excel file on my server but it is not worked. Same result. I have 5000 field but it is saving only 862 field. I didn't get any error. – Hüseyin Çiftçi Feb 27 '18 at 19:58
  • Are you sure it is the correct file? I cannot see any reason it would not read all the data. Perhaps there is a database error on insertion with some rows? I don't see that you are checking for database errors in your code. – ADyson Feb 27 '18 at 19:58
  • To check it's _reading_ all the rows, you could put some debugging output in there to echo `$data->sheets[0]['numRows'];` before you start the loop. You could also echo `$i` during each loop so we know which row it stops on. All these are simple debugging steps you can take in any kind of program. – ADyson Feb 27 '18 at 20:00
  • Yes I am sure. I exported same file on local host perfectly. Everything is same except I am using MySql instead of Phpmyadmin and i changed connection parameters to connect on web server. – Hüseyin Çiftçi Feb 27 '18 at 20:01
  • "MySql instead of Phpmyadmin" These two are not interchangeable. PhpMyAdmin is simply a GUI application which allows you to look at a MySQL database. I only mention this because I find it is helpful to properly understand the technology and tools you are using. – ADyson Feb 27 '18 at 20:02
  • Ok if you're sure it's the correct file then you need to do the debugging as I suggested. Also `$sorgu->execute();` will return `false` if it fails, but you aren't checking for that - you should. You can also set `$dbpdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );` to enable PDO to throw exceptions when it fails. – ADyson Feb 27 '18 at 20:04
  • i tried `$data->sheets[0]['numRows'];` code and i get true value. it is counting. – Hüseyin Çiftçi Feb 27 '18 at 20:06
  • Ok so if the count is correct it all points to a database issue I would think. Carry on checking for database errors as I mentioned. – ADyson Feb 27 '18 at 20:07
  • i didnt created new table when i install mysql workbench. I am double checking but http://huseyinciftci.com/yazilim/deneme/guncelleme.php you can see here that i give 2 `echo` code. 1. `echo 'Record count: '. $data->sheets[0]['numRows'].'';` 2. `echo 'Insert count: '. $i;`. But 2. echo command is not worked. – Hüseyin Çiftçi Feb 27 '18 at 20:18
  • if you didn't create the table you want to insert into, then obviously it isn't going to work... don't you get any exceptions? Your page is returning 500 internal server error...so you should have an error in your logs somewhere to tell you what is wrong – ADyson Feb 27 '18 at 20:19
  • Now i added `catch(PDOException $e){ echo $e->getMessage(); echo '
    '; }` code for error code but it isnt give me any error.     – Hüseyin Çiftçi Feb 27 '18 at 20:22
  • Yes i didnt create new table. I used older tables that I have been using in PhpMyAdmin. – Hüseyin Çiftçi Feb 27 '18 at 20:23
  • So you created the `table` table in your server database or not? It's not clear from what you're saying. – ADyson Feb 27 '18 at 20:25
  • BTW your page returns "Record count: 2626" (not 5000 as you claimed!) – ADyson Feb 27 '18 at 20:26
  • Yes i have a table in my server database. – Hüseyin Çiftçi Feb 27 '18 at 20:27
  • With the correct name? And user has correct permissions? If you aren't getting a PDOException, maybe you have some other exception? Try just catching Exception instead of PDOException. It should be in your logs also – ADyson Feb 27 '18 at 20:27
  • Yes i refresh my table that it have 2626 record. – Hüseyin Çiftçi Feb 27 '18 at 20:27
  • But it seems like it saves 0 rows into the database, right? Because it never even echos $i once. – ADyson Feb 27 '18 at 20:28
  • No it is saving different rows each time. 900 rows, 1000 rows, 700 rows. it is changing. – Hüseyin Çiftçi Feb 27 '18 at 20:29
  • That's impossible. If it never echos $i within your `for` loop , it cannot be inserting any data. You did write `echo 'Insert count: '. $i;` **within** the for loop, yes? – ADyson Feb 27 '18 at 20:30
  • Also please do what I suggested with the exception handling and also check your PHP logs. If PHP is not set to log errors, then enable it. – ADyson Feb 27 '18 at 20:31
  • I used 2 different way to save my data. In the link which I send you I used oleread.inc and reader.php. In the other version I used PhpExcel1.8. It isnt changed anything. – Hüseyin Çiftçi Feb 27 '18 at 20:31
  • ??? So are we talking about the code written above, or not? It's not clear any more. If you're going to make changes, you have to mention it. I can't help you fix code I can't see, especially if you aren't checking for errors properly. – ADyson Feb 27 '18 at 20:33
  • I looked error logs of php. I saw this error _Maximum execution time of 60 seconds exceeded in..._ – Hüseyin Çiftçi Feb 27 '18 at 20:33
  • Ok so the server timed out the request before your script completed. You have to increase the timeout value in php.ini. Finally we are getting somewhere. – ADyson Feb 27 '18 at 20:34
  • I really thank you to work with me. – Hüseyin Çiftçi Feb 27 '18 at 20:34
  • where can i find php.ini file? is it in my server? – Hüseyin Çiftçi Feb 27 '18 at 20:35
  • Your server is Linux or Windows? – ADyson Feb 27 '18 at 20:36
  • My server is Windows. – Hüseyin Çiftçi Feb 27 '18 at 20:36
  • See https://stackoverflow.com/a/17185370/5947043 – ADyson Feb 27 '18 at 20:38
  • I did `` php file and I read _Loaded Configuration File C:\Program Files (x86)\Parallels\Plesk\Additional\PleskPHP54\php.ini_ . Copied and pasted but there isnt any Program Files (x86) because my pc 32 bit i have only Program Files :) and in the Program Files there isnt any plesk file :) – Hüseyin Çiftçi Feb 27 '18 at 20:44
  • also i searched php.ini file in my pc. but it didnt find anything :) – Hüseyin Çiftçi Feb 27 '18 at 20:45
  • You are looking on the server, yes? are you on some sort of shared host or virtual host for your server? Maybe you have to ask the system administrator to change it. Or you can see if including a set_time_limit command in your PHP script works instead: http://php.net/manual/en/function.set-time-limit.php . – ADyson Feb 27 '18 at 21:20
  • I added `set_time_limit(200);` code to my php codes. It is worked but in 1424. field it throw error that "There is a problem with the resource you are looking for, and it cannot be displayed." – Hüseyin Çiftçi Feb 27 '18 at 21:59
  • I added `ini_set('max_execution_time', 300);` code to my php codes. It is worked but in 1488. field it throw error that "There is a problem with the resource you are looking for, and it cannot be displayed." – Hüseyin Çiftçi Feb 27 '18 at 21:59
  • is it still timing out then? How long does it need when you run it locally? – ADyson Feb 27 '18 at 22:05
  • there is a php_error log. "PHP Parse error: syntax error, unexpected '=', expecting ',' or ';' in C:\Inetpub\vhosts\huseyinciftci.com\httpdocs\yazilim\GDB\gdb\guncelleme.php on line 13". The 13. line is `ini_set('max_execution_time', 300);` – Hüseyin Çiftçi Feb 27 '18 at 22:07
  • this error same for `set_time_limit(200);` too. – Hüseyin Çiftçi Feb 27 '18 at 22:08
  • I don't get it. You said it worked for some rows. But if you have a syntax error, the code cannot run at all. What you are telling me is two contradictory things. Also, neither of those lines looks like it has a syntax error. – ADyson Feb 27 '18 at 22:09
  • In local my timeout limit 120 second. – Hüseyin Çiftçi Feb 27 '18 at 22:09
  • when I add `set_time_limit(200);` or `ini_set('max_execution_time', 300);` throwing this error. – Hüseyin Çiftçi Feb 27 '18 at 22:10
  • Before you said "I added set_time_limit(200); code to my php codes. It is worked but in 1424. field it throw error that "There is a problem with the resource you are looking for, and it cannot be displayed."". So...either it works for some rows, or you get a syntax error. Which is it? It can't be both. – ADyson Feb 27 '18 at 22:22
  • in local; it is working without adding set limit. so you know my problem, i added setting limit code for the 60 second error. Now, if i add set limit code, 60 second error isnt throwing but it is throwing "There is a problem with the resource you are looking for, and it cannot be displayed." error. – Hüseyin Çiftçi Feb 27 '18 at 22:48
  • So what about the syntax error you mentioned? has that gone away? You aren't being clear. – ADyson Feb 27 '18 at 22:50
  • i dont know what about this error. `include "menu.php"; ini_set('max_execution_time', 400); $table=""; $db_host=""; $db_user=""; $db_sifre=""; $db_name="";` this is the code block. and `ini_set('max_execution_time', 400);` is 13. line. – Hüseyin Çiftçi Feb 27 '18 at 22:53
  • The 60 second error has gone away. But our new error syntax error. is there any error in `ini_set('max_execution_time', 400);` code? – Hüseyin Çiftçi Feb 27 '18 at 22:58
  • https://eval.in/964080 that command compiles without any syntax error. Perhaps some other code nearby is the cause of the syntax problem. Without seeing it, it's impossible for me to know. The rest of what you showed is also ok: https://eval.in/964088. Maybe something else is the real cause of the error. Sometimes a missing bracket or semicolon elsewhere can cause php to report the wrong line number for the error – ADyson Feb 28 '18 at 06:08
  • the weird thing is, this codes saving 1200-1500 rows. why not saving all of rows... – Hüseyin Çiftçi Feb 28 '18 at 15:34
  • Code with a syntax error doesn't even execute at all, so again what you're saying is impossible. I can't really help you because you keep contradicting yourself, and also I suspect you have a) made changes to the code which I can't see, and/or b) have some other code in use which I have never seen and which is updating your database, and/or c) have more than one instance of your code pointing at the same database. It's is very unclear and confusing. I can't read your screen or your mind, just these comments. Sorry but we have made almost no progress after all and I don't have any more time. – ADyson Feb 28 '18 at 15:40
  • if you want you can see all my codes. the condition is weird. I think you understand me clearly. There isn't any sytax problem with my codes. If there is a problem with syntax no data will be save. But it is saving :) Thank you for your time. You tried to solve my problem. I am grateful to you. – Hüseyin Çiftçi Feb 28 '18 at 15:51
  • No, the problem is I don't understand you clearly. That was my whole point. Again you contradict yourself...you say there's a syntax error, then in a later comment you say there isn't. I don't know what to think is true. – ADyson Feb 28 '18 at 15:57
  • Why i couldnt write my problem... when i add `ini_set('max_execution_time', 400);` or `set_time_limit(200);` this is giving syntax error. if i dont write this codes it giving time limit error. but it is saving 1200-1500 records. this is simple to understand but hard for solve. – Hüseyin Çiftçi Feb 28 '18 at 16:28
  • if your file is so huge you maybe should consider doing it not through a web request, but some other type of task e.g. triggered from a cron job on a scheduled basis, so that it isn't subject to the webserver's timeout rules. Although having said that it's unclear to me why you have a syntax error when you add those extra instructions, because those codes don't cause a syntax error in PHP by themselves. Without seeing any context it's impossible to know, but it shouldn't be the case. I can only assume you did something wrong, but I can't say what without the full source code. – ADyson Feb 28 '18 at 16:33
  • give me your email adress if you want to see all codes. i coulndt share here because it is long. – Hüseyin Çiftçi Feb 28 '18 at 16:55
  • I don't particularly want to really, and I never give my email to strangers regardless. If you are wanting to be a successful programmer though TBH you need to learn to fix syntax errors yourself. – ADyson Feb 28 '18 at 16:59
  • Ok thank you for your interest. – Hüseyin Çiftçi Feb 28 '18 at 17:12

0 Answers0