Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Re: DBI - Multi-field SQL: *OR* condition w/o specifying field names...possible?

by Arguile (Hermit)
on Oct 05, 2002 at 04:39 UTC ( [id://202997]=note: print w/replies, xml ) Need Help??


in reply to DBI - Multi-field SQL: *OR* condition w/o specifying field names...possible?

It's possible. I've never deeply explored SQL::Statement or AnyData, but a generic approach using straight DBI is easy.

Grab the field names of the table (using either DBI's not-completely-supported-by-all-DBMSs metadata functions or a DBMS specific query). Using that list dynamically construct the ‘OR’ section of the WHERE clause.

While that method would do what you're asking for, it's not all that useful a query and quickly becomes very very expensive. The prefered approach for such a query is to use a full text search engine, which is generally DBMS dependent.

A good FTS will allow you to give weightings to fields (such as finding ‘foo’ in the title meaning more than just finding it in the body), search on multiple terms with the option of giving higher weightings when terms are close together, stemming, etc. All with a reasonable cost (I'm talking machine resources).

Many popular RDMBS packages have FTS engines either included or as additions. Oracle has many included tools as well many third party options, MS SQL Server has some FTS included IIRC, MySQL has rather limited FTS capability, and PostgreSQL has a few options (check under contrib/ or OpenFTS which is actually written largely in Perl). DBMSs’ that aren't listed here probably have such options, I just can't remeber or don't know them well enough.

Sorry for the brevity of the reply, but it's late and I don't have time to go into the topic deeply. Hope it helps anyways.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://202997]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (8)
As of 2024-03-28 11:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found