What I want to do is:
- Open a sql file
- Find a keyword then display the line
- Keep displaying each line until the next keyword
- 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
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.
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, butu
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)