Deployed Application to Live Server not working properly?

John_Betong · October 30, 2020, 1:11pm

Try the following to pinpoint the exact line where the error occurs:

echo “line ==> “ .__line__; exit;

Start at the top and gradually move down.

droopsnoot · October 30, 2020, 1:27pm

Isn’t that how it’s been narrowed down to the execute() statement?

jmyrtle · October 30, 2020, 1:39pm

I tried that earlier using the advice @droopsnoot gave me:

droopsnoot:

If you resort to adding
echo "here";
exit();
in your code in appropriate places, at what point does it start giving the 500 error? Once you can see how far the code gets before it dies, that might point to why it’s working differently.

“here” appeared until after the execute() statement.

John_Betong · October 30, 2020, 1:56pm

if($row[0] == NULL) {
				continue;
			}
echo “<pre>”; print_r($row); echo “</pre><br >’”;

Try adding the above line.

John_Betong · October 30, 2020, 1:59pm

Whoops, missed that

jmyrtle · October 30, 2020, 2:13pm

Still no results…

John_Betong · October 30, 2020, 2:25pm

The output was expected to be valid data and if the error occurred then examine the csv data and ensure it conforms to the table column value restrictions.

If as you say there are no results then it looks as though the while loop is never activated?

Try adding and incrementing a counter to see if there are any lines executed.

jmyrtle · October 30, 2020, 2:35pm

I know that the code processes before the while loop. It’s after the while loop where it stops.

So I know the connection to the database is working, but the code to process the import is not.

John_Betong · October 30, 2020, 2:39pm

Try adding pdo error reporting:

https://www.php.net/manual/en/pdo.error-handling.php

John_Betong · October 30, 2020, 2:43pm

Have you tested to see how many executions are valid and which csv line fails execution?

jmyrtle · October 30, 2020, 2:46pm

Isn’t that what the dbconnect file is supposed to do?

try {

$dsn = "mysql:host=$dbhost;dbname=$dbname";
$pdo = new PDO($dsn, $dbuser, $dbpwd);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

error_log('Connection Successful');
return $pdo;
}

catch (PDOException $e)

{
error_log('Connection failed.', $e->getMessage());
}

I’m not quite sure how to do that. Should I do what droopsnoot said for every bindParam() line? $stmt->execute(); is only running 1 query.

John_Betong · October 30, 2020, 2:55pm

It appears as though the query is never executed.

The error reporting appears ok.

There are other methods available to build queries, I would be tempted to try another method and also echo the complete csv contents.

Edit

I prefer to build a query string, echo the string before trying to execute the query.

mabismad · October 30, 2020, 3:19pm

You have got to get php’s error related settings set up so that php will help you, by either displaying or logging all the errors it detects. Setting the PDO error mode to exceptions won’t do much good if php isn’t displaying or logging all errors, which will now include the un-caught PDO exceptions.

What exactly did you change? The phpinfo() output that you posted lists the php.ini that php is using, the 'Loaded Configuration File; line. Also, you need to stop/start the web server to guarantee any changes made to the php.ini will take effect and then use a phpinfo() statement in a (functioning) .php script to confirm that the changes actually occurred.

If for some reason you cannot get the php error settings to work in the php.ini, you can put the settings into your code, since it appears that your code is actually running up to a point. However, the setting lines that @John_Betong posted where ‘published’ somewhere and contain curly-smart quotes and won’t be seen by php as actual code.

You can temporarily add the following two lines of code to the main .php file, immediately after the first opening <?php tag, to get php to report and display all runtime errors -

ini_set('display_errors', '1');
error_reporting(-1);

You should only have a single one, that gets required wherever it is needed. Don’t Repeat Yourself (DRY.)

jmyrtle · October 30, 2020, 3:28pm

What exactly do I need to change? According to the php.ini file, display errors are on, so why is PHP not showing me anything?

I looked at the log file and this is what I see from today’s date:

