Create an Onsite Search Window Specific to Site Content

[iwato]

BACKGROUND: I am in the process of integrating Matomo website analytics into my web application and stumbled across an important functional element that is missing on my main page -- namely, an onsite search window that delivers information about the site to the page.  Not only would such an element provide valuable information about what users are looking for on my site -- this, as opposed to what I am offering --, but it would also provide them with a reason to tarry just a little longer on my site.

QUESTION:  Can you provide me with some leads into setting up such functionality.  As most of my content is delivered dynamically, this need not be anything as elaborate as that provided by the InvisionZone domain.

Roddy

[justsomeguy]

What questions do you have about it?  If most of your page content is stored in a database then you would search those fields for what someone types in.  There are several ways to search in MySQL depending on the type of tables and fields.

[iwato]

OK.  I was not thinking in those terms, but I very much like the idea, for it seems that searching a database would be far quicker than searching all of the files of a website -- something that a public search engine can do easily, but whose results would vastly diminish the eventual true value of my site.

Now, I am used to searching field values based on the known value of a particular field, but I have no experience in searching all values of all fields for a select number of tables. This is, indeed, the task that I would want the user to be able to perform.

What is such a procedure called?
What would a typical SQL statement look like?

Roddy

 

[justsomeguy]

You wouldn't want to just search all fields in all tables, you need more intelligence than that.  You would only want to target specific fields that contain the data they might be looking for (there's no reason to search in an autonumber ID field, for example).  If you want to just search for a specific string of text then you can use a LIKE clause in the select query.  If you want a more natural language search then you should look into fulltext indexes, although you'll need to change some of the database structure to support fulltext searching.  Specifically, those columns need to be one of the text data types (tinytext, text, mediumtext, etc), and you need to define a fulltext index spanning all of the columns in the table that you want to include in the search.  You may need to change the storage engine also, I know that InnoDB tables support fulltext searching but I'm not sure about MyISAM tables.

[iwato]

Aha!.  Now I understand the purpose of the FULLTEXT INDEX option.  Alas, I thought that I had finished structuring my tables the last time that I renovated.

One last question before you depart for the weekend, if you have not done so already.  Do you know of any open-source software, such as a jQuery plug-in, that will generate a search-box of the type that we have been corresponding?

Roddy

[justsomeguy]

Not off the top of my head, but the front-end search box is pretty simple.  At the most basic it's just a text field.  If you want features like autocomplete or showing some partial matches you could click on then I'm sure you can find plugins to do all of that, or it shouldn't be too bad to put one together with the other stuff in jQuery.

[iwato]

Yep, I bundled  it on my own.  Nearly two days of work.  Please see my entry in the Javascript forum on simulating a button click with the Enter or Return key.  It is my last issue with the set up.

Roddy