Database search

[msteinblock]

I am looking for some help on how to setup a search engine for my database. Right now I have 4 different tables, all relating to each other thru different keys. I currently use a search for where you must use drop downs to fill in text fields. I want to change over to a search system that is just one text field that searches all the fields.

First question, do I need to create an additional table for keywords? If so, how do you keep that up to date? I get photos and information added every hour.

Do I need to look into a full text search or something different than the simple search/where?

I want to learn this and am looking for some hand holding to give me some guidance.

Thanks for the help!!

~Matthew

http://Locophotos.com

 

[TonyGroves]

You don't need a separate table for keywords.

If you use TEXT-type fields and have full-text indexes on those fields, then you can use the MATCH function to return relevance ratings for each record against supplied keywords, similar to web-search engines. This is suitable for large numbers of records and fields which may hold large amounts of text.

 

[msteinblock]

Man...what you just said got me lost.

Not all of the fields are text. Some are INT, some are VARCHAR. You are saying all these need to be TEXT?

What are full-text indexes? The indexes I have on each of the tables are INT, with each one showing a unique number.

Sorry for being dumb....please dont give up on me.

~Matthew

 

[msteinblock]

I was going thru some notes from a while back and it looks like I tried this once before. Here is what I have written down that did not work.

$sql_query1 .= "
, MATCH( City ) AGAINST('%$QuickSearch%') OR
MATCH( State ) AGAINST('%$QuickSearch%')OR
MATCH( Remarks ) AGAINST('%$QuickSearch%')
FROM Photos INNER JOIN Roster ON Photos.RosterID = Roster.RosterID WHERE
MATCH( FirstName ) AGAINST('%$QuickSearch%')OR
MATCH( LastName ) AGAINST('%$QuickSearch%')
FROM Users INNER JOIN Photos ON Users.UserID = Photos.UserID WHERE
MATCH( LocoID ) AGAINST('%$QuickSearch%')OR
MATCH( Model ) AGAINST('%$QuickSearch%')OR
MATCH( SerialNumber ) AGAINST('%$QuickSearch%')OR
MATCH( FrameNumber ) AGAINST('%$QuickSearch%')OR
MATCH( BuilderDate ) AGAINST('%$QuickSearch%')OR
MATCH( Lineage ) AGAINST('%$QuickSearch%')OR
MATCH( Notes ) AGAINST('%$QuickSearch%')
FROM Roster INNER JOIN Photos ON Roster.RosterID = Photos.RosterID WHERE
MATCH( ReportingMark ) AGAINST('%$QuickSearch%')OR
MATCH( RoadName ) AGAINST('%$QuickSearch%')OR
MATCH( Category ) AGAINST('%$QuickSearch%')
FROM Roads INNER JOIN Roster ON Roads.RoadID = Roster.RoadID";


I did not that not all those fields are full-text. I tried changing one from char(2) to full-text, but it would not let me. I am getting more confused as I go.

~Matthew

 

[TonyGroves]

You can certainly use VARCHAR fields, but for big (several K) text fields, the TEXT type is usual (and essential until recently).

If you want to search for an integer-field value, you can say something like:[tt]
SELECT * FROM tblname WHERE intfld = 123[/tt]

If you want to search a VARCHAR field for a particular string, you could use:[tt]
SELECT * FROM tblname WHERE charfld LIKE '%search%'[/tt]
This will return all records containing the character string "search", including such words as "researching" and so on.

However, if you want the flexibility of Google-style searching, where you search for words not strings, and can specify exclusions and other advanced criteria, then you need to use the MATCH function and have a FULLTEXT index on each relevant field. For example:[tt]
SELECT * FROM tblname WHERE MATCH(fld1,fld2) AGAINST('search -rescue')[/tt]
This will return all those records where field "fld1" or "fld2" contains the word "search" but not the word "rescue". The fulltext index is maintained automatically by the system.

 

[TonyGroves]

That last post was written before I saw your post.

 

[msteinblock]

What I ran into doing simple searches, if I searched for say "BNSF 100", nothing would be returned, because BNSF was in one field, 100 was in another. If I made the search do both fields separately, then I would get all BNSF's and all 100's. So instead of 1 photo, I would get back thousands of photos.

Maybe what I am trying to do is not possible. Maybe this is way over my head and I should hire someone to take a look at it. LOL

I can do the simple searches you mentioned in your last post, that is how I curently do it.

http://LocoPhotos.com/Search.php

~Matthew

 

[TonyGroves]

It's not impossible, or even complicated. If you want to search for records containing both the words "BNSF" and "100", in any of several fields, you could use:
[tt]
SELECT *
FROM tblname
WHERE MATCH(fld1,fld2) AGAINST('+BNSF +100')
[/tt]
In this case, there must be a fulltext index declared for the field list (fld1,fld2). The "+" marks indicate that both the words must be present.

It can also be done using LIKE matching, but as explained before, this matches strings not words, and would be slower and more complicated.

 

[msteinblock]

Ok, I will try to get those full text indexes...maybe that is part of the problem.

How do I do multiple tables using that match?

~Matthew

 

[TonyGroves]

Use separate queries.