[Fri Oct 30 11:45:41.111859 2020] [mpm_prefork:notice] [pid 1138] AH00163: Apache/2.4.41 (Ubuntu) configured -- resuming normal operations
[Fri Oct 30 11:45:41.146433 2020] [core:notice] [pid 1138] AH00094: Command line: '/usr/sbin/apache2'
[Fri Oct 30 11:48:47.399900 2020] [php7:notice] [pid 1201] [client 192.168.16.218:49607] Connection Successful
[Fri Oct 30 11:48:47.429247 2020] [php7:notice] [pid 1201] [client 192.168.16.218:49607] Connection Successful
[Fri Oct 30 11:48:52.046975 2020] [php7:notice] [pid 1905] [client 192.168.16.218:49611] Connection Successful, referer: http://192.168.16.14/invoice/login.php
[Fri Oct 30 11:48:52.175339 2020] [php7:notice] [pid 1905] [client 192.168.16.218:49611] Connection Successful, referer: http://192.168.16.14/invoice/login.php
[Fri Oct 30 11:48:56.384603 2020] [php7:notice] [pid 1202] [client 192.168.16.218:49608] Connection Successful, referer: http://192.168.16.14/invoice/index.php
[Fri Oct 30 11:48:58.445828 2020] [php7:notice] [pid 1202] [client 192.168.16.218:49608] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 11:49:03.294483 2020] [php7:notice] [pid 1202] [client 192.168.16.218:49608] Connection Successful, referer: http://192.168.16.14/invoice/import_invoices.php
[Fri Oct 30 11:49:03.305892 2020] [php7:error] [pid 1202] [client 192.168.16.218:49608] PHP Fatal error:  Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect decimal value: '' for column `cascotax_db`.`invoices`.`Use_Tax` at row 1 in /var/www/invoice/api/import.php:53\nStack trace:\n#0 /var/www/invoice/api/import.php(53): PDOStatement->execute()\n#1 {main}\n  thrown in /var/www/invoice/api/import.php on line 53, referer: http://192.168.16.14/invoice/import_invoices.php
[Fri Oct 30 11:49:07.418532 2020] [php7:notice] [pid 1908] [client 192.168.16.218:49619] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 11:49:19.136193 2020] [php7:notice] [pid 1909] [client 192.168.16.218:49635] Connection Successful
[Fri Oct 30 11:49:19.153122 2020] [php7:notice] [pid 1909] [client 192.168.16.218:49635] Connection Successful
[Fri Oct 30 11:49:24.202017 2020] [php7:notice] [pid 1911] [client 192.168.16.218:49643] Connection Successful, referer: http://192.168.16.14/invoice/login.php
[Fri Oct 30 11:49:24.296963 2020] [php7:notice] [pid 1911] [client 192.168.16.218:49643] Connection Successful, referer: http://192.168.16.14/invoice/login.php
[Fri Oct 30 11:49:27.315361 2020] [php7:notice] [pid 1911] [client 192.168.16.218:49643] Connection Successful, referer: http://192.168.16.14/invoice/index.php
[Fri Oct 30 11:49:30.565096 2020] [php7:notice] [pid 1911] [client 192.168.16.218:49643] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 11:50:09.252144 2020] [php7:notice] [pid 1204] [client 192.168.16.218:49675] Connection Successful, referer: http://192.168.16.14/invoice/import_invoices.php
[Fri Oct 30 11:50:09.257470 2020] [php7:error] [pid 1204] [client 192.168.16.218:49675] PHP Fatal error:  Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect decimal value: '' for column `cascotax_db`.`invoices`.`Use_Tax` at row 1 in /var/www/invoice/api/import.php:53\nStack trace:\n#0 /var/www/invoice/api/import.php(53): PDOStatement->execute()\n#1 {main}\n  thrown in /var/www/invoice/api/import.php on line 53, referer: http://192.168.16.14/invoice/import_invoices.php
[Fri Oct 30 11:51:07.193108 2020] [php7:notice] [pid 1910] [client 192.168.16.218:49686] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 11:51:09.392098 2020] [php7:notice] [pid 1201] [client 192.168.16.218:49690] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 11:51:10.280403 2020] [php7:notice] [pid 1905] [client 192.168.16.218:49693] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 11:51:14.172068 2020] [php7:notice] [pid 1202] [client 192.168.16.218:49694] Connection Successful, referer: http://192.168.16.14/invoice/import_invoices.php
[Fri Oct 30 11:51:14.172814 2020] [php7:error] [pid 1202] [client 192.168.16.218:49694] PHP Fatal error:  Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect decimal value: '' for column `cascotax_db`.`invoices`.`Use_Tax` at row 1 in /var/www/invoice/api/import.php:53\nStack trace:\n#0 /var/www/invoice/api/import.php(53): PDOStatement->execute()\n#1 {main}\n  thrown in /var/www/invoice/api/import.php on line 53, referer: http://192.168.16.14/invoice/import_invoices.php
[Fri Oct 30 11:53:37.509143 2020] [php7:notice] [pid 2008] [client 192.168.16.218:49746] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 11:53:39.993811 2020] [php7:notice] [pid 2010] [client 192.168.16.218:49749] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 11:53:40.742847 2020] [php7:notice] [pid 1202] [client 192.168.16.218:49752] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 11:53:44.699750 2020] [php7:error] [pid 1910] [client 192.168.16.218:49753] PHP Parse error:  syntax error, unexpected end of file in /var/www/invoice/api/import.php on line 69, referer: http://192.168.16.14/invoice/import_invoices.php
[Fri Oct 30 11:53:46.536817 2020] [php7:notice] [pid 1911] [client 192.168.16.218:49759] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 14:11:45.406165 2020] [php7:notice] [pid 2010] [client 192.168.16.218:51803] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 14:11:45.424764 2020] [php7:notice] [pid 2010] [client 192.168.16.218:51803] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 14:11:51.550477 2020] [php7:notice] [pid 1905] [client 192.168.16.218:51813] Connection Successful, referer: http://192.168.16.14/invoice/login.php
[Fri Oct 30 14:11:51.640002 2020] [php7:notice] [pid 1202] [client 192.168.16.218:51814] Connection Successful, referer: http://192.168.16.14/invoice/login.php
[Fri Oct 30 14:11:57.266720 2020] [php7:notice] [pid 2009] [client 192.168.16.218:51819] Connection Successful, referer: http://192.168.16.14/invoice/index.php
[Fri Oct 30 14:11:58.942967 2020] [php7:notice] [pid 2009] [client 192.168.16.218:51819] Connection Successful, referer: http://192.168.16.14/invoice/invoices_list.php
[Fri Oct 30 14:12:02.923816 2020] [php7:error] [pid 1910] [client 192.168.16.218:51826] PHP Parse error:  syntax error, unexpected '>' in /var/www/invoice/api/import.php on line 18, referer: http://192.168.16.14/invoice/import_invoices.php
[Fri Oct 30 15:23:44.860951 2020] [mpm_prefork:notice] [pid 1138] AH00169: caught SIGTERM, shutting down
[Fri Oct 30 15:23:44.954326 2020] [mpm_prefork:notice] [pid 3216] AH00163: Apache/2.4.41 (Ubuntu) configured -- resuming normal operations
[Fri Oct 30 15:23:44.954383 2020] [core:notice] [pid 3216] AH00094: Command line: '/usr/sbin/apache2'
[Fri Oct 30 15:38:45.081857 2020] [mpm_prefork:notice] [pid 3216] AH00169: caught SIGTERM, shutting down
[Fri Oct 30 15:38:45.173558 2020] [mpm_prefork:notice] [pid 3264] AH00163: Apache/2.4.41 (Ubuntu) configured -- resuming normal operations
[Fri Oct 30 15:38:45.173619 2020] [core:notice] [pid 3264] AH00094: Command line: '/usr/sbin/apache2'
[Fri Oct 30 15:39:16.413552 2020] [mpm_prefork:notice] [pid 3264] AH00169: caught SIGTERM, shutting down
[Fri Oct 30 15:39:16.511674 2020] [mpm_prefork:notice] [pid 3348] AH00163: Apache/2.4.41 (Ubuntu) configured -- resuming normal operations
[Fri Oct 30 15:39:16.511721 2020] [core:notice] [pid 3348] AH00094: Command line: '/usr/sbin/apache2'

