PHP - Problem skipping 0D0A lines from text file

Question

I get a text file (.sql) which contains MySQL inserts. I found that there are times when blank lines are included. These blank lines contain hex value 0D0A (Windows newline). MySQL reports an error when a blank line is sent for the query. So, as I read/send the lines to MySQL I want to skip sending any blank lines. I came up with the following code, but it's not working as I expected. Newlines are removed but blank lines are still sent to MySQL. I traced the problem to the PHP command empty(). According to the docs " " should be considered empty. So why does it not skip blank lines? I've spent a few days working on this but nothing I try works. I need another set of eyes, please. Here is the code:

<?php
$bom = pack("H*", "EFBBBF");
if(($reading = fopen("sample.sql", "r")) !== false)
{
 $sql = preg_replace("/^$bom/", "", fgets($reading));
 while(!feof($reading))
 {
  $sql = str_replace(array("\n", "\r", "\r\n"), " ", $sql);
  if(!empty($sql))
  {
   echo("{$sql}<br>");
   $sql = fgets($reading);
  }
 }
 if(!feof($reading))
 {
  echo("Unexpected read error in file." . PHP_EOL);
 }
 fclose($reading);
}
?>

I replace the newlines with a space (if I try to remove the newlines using "" IIS will crash). I expect the empty command to skip the space but it doesn't. The sample data you need to run this script is here.

Thanks for any and all help,

Charles

Check out this previous group of suggestions. https://stackoverflow.com/questions/47210504/removing-blank-lines-from-text-file-using-batch — Wilson Hauck, Nov 13 '20 at 03:57
@WilsonHauck I've read many posts here, in fact some of my code came from suggestions on this site. I just read your suggestion but don't see anything that can help me. I need a PHP solution. That post offered Powershell/Batch file solutions. During my research I tried many things both suggested and accepted answers but they caused IIS to crash. — CharlesEF, Nov 13 '20 at 06:32

CharlesEF · Answer 1 · 2020-11-15T23:29:46.000

After some much needed sleep I found my problem (sort of). I still think empty() should see " " as empty, I'll check the docs again.

To fix my code I had to change the str_replace to remove the newlines completely. Then I had to move fgets out of the if statement (if the line is blank you still need to get the next line).

In case anyone else comes across this problem here is the corrected code:

<?php
$bom = pack("H*", "EFBBBF");
if(($reading = fopen("sample.sql", "r")) !== false)
{
 $sql = preg_replace("/^$bom/", "", fgets($reading));
 while(!feof($reading))
 {
  $sql = str_replace(array("\r\n", "\n", "\r"), "", $sql);
  if(!empty($sql))
  {
   echo("{$sql}<br>");
  }
  $sql = fgets($reading);
 }
 if(!feof($reading))
 {
  echo("Unexpected read error in file." . PHP_EOL);
 }
 fclose($reading);
}
?>

Thanks for looking.

PHP - Problem skipping 0D0A lines from text file

1 Answers1