Search –– change log
written by: admin
written by: admin
Date Written: 3/12/07 Last Updated: 5/29/15
Beta 1 My first attempt in creating a search feature to search the database. This one searches the anime review's summary, basic premise, and conclusions for one typed in word.
It will list some numbered results per page, 3 times what is requested, so if 4 results are requested then 12 results are displayed on each page. Another problem is there may be 12 results on the first and second page, but 8 on the third and 4 on the last page. It took a long time to figure out why. The reason was because it would list all of the results from one column in the table until it ran out of matches and would not start listing the results from the next column in the table until a new page was up. This version won't create links for each result. The numbering system is also off.
Beta 2 With this version the numbering system has been fixed so that only ten results are displayed per page and no results are left out or duplicated. This version required extensive changes to accomplish this.
Beta 3 This version demonstrates truncated results.
Truncating has to do with strlen() and substr(). strlen() is string length and substr() determines which section of characters in the string to return. It should look something like this:
$lmt=500;
if (strlen($text) > $lmt) {
$text = substr($text,0,$lmt);
echo "$text";}
if (strlen($text) > $lmt) {
$text = substr($text,0,$lmt);
echo "$text";}
Beta 4 This version demonstrates linking each result to where it was located. In order to accomplish this I borrowed a short snippet of code from the anime review selection page where the links are auto generated and auto linked. Several minor bugs have also been fixed with this version. I have yet to fix the prev link in this version.
Beta 5 This version has the Prevs button fixed and has the ability to search for more than one word at a time with a limit of 5 terms. Fixing the prevs button required extensive changes. Being able to search for more than one term at a time in order to narrow down results was difficult to figure out, but required very few changes.
I did a tiny bit of cosmetics on the way the searches look making it look just a touch like the way Google does its results. I don't want to do the full cosmetic work until last, so as not to clutter up the script. The next beta version should include at least one of the following:
- (selective) searching of other sections such as manga reviews and theology.
- to be simplified, especially the prevs link, which is more complicated than it should be.
- include the use of symbols and quotes.
- an added measure of security.
- allow for unlimited word search.
Search 1.1 This version searches manga reviews now. The code was slightly cleaned up as well.
Here is a short example of a query
SELECT * FROM anime WHERE premise like \"%$terms1[0]%\" and premise like \"%$terms1[1]%\...
and
SELECT * FROM anime WHERE summary like \"%$terms1[0]%\" and premise like \"%$terms1[1]%\...
and many more queries for each column.
Search 1.2 The script has been simplified so that six while loops have been narrowed to a very efficient one by making use of arrays. This took around three days to do and was not easy, however the result is that the code is simplified a bit and a little easier to understand and update.
Search 1.2.4 Version 1.3 is a big project, so I am going to update it in segments. At present the script has been shortened significantly and simplified as well. The prevs section of the script has been shortened and the manga links have been fixed as well.
Search 1.3 The script has been greatly simplified and has been condensed from 257 lines down to 189
Search 1.3.5 I have updated it so that 7 parts no longer need to be updated. Still working on it. Fixed the prevs bug ...again. I count about 4 lines and one section that need to be updated now and all of which can be yet eliminated.
search 1.4 The while loops now automatically know how many loops to run
two of the big four lines have been rewritten so that they no longer need to be updated. The search now has no limits on the number of search terms you can enter into a query
A bug has been fixed as well.
Search 1.5 Eleven more lines in the easy to update section no longer need to be updated. The links to the next page and prevs page now auto update. This leaves 4 lines left that need to be updated when a new section is added.
This version's major features are that it dynamically searches the database and can be easily updated and adapted to different websites.
Search 2.0 beta 1 For some reason I have started up on this script again. It is hard to figure out everything that I did, so I am rewriting it.
This new version already has a beta 1 version that works. I am not sure how long it will take before I replace the search feature that is already up. This new version has a lot of reconstructive surgery that it needs. Here is a short list.
- Faster queries.
- Eliminate duplicates.
- many many bug fixes. (very big project)
- more intelligent searches involving symbols.
- MySQL database interface for more dynamic future upgrades. (probably version 3.0, so don't hold your breath.)
Search 2.0 beta 2 This version is pretty much the same as the last version, but large parts have been edited out so as to recognize the slightly different design. Links have been fixed. Searches are faster, but the speed difference really is not very noticeable.
Here is an abridged version of what the new queries look like:
SELECT basic_premise, summary, conclusion FROM anime_review WHERE (basic_premise like \"%$terms1[0]%\" and basic_premise like \"%$terms1[4]%\") or (summary like \"%$terms1[0]...
I had to abridge it because the queries were huge, but I only needed one per table. They also eliminated the duplicates problem from the first version.
Search 2.0 beta 3 This version will simplify the script.
I have been going over the script and noticed that all of the search scripts have searched for all of the terms entered and in any order with the exception of some of the earliest scripts, but the search is only returned if all of the terms are found in the one column.
For example the terms 'none yet' are only found in certain reviews where either the basic premise or summary have not been written yet. If I search for 'none yet is' there will be no results returned because the terms 'none', 'yet', and 'is' do not all appear together in the premise, summary, or body of the review even if they all do appear in the article.
Still working on the script.
I still want to finish scanning it for bugs first. The next thing to work on will need to be allowing for unlimited terms in the search field.
I decided to create THIS version primarily because I wanted to eliminate duplicates that were still cropping up. I made it 2.0 as opposed to 1.6 because I had to start over again almost from scratch.
Forget version 2! I am going to move on to version 3! I know that it has been a while since my last update, but it occured to me a while back that the results were not all that accurate in that the search of version 1 and 2 searched each category independantly so that all of the terms had to appear in a column together in order to qualify as a result.
Last night it occured to me how to get the search program to search the document as a whole, but only those columns that I wanted searched.
You can concatenate them so that the columns are searched as one conglomerate as opposed to three seperate entities. Sorry, I wish I could describe this better. Take a look at the following code:
SELECT premise, summary, conclusion FROM review WHERE concat(premise, conclusion) like '%must%' and concat(premise,summary,conclusion) like '%storyline%'
In the above example 'must' and 'storyline' can appear together in the premise or together in the conclusion and it will equal a match. However, the two terms can also occur seperately with 'must' occuring in premise and 'storyline' appearing in the conclusion and it will also match. This wasn't possible in versions one or two. The queries are also much much shorter.
The premise, summary and conclusion are all merged together and searched as a whole as opposed to searching each area independantly.
Search 3.0 test I updated the query part of the search and it worked beautifully. I'll see if I can get some more updates to this program soon. Gotta take care of a few things today yet.
- The code has been trimmed down a lot with the extraneous portions removed.
- I did a deep scan of the prevs section and it looked clean, so it looks like previous bugs that were in version one were introduced when I made improvements.
The rest is looking good. In case you are wondering I am working off of the Search 1.1 version that I did a while back. I would have done a later version, but the program just looked too complicated, so I had to start with something I understood first.
Before I work on version 4 I will need to develop my understanding of Javascript. The program will search in the normal way and then additional pages will simply do a query with a LIMIT added and replace the content of the first set of results without refreshing the page.
Actually, I think that I can add a virtual pagination script from dynamic drive and that will speed things up tremendously. I can do the javascript thing later.
Search 3.1 (7/5/07) This version is rather amazing. It uses the virtual pagination script from dynamic drive so that the query needs to only be done once. It still needs a bit of work yet, but already it looks wonderful.
Another version that I am not so sure that I even want to try at this point would only execute a query when the "next" or "prevs" button is clicked. However I think that I will add that feature only when the queries start to take too long to execute.
Search 3.2 (7/7/07) Added arrays to the program to ready it for the admin page.
Added 'title' to the list of cols searched.
Added unlimited search terms to the search program.
Search 3.3 (7/17/07) This version is customizable with the admin page. Two additional files were created for this version and a MySQL segment was set aside for it as well for a total of 3 files used to run the search program now.
Added the loops to the program.
Search 3.4 (7/20/07) This version is much easier to use. All I have to do is type in the category to search and the program generates the rest of the code. Lots of bug fixes as well, like links have been corrected and all of the verses in daily bible verse are searchable. I made it so that the tables can be entered in any order. In version 3.3 I had to enter in 4 additional columns for a total of 5 and keep track of the order that they were placed in in order for the program to run correctly or at all.
Search 3.5 (7/25/07) I added checkboxes to make selecting tables easier and look better.
Search 3.6 (7/28/07) There were several bug fixes with this version that had to be taken care of before moving on to the next phase of this program. In particular:
1.) The main content files and memoblog files are now accessible.
2.) The subject of each result is now accurately listed and no longer has 'anime' as it's default.
3.) My favorite bug fix is that the pagination links at the bottom of the page is now fixed in place allowing for faster clicking through the pages.
Search 3.7 (7/31/07) The public can now decide what parts of the site to search and can narrow the search by category.
Search 3.8 (10/13/08) Search terms are now lowercase to miss fewer results than before.
Search 3.9 (12/1/08) Search queries are now case insensitive so as not to miss results.
code was changed from
SELECT ID, ID, summary FROM memoblog WHERE ( concat(ID, summary) LIKE '%wordpress%' )
to
SELECT ID, ID, summary FROM memoblog WHERE (lcase( concat(ID, summary)) LIKE '%wordpress%' )
Search 3.10 (12/2/08) Major bugfix where the wrong tables were being searched or not searched. This bug was introduced after sliming down the search program which was done after several tables in the database were removed.
Some bugs still remain:
- cancel button will search instead of cancel.
- Search terms are not present in the search field after a search
- "misc articles" should be renamed to articles
- more results need to be displayed per page.
- cancel button was removed.
- Search terms are present in the search field after a search is performed.
- "misc articles" was renamed to "articles".
- Added the ability for the webmaster to determine how many results to display per page.
- Removed several lines of empty code.
- Bug where limiting searches by category would pull the results, but not display them has been fixed.
- Decreased the height of the results div by 10px.
- Replaced the database connection with an include command.
- Removed several lines in the search stuff page.
- Fixed an old bug where maincontent was not always being searched.
- The admin section of the search feature has been completely rewritten.
- The user interface is much more advanced and easier to use and more versatile.
- It scans the database for all tables and allows the user to check the tables he wants to search. The user then selects which columns from the tables selected that will be searched.
- There is a space next to each table for entering the links of the results.
- The selections are remembered to make editing of search parameter easy.
- Code no longer has to be carefully edited in order to add or remove a table from the search parameter.
- Instructions have been added as well.
- Updated the virtual pagination part of the script. Going back to the search page will no longer reset the search results to page 1. This script from Dynamic Drive is also more advanced and customizable.
- Updated the pagination format located at the bottom of the search page.
- The database for the search feature has been updated from MySQL 4.0 to MySQL 5.0 series.
- Search terms then became case sensitive. This has since been fixed by updating the sql query to
SELECT ID, ID, summary FROM memoblog WHERE (lcase( concat(cast(ID as char), summary)) LIKE '%wordpress%' )
Search 3.16 (1/8/10)
- increased the security so that if a specific column contains a flagged word then the result will not be displayed. This is site specific at present so it won't work when applied to other sites; at least not yet anyway. If the user is logged in as the administrator then the private results will be displayed.
Search 4.0 (1/14/10)
- This is an almost complete rewrite of the code.
- The checkboxes are now quite dynamic and fully integrated into the search program.
- The names of the display names for the searchable tables can be set via the admin search page as well as the number of results per page.
- The bug that was eliminating results if there was a NULL value anywhere in the result has been fixed.
- some simplification of code.
SELECT ID, summary, image FROM memoblog WHERE (lcase( concat(cast(ID as char), summary, IFNULL(image,''))) LIKE '%yoo%' )
Search 4.1 (1/15/10)
- The testsrch2.php and testsrch3.php pages have been updated with cleaner code that makes more sense.
- Updated the CSS for the search boxes to make the text larger and add some spacing.
- if no columns are selected then go back to the previous page and display a warning message (admin)
- Security update to prevent sql injection.
- terms can now be placed in quotes to search for exact phrases. I am not sure what to do yet about odd numbers of quotes though.
- Search results are now ordered by ID. This is is a site specific update and may need to be customized when applying the program to another's site.
Search 5.0 (5/6/10)
- Started using Ajax Pagination script so as to accommodate queries that produce very high results. Ajax Pagination greatly reduces load time to a small fraction of what it was and is now virtually impossible to be slowed down since the results are retrieved one page at a time instead of all at once.
- The queries are no longer separated by table and look something like:
(select ID, title, '' as missing from anime WHERE (lcase( concat(cast(ID as char), summary, IFNULL(image,''))) LIKE '%e%' ) order by ID)
UNION
(select ID, ID, title from misc WHERE (lcase( concat(cast(ID as char), summary)) LIKE '%e%' ) order by ID)
UNION
(select ID, title, date2 from manga WHERE (lcase( concat(cast(ID as char), summary, IFNULL(image,''))) LIKE '%e%' ) order by ID)
limit 0, 10 - Cleaned up the CSS for a cleaner look.
- Added a link to the old version. Both will return the same number of results and in the same order. The only difference is in the type of pagination used: virtual pagination in 4.5 or Ajax Pagination in 5.x.
- Fixed bug where searching without entering a search term resulted in an error message.
- The CSS for the search categories was centered, slimmed down and wraps correctly now when resizing the browser window.
- The code was redone by placing the search form in its own file to be retrieved once instead of every time a new page is loaded. This will make the code easier for me to follow and page loads will be faster.
- Search terms and phrases can now be negated.
- Added annotation to the script. This makes future updates much easier.
- I have decided to not work on upgrading the old version of the script, but I will leave it up as another option for people.
- Bugfix: Number of search results is now updateable via the admin config page.
- Security upgrade where code could be executed when displaying the search terms entered.
- Minor Security update where the results # displayed the number of results that would be displayed if the user were logged in as the admin. The correct results were still displayed. Only the integer was wrong. Due to the nature of this update, LIMIT is no longer used in the queries.
- Minor bug fix where an error popped up when article was not selected before a search.
- Minor bug fix where the excerpts of the search results may overlap on lower resolutions. The height of the results were originally fixed to make it easier to go from one page of results to the next when I was using virtual pagination, however with ajax pagination fixed height is no longer needed.
- bug fix: the number of results per page can be set once again.
- Fixed a tiny issue where – displayed as a square in search results.
- Fixed an issue where searching columns that were in the datetime format produced an unusual number of results. This fix is more of a workaround than a real fix though.
- Fix where an error message would display if a search term was entered with no categories selected.
- Tiny fix to get –, ‘, and — (–, ’, —) to display as is as opposed to a hyphen or a boxed question mark.
- Take steps to make the search feature more "plug and play".
- Make the "ORDER BY" aspect of the tables customizable.
- Change the spaces in the table names to
- Add an option to deselect all checkboxes.
- Save search categories in a cookie.
Useful links
text indexing
full text indexing dev.mysql.com
TAGS: website, search feature