Is that not what I’m supposed to use?

I need two since my code runs DataTables. If I try to reference my dbconnect file in DataTables, the DataTables script does not work.

mabismad · October 30, 2020, 3:39pm

Those are all comments listing the suggested values for different situations. The settings are lines that look like -

display_errors = on
error_reporting = E_ALL

You would need to find the current lines setting the values and change those lines.

Since we don’t know what php.ini file you edited and there are usually several to be found in a typical installation, I listed how you can find the one that php is using.

jmyrtle · October 30, 2020, 3:50pm

My apologies, I realized they were commented lines after I posted my last response.

After enabling these settings, Firefox shows this:

Fatal error: Uncaught PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 
Incorrect decimal value: '' for column `cascotax_db`.`invoices`.`Use_Tax` at row 1 in
 /var/www/invoice/api/import.php:53 Stack trace: #0 /var/www/invoice/api/import.php(53):
 PDOStatement->execute() #1 {main} thrown in /var/www/invoice/api/import.php on line 53

Apache’s error log also shows two more errors:

[Fri Oct 30 11:53:44.699750 2020] [php7:error] [pid 1910] [client 192.168.16.218:49753] 
PHP Parse error:  syntax error, unexpected end of file in /var/www/invoice/api/import.php 
on line 69

[Fri Oct 30 14:12:02.923816 2020] [php7:error] [pid 1910] [client 192.168.16.218:51826]
 PHP Parse error:  syntax error, unexpected '>' in /var/www/invoice/api/import.php
