asked on

Perl database Search

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

manav_mathur

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

sirius_1

ASKER

Sorry i forgot to include all that,

Im using DBI and the database is acess 2000

sirius_1

ASKER

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

manav_mathur

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

sirius_1

ASKER

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.

ozo

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.

SOLUTION

manav_mathur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ozo

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";
}

sirius_1

ASKER

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.

manav_mathur

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

ASKER CERTIFIED SOLUTION

ozo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

manav_mathur

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

Manav

sirius_1

ASKER