Before I get into my problem, I'll lay out a couple things:
Basically I've made a searchable image database for very high-resolution blueprint sheets. This will be deployed as my company's intranet, and will be available remotely for contractors and advisors in the field. The total amount of records initially being inserted into the mySQL db is ~3000.
What I've already made is an add blueprint page where a user submits all the data and provides the blueprint to be uploaded - it gets renamed to the print's unique scan_id and gets sent to the proper image folder. Eventually to come will be a script for it to take the high-res and make it a low-res .jpg for previewing - but first I need to know about searching.
The search portion will actually contain two sections - browsing and searching. Browsing will return very general results, whilst searching is hoped to return more specific results for those who know what they're looking for.
What I have done is preliminary, and very simple. Basically it only searches the rows for one keyword and displays the results in a paginated form. Here's a live, working example. Type 'test' into the keyword box and click Find 'Em - dont worry, those're all different records in the DB: http://www.darwenstheory.net/intranet_test/search.php
This is the query used:
SELECT * FROM images WHERE s_Description LIKE '%".$_REQUEST['keyword']."%' ORDER BY p_Site
As you can see, this isn't very effective - try searching for more than one word, and it throws errors.
So, this is where I need assistance. I need a more efficient way of searching the table for all words inputted for ALL columns in my table. Someone provided me this example:
SELECT * FROM table WHERE MATCH (col1,col2) AGAINST ('string');
As I understand it, this does not return actual field data from the db, rather a decimal relevance value. I have not the first clue how to handle that after the query is made.
My results page is to be simple, with users being able to preview / print / download the blueprints right from that page, with all the data provided for them. If I have to, as a last result, I will generate a .php page for each blueprint after it is added to the database, but I'm trying to make this search script as little clicks as possible for the end user.
So, the essence of what I need figured out is this:
best way for searching for multiple keywords separated by spaces; searching for phrases using either AND or '&' as a linker.
I am not asking ya guys to do anything for me, rather I am asking for discussion on the best way to do this, with possibilities, so that I may decide and code for myself the best possible way of searching my database. Complete code for my current search & results script can be provided upon request. Thanks a lot guys.
