How to combine effective keyword search with structured data criteria

I'm curious what solutions people are employing to provide effective solutions for searching text and structured data in combination.

For example, I have a collection of documents stored in a database and I need to make them searchable using both keywords and categories. The categories are stored using a typical, normalized database structure and are exposed to the user as checkboxes and select lists on the search form. In this case, for a document to match the search, it has to match the keyword phrase and also be associated with all of the categories selected by the user. The user can omit either the keyword phrase or categories and just search using one or the other.

I have the category part handled. But how to get effective text search to go with that is trickier. The application is running on PHP 4 & MySQL 4.1. I'm currently using MySQL's full-text search features, but they leave quite a bit to be deed -- e.g. there's no stemming, it doesn't ignore punctuation, etc.

I'm interested in finding out how other developers have effectively handled this kind of situation. I'm considering ditching, or at least supplementing, MySQL's full-text search in favor of some more effective keyword search solution. I've been taking a look at Google Site Search for example. I think that would probably provide a more effective text search solution than using MySQL full-text, but I don't know that there'd be a way to integrate that with the structured data that needs to be part of the criteria for searches in my app.

Any suggestions

Posted On: Thursday 25th of October 2012 10:50:29 PM Total Views:  357
View Complete with Replies

Related Messages:

how to combine two columns of a table to some columns of another table   (142 Views)
i have 2 separate databases... in the 1st database i have a table with 14 columns and in 2nd database i have a table with 7 columns.i want to make this as a single table with 3 columns from 1st table and 2 columns from 2nd table. also both tables contain some common to match two tables using .net
Can you combine two SELECTs which join on the same field (non trivial)   (119 Views)
I have a table (choices) which includes two fields (first_choice,second_choise) which contain ID numbers of items, and an identifier for the row (choiceid) I have another table (items) which contains fields including the item ID (itemid), name of the item (name) and others. 1) Is it possible to create a MYSQL statement that would enable me to create a table containing the names of the choices If I was doing this in PHP I would code two seperate SELECTs... SELECT choiceid,name FROM choices,items WHERE choices.firstchoice=items.itemid ORDER BY choiceid SELECT choiceid,name FROM choices,items WHERE choices.secondchoice=items.itemid ORDER BY choiceid ... and then write a function to combine the two results by matching IDs. 2) Is it possible for the statement to work when the second choice has a value of 0 (and so will not have a value in the items table) My guess is that this may be possible with some form of JOIN statement. 3) I could do with the results being ordered in alphabetical order by first choice, and then second choice. This may be impossible, but I can hope.
How can I combine my two queries?   (114 Views)
I am trying to SELECT all the scheduled rules of all the suupliers but cant figure out how to join my two queries: 1.) THIS QUERY SELECTS ALL THE SCHEDULE OF ALL THE SUPPLIERS. THE FIELD condition_value is the suppliers unique code number: "select rules.rule_id, name, start_date, end_date, condition_value from rules, rules_schedule where rules.rule_id = rules_schedule.rule_id" sample output: rule_id| name | start_date | end_date | condition_value | 1 | first rule | 2009-01-01 | 2009-02-02 | 1234 | 2.)THIS QUERY SELECTS THE SUPPLIERS PP-ID- JUST SOME ID AND "NAME" AND "ASI" - WHICH IS THE SUPPLIERS UNIQUE CODE NUMBER OR SAME AS THE condition_value in query#1: "SELECT pp_id, (SELECT value FROM suppliers_attribute_map INNER JOIN suppliers_varchar USING (value_id) WHERE suppliers.supplier_id=suppliers_attribute_map.supplier_id AND meta_attribute_id=215 AND approved=1) as supplier_name, (SELECT value FROM suppliers_attribute_map INNER JOIN suppliers_int USING (value_id) WHERE suppliers.supplier_id=suppliers_attribute_map.supplier_id AND meta_attribute_id=127 AND approved=1) as asi FROM suppliers" sample output: pp_id | supplier_name | asi | 00-01 | ABC CO. | 1234 |
How to combine text with 'IN' and a subquery?   (201 Views)
Quite hard coming up with a title for this post This is formatted with some PHP but I thought this forum was probably more appropriate. Basically, I have the following query: Code: SELECT objName,objID,objImg FROM wwObjects WHERE objLocation = 'loc_" . $_POST['loc'] . "' OR objLocation IN (SELECT locID from wwLocations WHERE locParent = '" . $_POST['loc'] . "') ORDER BY objName Now, my problem is that objLocation is stored with a prefix (in this case "loc_", whereas locParent is just the number which follows the prefix. This is fine with the '=' because I can just insert the string before the number into the query, however I can't for the life of me work out how to do the same thing with the 'IN' and subquery.
UPDATE combined with SELECT gives headaches   (119 Views)
Hi all, I am trying to combine an UPDATE with a SELECT statement to change multiple rows. The query works without errors, but whatever I try, zero rows are affected (and that's not what it should do, or better, what i would like it to do). I've searched everywhere on the Internet to find a solution but to no avail, so that's why I'm trying here. The SELECT statements all work fine on the table when I run them seperately, with the deed results, but combined in the UPDATE, I get no results (no affected rows). Here's the query: UPDATE forum_article SET childs = (SELECT COUNT(`response_to_forum_article_id`) FROM (SELECT * FROM `forum_article`) AS X WHERE `response_to_forum_article_id` '0' GROUP BY `response_to_forum_article_id`) WHERE forum_article_id = (SELECT `response_to_forum_article_id` FROM (SELECT * FROM `forum_article`) AS Y WHERE `response_to_forum_article_id` '0' GROUP BY `response_to_forum_article_id`) Hope somebody has a clue. The subqueries are there because I learned that MySQL needs aliased subqueries when the UPDATE and SELECT statements are affecting the same table. Also, to explain a little bit more, the 'childs' is the column that needs to be populated.
Want Like '%term%' where term=[keyword set]   (115 Views)
Hopefully the subject describes the problem. I basically want to select all rows where a particular field matches a given term, where that term could be any term listed in another table. Is this possible with a single SQL statement, or would I need to run a seperate statement for each term TIA
Possible to combine these 3 SELECT's into one query?   (112 Views)
I have 3 SELECT queries and I am trying to combine them into 1. 3 tables: 1. 'article' table (id, title, ...) -- holds title, subtitle etc. 2. 'subtypes' table (id, subtype) -- holds the subtype of each article (e.g if article type is "news", the subtype might be "local".) 3. 'keyword' table (id, keyword) -- holds any number of keywords associated with each article. [Note in my particular case only one type of article has a subtype, so that is why I had to split it off into it's own table.] The 3 queries I have for pulling an article (with for example, id = 266), it's subtype (if any) and it's kewords from the database are: Code: select * from articles where id = 266 select subtype from subtypes where id = ( select subtype_id from articles2subtypes where article_id = 266 ) select * from keywords where id in ( select keyword_id from articles2keywords where article_id = 266 ) 'articles2subtypes' table matches articles with their subtype (if any), and 'articles2keywords' table matches articles up with their associated keywords. I would really like to combine those 3 queries into 1 if possible. Is that possible, and what would the query be THANKS!
Can this query with two tables be combined?   (131 Views)
Right now I have two different webpages that give me the data I need. But I was wondering if I could combine them onto one report. Here are the two queries I use: $dailySearches = "SELECT DATE_FORMAT(date, '%Y-%m-%d') as Date, COUNT(*) as Searches FROM searches GROUP BY DATE_FORMAT(date, '%Y-%M-%D') order by Date desc limit 120;"; $dailyLogins = "SELECT DATE_FORMAT(last, '%Y-%m-%d') as Date, COUNT(*) as Logins FROM users GROUP BY DATE_FORMAT(last, '%Y-%M-%D') order by Date desc limit 120;";
Speeding up queries with combined indexes   (118 Views)
Does it speed up queries to use a combined index on the fields you're searching one, rather than indexes on each field eg if we were to query with the condition: Code: SELECT * FROM my_table WHERE col1 < 100 AND col2>300 If we have an index on col1 and and index on col2, the optimiser would only use one of the indexes, would it not however if we added Code: alter my_table add index combined_index(col1,col2) The optimiser seems to prefer this joined index. I haven't run any benchmarks, but is this the way to go Utilising a combined index for a combination of fields you are likely to use in your condition Maybe there's a better term for it that I could look up.
Can these queries be combined?   (125 Views)
I am retrieving a set of rows and then running a loop for each of the rows. Inside the loop there is a query to find out the total number of related rows. Is it possible to do it all in one shot instead of running separate queries Code PHP: $questions=mysql_query("SELECT id, key, content FROM table WHERE type='question'"); while($row=mysql_fetch_array($questions)){ $answers=mysql_query("SELECT COUNT(id) FROM table WHERE key='{$row['key']}' AND type='answer'"); }
Anyway to combine these together?   (116 Views)
Hi , Is there anyway of combining these to pieces of code together So the query can count the messages that are stored for that user Code: // count how many new messages they are for that user $new_count = mysql_query(" SELECT COUNT(new) as NumNew FROM gallery_messages WHERE userId='".$got_id[0]."' AND new='1' "); $new_counted = mysql_fetch_row( $new_count ); mysql_free_result( $new_count ); // count how many messages have been replied and stored for that user $rep_count = mysql_query(" SELECT COUNT(replied) as NumReplied FROM gallery_messages WHERE userId='".$got_id[0]."' AND replied='1' "); $rep_counted = mysql_fetch_row( $rep_count ); mysql_free_result( $rep_count ); Thank you for your help and time, spence
How do I combine tables in MYSQL so that 2 different sites share the same username an   (124 Views)
I have a site using WebNetwork by WebScribble and phpbb.... They share the same database. My goal is to log into one WebNetwork and not have to log in again when bridging to phpbb... phpBB is the application I use to run the web forum on my WebNetwork site. I need to sync username and password functions only... as I don't want administrators and moderators of the forum to have that power on the webnetwork site.... More info... table for WebNetwork is "sn_user" table for phpBB is "phpBB_user" Please help! - Ken
Muliple databases or one combined?   (121 Views)
I have multiple database-driven webapps that I'd like to install on a single server (bugDB, wiki, etc). From a performance standpoint, what makes more sense: 1. Create a database for each of the webapps 2. Create a single database for all of the webapps and differentiate the tables by appending a string at the beginning.
Please help me combine these queries   (112 Views)
Original Query: SELECT * FROM `tx_categories_build` WHERE `galleries` >= 20 ORDER BY `name` But I also need to do this as well.. but in ONE query. SELECT tag FROM `tx_categories` WHERE name LIKE 'the name column' SELECT COUNT(*) as MOVIES FROM `tx_galleries` WHERE format='movies' AND categories LIKE '%tag%' SELECT COUNT(*) as PICS FROM `tx_galleries` WHERE format='pictures' AND categories LIKE '%tag%'
Searching keyword against two fields   (113 Views)
dear all, i would like to be able to search a keyword, and match it against 2 columns. so for example, I have cli_fname and cli_sname fields, and I want to be able to search for "mark williams" and have mysql match the search term against the two fields, but treated as one (if I am making sense) I have tried CONCAT(cli_fname,' ',cli_sname) as cli_name, but if I add %keyword% LIKE cli_name, I get an "unknown field" error. is there a way to do this I asked a similar question at the end of this post too: Many
How to combine these 2 sql statements   (164 Views)
so i got 2 tables. checks id int gid int ... user id int status enum('active','suspended') ... so one sql that i want is select count(*),u.status from checks c left join user u on where c.gid>0 group by u.status but then i also want one that just says select count(*) from user where user='active'; is there a way i can join those 2 into 1 single sql statement one thing to note is that not all id in user exist in check (check only has a subset of entries in user ) basically i need to know all the users that are active, and then all those active users that have entries in checks and gid > 0 , but then i also want to have the number of active users as well regardless if they have entries in checks or not. thanx
Possible to combine these three queries?   (102 Views)
I'm not using mysql v4.1!!! Is it possible to combine these queries into one Code: SELECT max( id ) FROM table WHERE id > '$my_id' SELECT * FROM table WHERE id = '$my_id' SELECT min( id ) FROM table WHERE id > '$my_id' I am trying to use the query to develop a "previous" and "next" navigation system.
ORA-00905: missing keyword   (94 Views)
hey , new to SQL, trying to run this simple query SELECT * from STAFF WHERE REVIEW between 31 JANUARY 06 and 31 AUG 06 its coming up wit an error such as: ORA-00905: missing keyword anyone know how to fix this please
Using keyword = 'believe' instead of match(keyword) against('believe')   (195 Views)
The first query produces many rows. The second query doesn't produce any. If I change the keyword to lemon instead of believe, then both queries produce results. Any ideas on this one select * from searches where keyword = 'believe' order by sID; select * from searches where MATCH(keyword) AGAINST('believe');
MySQL: Fulltext v/s LIKE %keyword%   (392 Views)
Hi I was trying to learn what a Fulltext search is and was wondering: 1) How is it different from the LIKE '%keyword%' operator 2) Why should I use a Fulltext search and not the LIKE operator or when to use what Can someone please explain this to me Many