?
Solved

Perl database Search

Posted on 2005-03-02
15
Medium Priority
?
211 Views
Last Modified: 2010-03-05
Hi,

Here is my Problem:

I have a database with several fields (price,addcontents,dateadvertised etc), i need to be able to search addcontents in conjunction with others like dateadvertised.

My input will be a string like any of the following "holden statesman", "holden AND statesman", "holden OR statesman", ''holden NOT statesman". It may contain any number of criteria for example "holden AND statesman NOT barina" etc. (sort of like a web search)

I need to break the string into parts and search through the addcontents field for any matches. The keywords AND, OR, NOT need to be used in order to determine if the search is a union or an intersection etc. The default if no keyword is specified is AND.

I need this to fit into an sql query with other criteria eg: "SELECT * FROM carAdds WHERE dateadvertised<$somedate AND TheNewSqlCriteria What ever it is.

I think that makes the problem clear. Any help or further questions pls post and i will respond.

Thanks

Peter
0
Comment
Question by:sirius_1
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 16

Expert Comment

by:manav_mathur
ID: 13446924
What database is it??
use DBI ; @see help for this

also, wont the input string, if directly appended to the end of the sql statement, result into another proper sql query??

Manav
0
 

Author Comment

by:sirius_1
ID: 13446973
Sorry i forgot to include all that,

Im using DBI and the database is acess 2000
0
 

Author Comment

by:sirius_1
ID: 13446981
I didnt mean the new sql was appended i mean whatever the extra sql is going to be goes there, it was just an example to illustrate that im going to have other crietia in the sql string
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:manav_mathur
ID: 13447118
Do you have DBD::ODBC and DBI installed?

i.e, how are you going to lay down priorities. This token-parsing looks difficult to be implemented in Perl, but Im not sure coz I havent done this type of thing.
for eg, what will this mean??
"NOT holden OR barina"
"holden AND statesmen OR barina AND statesman"

Manav



0
 

Author Comment

by:sirius_1
ID: 13447155
i have DBD::ODBC and DBI installed. There is no token parsing, all i need to do is read the addcontents out of a database and search through it as i have stated.

"NOT holden AND BARINA"

Basically this means the sql would be like

"SELECT * FROM carAdds WHERE addcontents LIKE "%barina%" AND addcontents NOT LIKE "%barina%"

I think the trick is firstly, breaking up the string into elements and keywords and then secondly writting the SQL string so its in some sort of loop that will loop through all the elements.
0
 
LVL 85

Expert Comment

by:ozo
ID: 13447158
SQL undestands  AND, OR, NOT
if you want to use them in a way different from SQL query syntax
then you may have to do some translation, which should not be hard,
but you'll have to explain what the relationship should be between your
input strings and the SQL querys you want to do.
0
 
LVL 16

Assisted Solution

by:manav_mathur
manav_mathur earned 400 total points
ID: 13447175
What I meant in my question was

"NOT barina OR statesman"

Does this mean "not barina and not statesman"?? Otherwise, what do we OR with??

a basic code that I have come up with(which maybe does not satisfy all requirements) is

my $input = "holden AND statesman NOT barina NOT abcd OR efgh" ;
while($input =~ /(\s*(NOT|OR|AND)\s*(\S+))/ig) {
my $token = $2 ;
my $tok_string = $3 ;
$input =~ s/$1// ;
$final_condition .= "AND \$addcontents != $tok_string " if $token =~ /NOT/i ;
$final_condition .= "AND \$addcontents == $tok_string " if $token =~ /AND/i ;
$final_condition .= "OR \$addcontents == $tok_string " if $token =~ /OR/i ;
}
while ($input =~ /\s*(\w+)\s*/g) {
$final_condition .= "AND \$add_contents == $1 "  ;
}
print "Final condition after : $final_condition\n" ;

Manav
0
 
LVL 85

Expert Comment

by:ozo
ID: 13447215
for my $input ( "holden AND statesman NOT barina NOT abcd OR efgh" , "NOT holden AND BARINA" ){
  $_ = $input;
  s/\b(?!(?:AND|OR|NOT)\b)(\w+)\s+(?!(?:AND|OR)\b|\s)/$1 AND /g;
  s/\b(?!(?:AND|OR|NOT)\b)(\w+)/addcontents LIKE "%$1%"/g;
  print $_,"\n";
}

0
 

Author Comment

by:sirius_1
ID: 13447252
Thanks Manav thats the sort of help i was looking for i think i can work it out from there but give me a few days to get it working and check, and i will get back to you.

0
 
LVL 16

Expert Comment

by:manav_mathur
ID: 13447267
See Ozo's code, it's more in tune with your requirements. The code hurts your fingers less,  but it racks your brains more.

Manav
0
 
LVL 85

Accepted Solution

by:
ozo earned 600 total points
ID: 13447276
for my $input (
    "holden AND statesman NOT barina NOT abcd OR efgh" ,
    "NOT holden AND BARINA",
   "NOT barina OR statesman",
   "NOT (barina OR statesman)",
    "holden (statesman NOT barina) (abcd OR efgh) NOT holden BARINA" ,
){
  $_ = $input;
  s/(?!\b(?:AND|OR|NOT)\b)(\b\w+|[)])\s+(?!(?:AND|OR)\b|\s)/$1 AND /g;
  s/\b(?!(?:AND|OR|NOT)\b)(\w+)/addcontents LIKE "%$1%"/g;
  print $_,"\n";
}
0
 
LVL 16

Expert Comment

by:manav_mathur
ID: 13447300
I think http:#13447276 is really what you are looking for......quite brilliant.

Manav
 
   
0
 

Author Comment

by:sirius_1
ID: 13447509
Yep i saw that its great, it will just take me a couple of days to get the time to implement it into my code and make sure it all works.
0
 
LVL 18

Expert Comment

by:kandura
ID: 13455261
0
 

Author Comment

by:sirius_1
ID: 13469294
Thank you both manav and ozo they were both excellent answers.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many situations when we need to display the data in sorted order. For example: Student details by name or by rank or by total marks etc. If you are working on data driven based projects then you will use sorting techniques very frequently.…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question