on line 18

I’m a bit confused now.

Line 53 is $stmt->execute();, Line 69 doesn’t exist and Line 18 doesn’t hold a > because an IF statement condition is there:

if($row[0] == NULL) {
				continue; // Line 18
			}

droopsnoot · October 30, 2020, 6:02pm

That normally suggests in a parse error that PHP can’t figure out where the error is, so it gives the last line of the file, which seems about right if your early post is the current code.

This is starting to sound as if your file has a dodgy character somewhere in it. Do you have a text editor that will highlight non-ASCII code or that kind of stuff?

jmyrtle · October 30, 2020, 6:12pm

I used Atom when I wrote this.

The original code is here. I’m not sure whether or not there are any characters that look out of place…

<?php

include('dbconnect.php');

if(isset($_POST['btn_upload'])){
	$filename = $_FILES['file']['tmp_name'];
	$file = fopen($filename,"r");


	// Read and throw away the header row
	fgetcsv($file);

	$stmt = $pdo->prepare("INSERT INTO invoices (id, Invoice_Number, Customer_Number,
 Customer_Name, Invoice_Date, Sales_Order_Number, Sales_Amount, Progress_Billing, 
Extra_Charge_Amount, Tax_Amount, Invoice_Amount, Writeoff_Amount, Paid_Amount, 
Balance_Due, Use_Tax, Tax_Stat, Salesperson_1, Comm_Rate_1, Salesperson_2, 
Comm_Rate_2, Salesperson_3, Comm_Rate_3, Deposit_Applied, Sales_Tax_Codes,
 Sales_Tax_Amounts, Customer_Address1, Customer_Address2, Customer_City, 
Customer_State, Customer_Zip, Customer_Country) 
VALUES (:id, :Invoice_Number, :Customer_Number, :Customer_Name, :Invoice_Date,
 :Sales_Order_Number, :Sales_Amount, :Progress_Billing, :Extra_Charge_Amount, 
:Tax_Amount, :Invoice_Amount, :Writeoff_Amount, :Paid_Amount, :Balance_Due, 
:Use_Tax, :Tax_Stat, :Salesperson_1, :Comm_Rate_1, :Salesperson_2, :Comm_Rate_2, 
:Salesperson_3, :Comm_Rate_3, :Deposit_Applied, :Sales_Tax_Codes, 
:Sales_Tax_Amounts, :Customer_Address1, :Customer_Address2, :Customer_City, 
:Customer_State, :Customer_Zip, :Customer_Country)");

	while (! feof($file)) {
  		$row = fgetcsv($file);
			if($row[0] == NULL) {
				continue;
			}
			$stmt->bindParam(':id', $row[0]);
			$stmt->bindParam(':Invoice_Number', $row[1]);
		$stmt->bindParam(':Customer_Number', $row[2]);
		$stmt->bindParam(':Customer_Name', $row[3]);
		$date = date_create($row[4]);
		$dfstr = date_format($date, 'Y-m-d');
		$stmt->bindParam(':Invoice_Date', $dfstr);
  		$stmt->bindParam(':Sales_Order_Number', $row[5]);
  		$stmt->bindParam(':Sales_Amount', $row[6]);
  		$stmt->bindParam(':Progress_Billing', $row[7]);
  		$stmt->bindParam(':Extra_Charge_Amount', $row[8]);
  		$stmt->bindParam(':Tax_Amount', $row[9]);
  		$stmt->bindParam(':Invoice_Amount', $row[10]);
  		$stmt->bindParam(':Writeoff_Amount', $row[11]);
  		$stmt->bindParam(':Paid_Amount', $row[12]);
  		$stmt->bindParam(':Balance_Due', $row[13]);
			$stmt->bindParam(':Use_Tax', $row[14]);
			$stmt->bindParam(':Tax_Stat', $row[15]);
			$stmt->bindParam(':Salesperson_1', $row[16]);
			$stmt->bindParam(':Comm_Rate_1', $row[17]);
			$stmt->bindParam(':Salesperson_2', $row[18]);
			$stmt->bindParam(':Comm_Rate_2', $row[19]);
			$stmt->bindParam(':Salesperson_3', $row[20]);
			$stmt->bindParam(':Comm_Rate_3', $row[21]);
			$stmt->bindParam(':Deposit_Applied', $row[22]);
			$stmt->bindParam(':Sales_Tax_Codes', $row[23]);
			$stmt->bindParam(':Sales_Tax_Amounts', $row[24]);
			$stmt->bindParam(':Customer_Address1', $row[25]);
			$stmt->bindParam(':Customer_Address2', $row[26]);
			$stmt->bindParam(':Customer_City', $row[27]);
			$stmt->bindParam(':Customer_State', $row[28]);
			$stmt->bindParam(':Customer_Zip', $row[29]);
			$stmt->bindParam(':Customer_Country', $row[30]);
  		$stmt->execute();

}

	fclose($file);
	header("Location: ../invoices_list.php");
}

if(isset($_POST['btn_back'])) {
	header("Location: ../invoices_list.php");
}

?>

Also, going back to the Use_Tax problem… the error says invalid datetime format, but the structure of the column is decimal, not datetime…

There is a date column for Invoice_Date which the PHP code is changing the format of using this information:

$date = date_create($row[4]);
$dfstr = date_format($date, 'Y-m-d');
$stmt->bindParam(':Invoice_Date', $dfstr);

droopsnoot · October 30, 2020, 6:59pm

Impossible to tell here, because the forum software will strip them out while it’s posting your comment. You could try copying back from the forum and pasting into a new file.

jmyrtle · October 30, 2020, 7:21pm

Did you see my previous edits? I wonder if it has something to do with the way the date is written?

I’m getting hopeless at this point… why can’t a program transfer from a Windows XAMPP to a local Linux server? The code is the same and the database is the same, so what’s the difference that’s making the code not work?