breal has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, OK, I have an SQL query that returns some rows from a mysql table. I then apply a regex to search for & display specific titles... code is as follows: 
         $sth=$dbh->prepare($sql);
    $sth->execute() ||
    die "Could not execute SQL statement... maybe invalid syntax?";
    
    #show the results of the regex query in a table
    print "<br>Search Results:<br>".
        "<table border=1>";

while (my @array = $sth->fetchrow_array()){        
        print "<tr>";
        foreach my $elem (@array){
            #search & display the first element in the array for the r
+egex
            $searchTitle=$array[0];
            if ($searchTitle=~/(?=.*pr\w{4}m)(?=(?=\w*w\w*)(?=\w*b\w*)
+\w+\b)/){
            print "<td>";
            print "$elem\n";
            print "<td>";
  }
 }
}
        print "</table>";    
    $sth->finish;
    $dbh->disconnect;

[download]
This is held in a subroutine that when called, displays the results in a browser. Cue my problem... I need to perform another search on the results from this search. How do I save the results of the $searchTitle regex to a variable that I can use in another subroutine? I imagine theres two ways - printing to a log file that I can read back later on, or saving to a variable that I can pass to the other sub. Ive tried the following: 
while (my @array = $sth->fetchrow_array()){        
        print "<tr>";
        foreach my $elem (@array){
            #search & display the first element in the array for the r
+egex
            $searchTitle=$array[0];
            if ($searchTitle=~/(?=.*pr\w{4}m)(?=(?=\w*w\w*)(?=\w*b\w*)
+\w+\b)/){
            print "<td>";
            print "$elem\n";
            print "<td>";
            
            #SAVE OUTPUT FOR SEARCH 3:        
            #WARNING: This prints out the title element n times where 
+n = number
            # of columns in the SQL search
            unless (open (LOGFILE, ">>//applications/mamp/logs/search1
+results"))
                {print "Failed to open search1results log!";exit}
            flock (LOGFILE, LOCK_EX);
            
                print LOGFILE "$searchTitle\n";
            
            unless (close (LOGFILE))
                {print "Failed to close search1results log!";exit}
            }
        }
    }

[download]
But as I indicated in the comments, this repeats every line returned once for every column of the table that im searching on. Was hoping theres an easier way to stick the $searchTitle results in an array that can be passed to another sub, but im not too familiar with passing variables between subroutines yet & don't know how to do it (ive read some tutorials but I just don't get how to apply them in my code above where ive got a regex searching on the first element of an array..). Any help or pointers greatly appreciated. In need of some divine intervention. Thanks

Replies are listed 'Best First'.
Re: loops & sql results storage (flow)
by ikegami (Patriarch) on Apr 19, 2008 at 16:10 UTC

    For starters, the order of your loop and your conditional are backwards. There's no reason to check for a match for each element of @array. 

    foreach my $elem (@array){
   $searchTitle=$array[0];
   if ($searchTitle=~/(?=.*pr\w{4}m)(?=(?=\w*w\w*)(?=\w*b\w*)\w+\b)/){
      print "<td>";
      print "$elem\n";
      print "<td>";
   }
}

    [download]

    should be 

    my $searchTitle = $array[0];
if ($searchTitle =~ /(?=.*pr\w{4}m)(?=(?=\w*w\w*)(?=\w*b\w*)\w+\b)/){
   foreach my $elem (@array){
      print "<td>";
      print "$elem\n";
      print "<td>";
   }
}

    [download]

    Beyond that, I'm not sure I can help you, since I don't understand what you are trying to do. What is the subroutine supposed to do? Writing the results to a file is does not appropriate.

[reply]
[d/l]
[select]
Re: loops & sql results storage (regexp)
by ikegami (Patriarch) on Apr 19, 2008 at 16:27 UTC

    That regexp is rather incomprehensible and has useless assertions. (For example, (?=\w*w\w*) could be written as (?=\w*w). And \w+\b is redundant.) The following is equivalent and easier to read: 

    $searchTitle =~ /pr\w{4}m/ && $searchTitle =~ /w\w*b|b\w*w/

    [download]

    The expression is true if the title contains both 1) a word containing "pr" followed by /\w{4}/ followed by "m", and 2) a word containing "b" and "w" in any order.

[reply]
[d/l]
[select]
Re: loops & sql results storage
by igelkott (Priest) on Apr 19, 2008 at 16:30 UTC

    You've identified two methods (save to variable and save to file) but another reasonable alternative might be to rerun the original search in the other function. Sure it's wasteful but it's easy. Depends on the how long the database search takes and if the extra resources can be justified.

    Otherwise, if you want to go the variable approach, you could just add push @results_1, [@array] after the if statement. If you don't want to deal with arrays of arrays and if you know that your possible results are limited in some way, you could simplify this to something like push @results_1, join('\t',@array) (blindly assuming tab isn't to be found in your output).

    These suggestions are a bit sloppy but might be usable for a casual application.

[reply]
[d/l]
[select]
Re: loops & sql results storage
by pc88mxer (Vicar) on Apr 19, 2008 at 20:17 UTC
    Not related to your original question, but using flock may not be necessary in this situation. Under Unix, appends are atomic when the file is opened in append mode. This is at least true at the level of the write system call (i.e. syswrite in perl.)

    So, if you open the file in append mode and:

    1. you use buffered I/O but only make one small write (small = smaller than the file buffer size), or
    2. you turn on autoflush, or
    3. you use syswrite
    then you don't need to use a mutex to prevent interleaving of output with other processes.

    Unfortunately this only applies to Unix systems (and then only to local file systems - appends are not guaranteed to be atomic under NFS.) Under Win32 I've read that appending writes are not guaranteed to be atomic.

[reply]
[d/l]
[select]
Re: loops & sql results storage
by breal (Novice) on Apr 19, 2008 at 16:44 UTC
    Hi thanks for your reply. Points noted & ammended on the order of loop & the regex construction. This subroutine is supposed to display the results of a search on a page. I need to then search within the results, if that makes sense.
[reply]

      Your clarification makes me wonder if you might wish to reconsider the design:

      This subroutine is supposed to display the results of a search on a page. I need to then search within the results...,

      Do you really need or want to "display" the initial results? If you don't need the overhead of rendering, is there any reason to introduce it?

      You may find it easier and more effective to simply store the initial results in an appropriate structure and then search through that.

[reply]

Back to Seekers of Perl Wisdom