Ignore:
Timestamp:
03/17/05 18:02:08 (20 years ago)
Author:
Kris Deugau
Message:

/branches/new-search-20050223

Functional multi-component search, minus detail on include/exclude.
Also missing details of IP address search code.

File:
1 edited

Legend:

Unmodified
Added
Removed
  • branches/new-search-20050223/cgi-bin/search.cgi

    r197 r201  
    6262    $webvar{input} =~ s/^\s+//;
    6363    $webvar{input} =~ s/\s+$//;
    64     if ($webvar{input} =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{1,3}$/) {
    65       # "Perfect" IP subnet match
    66       $searchfor = "ipblock";
    67     } elsif ($webvar{input} =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
    68       # "Perfect" IP address match (shows containing block)
    69       $searchfor = "ipblock";
    70     } elsif ($webvar{input} =~ /^(\d{1,3}\.){2}\d{1,3}(\.\d{1,3}?)?/) {
    71       # Partial IP match
     64    if ($webvar{input} =~ /^[\d\.]+(\/\d{2})?$/) {
     65      # IP addresses should only have numbers, digits, and maybe a slash+netmask
    7266      $searchfor = "ipblock";
    7367    } elsif ($webvar{input} =~ /^\d+$/) {
     
    8579  print "Complex Search...............\n";
    8680
    87 print "<pre>\n";
    88 foreach my $key (keys %webvar) {
    89   print "key: $key      value: $webvar{$key}\n";
    90 }
    91 print "</pre>\n";
     81  # Several major cases, and a whole raft of individual cases.
     82  # -> Show all types means we do not need to limit records retrieved by type
     83  # -> Show all cities means we do not need to limit records retrieved by city
     84  # Individual cases are for the CIDR/IP, CustID, Description, Notes, and individual type
     85  # requests.
     86
     87my $sql = "SELECT cidr,custid,type,city,description FROM searchme";
     88
     89my $sqlconcat;
     90if ($webvar{which} eq 'all') {
     91  # Must match *all* specified criteria.        ## use INTERSECT or EXCEPT
     92$sqlconcat = "INTERSECT";
     93#  $sqlconcat = "and";
     94} elsif ($webvar{which} eq 'any') {
     95  # Match on any specified criteria             ## use UNION
     96$sqlconcat = "UNION";
     97#  $sqlconcat="or";
     98} else {
     99  # We can't get here.  PTHBTT!
     100  printAndExit "PTHBTT!!  Your search has been rejected due to Microsoft excuse #4432:  Not enough mana";
     101}
     102
     103# Cases:
     104# -> No search terms.  Display everything.
     105# -> One or more terms (cidr/custid/desc/notes)
     106
     107# -> Type/city restrictions
     108
     109my $cols = "cidr,custid,type,city,description";
     110
     111$sql = "(select $cols from searchme where $webvar{custexclude} custid ilike '%$webvar{custid}%')".
     112        " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ilike '%$webvar{desc}%')".
     113        " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ilike '%$webvar{notes}%')";
     114
     115if ($webvar{alltypes} ne 'on') {
     116  $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in (";
     117  foreach my $key (keys %webvar) {
     118    $sql .= "'$1'," if $key =~ /type\[(..)\]/;
     119  }
     120  chop $sql;
     121  $sql .= "))";
     122}
     123
     124if ($webvar{allcities} ne 'on') {
     125  $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in (";
     126  $sth = $ip_dbh->prepare("select city from cities where id=?");
     127  foreach my $key (keys %webvar) {
     128    if ($key =~ /city\[(\d+)\]/) {
     129      $sth->execute($1);
     130      my $city;
     131      $sth->bind_columns(\$city);
     132      $sth->fetch;
     133      $city =~ s/'/''/;
     134      $sql .= "'$city',";
     135    }
     136  }
     137  chop $sql;
     138  $sql .= "))";
     139}
     140
     141
     142# gotta find a way to search cleanly...
     143
     144#if (!(!$webvar{cidr} && !$webvar{custid} && !$webvar{desc} && !$webvar{notes} &&
     145#       $webvar{alltypes} && $webvar{allcities})) {
     146#  $sql .= " INTERSECT ";
     147#}
     148#
     149#$sql .=
     150# ($webvar{custid} eq '' ? '' : "SELECT cidr,custid,type,city,description FROM searchme WHERE custid LIKE '%$webvar{custid}%'").
     151# ($webvar{desc} eq '' ? '' : " $sqlconcat SELECT cidr,custid,type,city,description FROM searchme WHERE description like '%$webvar{desc}%'").
     152# ($webvar{notes} eq '' ? '' : " $sqlconcat SELECT cidr,custid,type,city,description FROM searchme WHERE notes like '%$webvar{notes}%'").
     153#"";
     154
     155print $sql."\n";
     156
     157$sth = $ip_dbh->prepare($sql);
     158$sth->execute;
     159
     160print "<table border=1>\n";
     161while (my @data = $sth->fetchrow_array) {
     162  print "<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td><td>$data[4]</td></tr>\n";
     163}
     164
     165#print "<pre>\n";
     166#foreach my $key (keys %webvar) {
     167#  print "key: $key     value: -'$webvar{$key}'-\n";
     168#}
     169#print "</pre>\n";
    92170
    93171} else {
Note: See TracChangeset for help on using the changeset viewer.