SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best practice for searching trough database.

    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.
    Last edited by emgenor; Sep 1, 2011 at 06:21. Reason: Spelling errors

  2. #2
    SitePoint Member
    Join Date
    May 2011
    Location
    London, UK
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Kantsev Linux Laboratories
    t: 02071832945
    w: kantsev.com
    Managed Cloud hosting and Server Management, UK

  3. #3
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Member
    Join Date
    Aug 2010
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    Non-Member
    Join Date
    Aug 2011
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •