skip to Main Content

What I want to do is:

  1. Open a sql file
  2. Find a keyword then display the line
  3. Keep displaying each line until the next keyword
  4. Keep doing 1 – 3 until the end of the file

This is my code but it stops displaying after the first match:

<?php
$filename = 'test.sql';
$startWord = ':begin';
$endWord = 'sqlexception';

// Open the SQL file for reading
$file = fopen($filename, 'r');

if ($file) {
    $foundStart = false;

    while (!feof($file)) {
        $line = fgets($file);

        if ($foundStart || stripos($line, $startWord) !== false) {
            $foundStart = true;
            echo $line; // Display the current line

            if (stripos($line, $endWord) !== false) {
                $foundStart = false; // Stop displaying lines after finding the end word
            }
        }
    }

    fclose($file);
} else {
    echo "Unable to open the file.";
}
?>

This is the sql file I want to retrieve the data from

getDataNlp:begin


    declare continue handler for sqlexception 
    begin
        set returnCode = -1;
        set returnMsg = 'SQL Exception';
    end;
    
    if useTransaction = 1 then
        start transaction;
    end if;
    
    set returnMsg = '';
    set returnCode = 0;
    set @count = 0;
    set @orgName = orgName;
    set @orgId = '';
    set @oprEmail = oprEmail;

CreateOpr:BEGIN


    DECLARE idx INT DEFAULT 1;
    DECLARE v_orgName VARCHAR(128) DEFAULT '';
    declare continue handler for sqlexception 
    begin
        set returnCode = -1;
        set returnMsg = 'SQL Exception';
    end;
    start transaction;

    set returnCode = 0;
    set returnMsg = '';
    set @count = 0;
    set @oprName = oprName;
            
    select count(1) into @count from Opr where name = @oprName;
    if (@count > 0) then
        set returnCode = -1;
        set returnMsg = 'Opr with same oprName already exist';
        rollback;
        leave CreateOpr;
    end if;
 
END ;;

And I want to retrieve the lines like this

getDataNlp: begin

    declare continue handler for sqlexception 

2

Answers


  1. The reason your code stops displaying after the first match is because you’re setting $foundStart back to false after you find the $endWord. Since you’re reading the file line by line, $foundStart becomes false and it stops displaying any subsequent lines. Check this while instead Yours.

        while (!feof($file)) {
        $line = fgets($file);
    
        if (stripos($line, $startWord) !== false) {
            $foundStart = true; 
        }
    
        if ($foundStart) {
            echo $line . '<br>'; 
        }
    
        if (stripos($line, $endWord) !== false && $foundStart) {
            echo "---- End of Block ----<br>"; 
            $foundStart = false; 
        }
    }
    
    Login or Signup to reply.
  2. I’d probably just parse the whole sql file in one go with preg_match_all() instead of trying to parse line by line.

    Use a ranged quantifier {0,$yourLimit} to declare how many lines are permitted between found start & end keywords.

    A . will match all non-vertical space characters. I am assuming that you don’t need multibyte support, but u can be added to the pattern modifiers. i is for case-insensitivity. m makes the $ match the end of a line instead of the end of the string. R matches newline sequences. I am using a lookahead at the end to rtrim() the whitespaces that I assume you don’t want in the result.

    Code: (Demo)

    $startWord = ':begin';
    $endWord = 'sqlexception';
    $maxLinesBetween = 4;
    preg_match_all('#.*' . preg_quote($startWord) . '.*(?:R.*){0,' . $maxLinesBetween . '}.*' . preg_quote($endWord) . '.*?(?=s*$)#im', $sql, $m);
    var_export($m[0]);
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search