?
Solved

Perl database Search

Posted on 2005-03-02
15
Medium Priority
?
209 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 84

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 84

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 84

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
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

777 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