Databases
StackOverflow2013
Postsdbo
POIn Perl, how can I skip some rows returned from a DBI select using multiple regex matches?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIn Perl, how can I skip some rows returned from a DBI select using multiple regex matches?
    primarykey
    data
    text
    <p>When I write out a <a href="https://metacpan.org/module/HTML%3a%3aTable" rel="nofollow"><code>HTML::Table</code></a> I want to skip certain rows. This should be done according to a few user parameters which I get from the web page and compile them as regex.</p> <p>The Parameters are the upper case <code>NOT_*</code> with values such as <code>cc08</code> or a post code or whatever.</p> <pre><code>my $nameRegex = ($NOT_NAME) ? qr/$NOT_NAME/ : ''; my $rackRegex = ($NOT_RACK) ? qr/$NOT_RACK/ : ''; my $unitRegex = ($NOT_UNIT) ? qr/$NOT_UNIT/ : ''; my $addressRegex = ($NOT_ADDR) ? qr/$NOT_ADDR/ : ''; my $townRegex = ($NOT_TOWN) ? qr/$NOT_TOWN/ : ''; my $pcodeRegex = ($NOT_PCODE) ? qr/$NOT_PCODE/ : ''; </code></pre> <p>In the while loop (for a SQL query elsewhere) I get the data, and what I think im doing is "unless you match any of these" add this row of results.</p> <pre><code>while ((my $id, my $name, my $rack, my $unit, my $town, my $address, my $pcode, my $lat, my $lon) = $select_sites-&gt;fetchrow_array()) { my $checkbox = "&lt;input type='checkbox' name='FILTER_SITE' value='$id' $checked{$id} /&gt;"; unless ($name =~ $nameRegex || $rack =~ $rackRegex || $unit =~ $unitRegex || $address =~ $addressRegex || $town =~ $townRegex || $pcode =~ $pcodeRegex) { $rows++; $sitesResultSection-&gt;addRow($checkbox, $name, $rack, $unit, $town, $address, $pcode, $lat, $lon); } </code></pre> <p>Spitting the Regexs would look something like this for <code>$NOT_RACK = "cc08"</code></p> <pre><code> = qr//; (?-xism:cc08) = qr/cc08/; = qr//; = qr//; = qr//; = qr//; </code></pre> <p>However the problem is no rows are added at all, while only one result in the query must be omitted for "cc08", and all others must be shown.</p> <p>The reason I'm doing this in HTML is because there are already other filters in the SQL query (limiting the result set significantly) and making those filters dynamic according to user input would be a nightmare.</p> <p><b>Answer accepted, however I had a further issue:</b></p> <p>This is how I initialised those NOT_s, in the same fashion I used for my query's <code>REGEXP</code> conditions. Thusly when a user enters "City rackname" it'll display racks in <code>City</code> </p> <pre><code>my $NOT_NAME = &amp;useOrs(&amp;trim($cgi-&gt;param('NOT_NAME'))); my $NOT_RACK = &amp;useOrs(&amp;trim($cgi-&gt;param('NOT_RACK'))); my $NOT_UNIT = &amp;useOrs(&amp;trim($cgi-&gt;param('NOT_UNIT'))); my $NOT_ADDR = &amp;useOrs(&amp;trim($cgi-&gt;param('NOT_ADDR'))); my $NOT_TOWN = &amp;useOrs(&amp;trim($cgi-&gt;param('NOT_TOWN'))); my $NOT_PCODE= &amp;useOrs(&amp;trim($cgi-&gt;param('NOT_PCODE'))); my $QUICK_SEARCH_SITES = &amp;trim($cgi-&gt;param('QUICK_SEARCH_SITES')); my $searchRegexp = ($QUICK_SEARCH_SITES) ? &amp;useOrs($QUICK_SEARCH_SITES) : '.*'; sub useOrs { my $tmp = $_[0]; $tmp =~ s/\s+/|/g; return $tmp; } </code></pre> <p>Here's an excerpt from the SQL query <code>WHERE name REGEXP ? OR rack-id REGEXP ? OR [..]</code> So with these hacks some reasonable flexibility is achieved without having to train the monkeys that will use the tool. However using merely <code>$var =~ /$NOT_VAR/</code> will apparently match only exactly that, case sensitive etc. To achieve the looseness of the SQL filter Instead of using <code>&amp;useOrs</code> I use</p> <pre><code>sub useAny { my $tmp = $_[0]; $tmp =~ s/\s/./g; return $tmp; } </code></pre> <p>And most importantly <code>$var =~ /.*$NOT_VAR.*/i</code> </p> <p>I was under the impression that one is supposed to hack away with Perl so there.. :) Suggestions still welcome.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRecct
    1. USRecct
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIn Perl, how can I skip some rows returned from a DBI select using multiple regex matches?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIn Perl, how can I skip some rows returned from a DBI select using multiple regex matches?
      1. This table or related slice is empty.
      1. VTDownMod
    1. COIf you have a separate question, post a separate question. *one is supposed to hack away with Perl* does not reflect well. Whatever the programming language, throwing stuff on the wall until it sticks for a while is not the right approach.
      singulars
      1. POIn Perl, how can I skip some rows returned from a DBI select using multiple regex matches?
      1. USSinan Ünür
    2. COI don't I just expanded my post because I found a further issue with my code. And hacking's supposed to be solving problems right? I find my present accepted solution appropriately robust and readable.
      singulars
      1. POIn Perl, how can I skip some rows returned from a DBI select using multiple regex matches?
      1. USRecct
    3. CO`/.* ... .*/` is default behaviour for perl regexes, and you do not need to add it. `/$NOT_VAR/` matches exactly the same things as `/.*$NOT_VAR.*/`, it only matters if you are capturing anything, or are using the `/g` option. Which you are not.
      singulars
      1. POIn Perl, how can I skip some rows returned from a DBI select using multiple regex matches?
      1. USTLP
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload