Best practice for searching trough database

Databases
1

Hi.

I’m currently involved in a project involving allot of database management and I need to find a good way to search trough several attributes in several tables.

The guys who I are building the application for wanted to be able to search for random stuff, like phone numbers, names, ipadresses, billing info etc.to find relevant info based on different bits of data found in different tables.

I’d prefer using MySQLi but creating a prepared statement to search trough maybe 10-20 attributes in 4-5 different tables seems to me to not be optimal.

I have looked at full text search, but allot of the information stored are stored as int type and can’t be searched trough with full text unless converted to text types.

2

Thanks for the responses.
I think I’ll try to use the dump table concept.
Maybe create a trigger on the “real” tables to insert data in the dump table as they are updated/inserted into?

3

That tactic works well if you don’t have horribly frequent updates depending on database implementation. If anything, it is a great start until you need to upgrade.

4

Some sort of full-text search is probably the best option. Depending on how loose or structured you need it there are lots of angles. Easiest one is to just create a “search dump” table consisting of 2 fields: a big text field with all the stuff you want indexed then a key column tracking back to the ID of the actual data. Fulltext index that big text field and run searches against it. Results depend on skill level of fulltext index.

If you need to get fancier, I’d start by looking at something like apache solr, which is a web-server wrapper around Lucene and is quite, quite capable.

5

Hi,
any chance you could dump the database and grep the contents?
You could use regular expressions to enhance your searches.

All this assuming you have access to a Linux environment.

6

I’m developing in a windows enviroment, but I assume it will be run on a linux server when it’s done. But I have to be able to do this within PHP.

Will converting all the attributes that currently are ints to varchar and using full text search be an option?

7

Mmmm…

It can more complicated but… maybe this simple approach can feed your
needs: Create an stored procedure with all the search params that you need,
set the default value of each param to NULL.

Now in the select where you filter use this approach (pseudo SQL here):

DECLARE My Store Proc
MyParam1 Int = NULL,
MyPAram2 varchar(80) = NULL,
(…)

SELECT …
FROM …
WHERE
(MyParam1 IS NULL OR tablefield1 = MyParam1)
AND
(MyParam2 IS NULL OR tablefield1 = MyParam2)

By using this approach you control if the param has been informed using
the IS NULL and the OR switch, and you avoid having chunking T-SQL IF
statements.