Re: Getting queries from files FYI

Jay Blanchard wrote:

You may (or may not) remember me posting to the list a couple of weeks

I am a goldfish. hi my name is 10second Tom.... ;-)

ago asking about using REGEX to get queries out of PHP files for a migration project. I had to let it go for several days, but started working on it again yesterday, here is the code (no REGEX was used in the making of this code);

seems like a nice little tool. I'm going to give it a go on my firebirdDB related projects... just too see if it works well (and if it does to see exactly how many queries I have :-) ... to that end I think I will have to extend the $arrQueryStarters array with: , 'EXECUTE', 'WHERE', 'ORDER BY', 'LEFT JOIN' and possibly a few more... also in cases where one has a single query string that is spread out over many lines then it would be nice to be able to have the script return the complete string (code snippet) as a whole rather than getting each line of the query piecemeal.... something to dig into... I'll let you know how I get on! rgds, Jochem

/* * CHA Query Finder * Jay Blanchard * August 2005 * NOT REALLY TOO EXTENSIBLE * * usage: call from command line, perform manual output to text file * i.e. php chaFinder.php > <nameOfFileToSave.txt> */ /* which directory will we be opening? this one, of course */ $theDirectory = $_SERVER['PWD']; /* array of things that identify beginnings of queries */ $arrQueryStarters = array('SELECT', 'INSERT', 'UPDATE', 'FROM');

/* cruise the directory looking for PHP files */ if (is_dir($theDirectory)) { if ($dh = opendir($theDirectory)) {

    while (($theFile = readdir($dh)) !== false) {
      $fileParts = explode('.', $theFile);
      /* we only want to look at PHP files */
      if("php" == $fileParts[1]){
        /* always echo the file name, even if no queries */
        echo "filename: $theFile \n";
        $lineNo = 0;
        /* cruise the file looking for queries */
        $openFile = fopen($theFile, "r");
        while(!feof($openFile)){
          $fileLine = fgets($openFile, 4096);
          $lineNo++;
          /* loop through query starter array */
          for($i = 0; $i < count($arrQueryStarters); $i++){
          /* test if CHA is part of the query element */
          if(strstr($fileLine, $arrQueryStarters[$i]) &&

strstr($fileLine, 'CHA')){

             echo "Line Number: " . $lineNo . " " .  $fileLine;
           }
         }
       }
       fclose($openFile);
     }

} closedir($dh); } } This returns results that look like this ; filename: adminUsrMgmtVer.php filename: agRep.php filename: agRepExcept.php Line Number: 31 $sqlComm = "SELECT DISTINCT(TmsCommission) from CHA.TMSUBS ";

Line Number: 61         $sqlAgImpact = "SELECT count(*) AS count FROM

CHA.TMSUBS WHERE ...

Line Number: 61         $sqlAgImpact = "SELECT count(*) AS count FROM

CHA.TMSUBS WHERE ...

Line Number: 65         $sqlAgTN = "SELECT TmsMastPhoneNumber, `Tm ...
Line Number: 345        $sqlBtnGBTR .= "FROM CHA.TMARMAST c ";
Line Number: 372        $sqlNew .= "FROM CHA.TMARMAST ";

Given this you can see that with some more code I could find the beginning and/or end plus all of the lines in between so that the complete query can be seen and cataloged by file name and line number(s). This is very much what I was looking for. If you have any suggestions for improving this code, or see where I have done too much bull-in-the-china-shop/brute-force grepping they are very much appreciated.