MYSQL :: Search On Keywords

Simple Search String For Keywords...
It's a simple 'when someone uses this keyword, bring back all listings containing that keyword.' Just like when you're searching in ANY db, you put in a keyword and it brings back all THOSE files. Now does that sound so impossible? For some reason, my last host's programmers and my brand-new host's programmers then a couple of paid programmers gave up. It's a standard string used in all dbs I would suppose, hence the need for databases-- databases search and they are a collection of data arranged for ease and speed of search and retrieval. So why have so many programmers tried to fix this simple string and failed? HELP. I am not a programmer and my site has been down now since before Christmas when I moved it from one server to another and "it got broken?" or something happened in the dump file.

Search Design: Keywords Table (MySQL)
I'm trying to implement keyword searching in my database.
Currently, I have a table containing all my entries with description and name.
The existing sql search utilises fulltext search to find the relevant data from the description and name fields. I'm thinking of adding an additional keyword table that would have higher relevancy in returning results (currently, you'll be lucky to find any relevant entries).
I have a separate table and the design is as follows:
REATE TABLE `keywords` (
`id` smallint(5) NOT NULL auto_increment,
`entryid` mediumint(9) NOT NULL,
`keyword` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;
My other table that contains the description and name fields are `entries`

My problem now is, how can I match the search keywords with that of the table `entries` and `keywords`? I want to avoid using more than one sql query for each search.

For each entry in the table `entries`, there may be up to 20 keywords (e.g. 20 rows in `keywords` table). I'm open to another approach for the keyword table. The reason why I have a separate table is to facilitate management and hopefully be able to pool results in a way of a cloud for keyword statistics.
Can anyone offer any advice/help in this regard?

For Using Keywords
Level of experience in SQL or MySQL = novice;

I am trying to create a study notes database in MySQL and populate it with records (I currently have about 40,000 questions text format and they keep growing at the rate of 25-30 questions/month).
I expect one of the fields in the database to use TEXT as a field type. Heard from experts in the past that using a TEXT field or VARCHAR field can slow the process of searching the database. The questions I plan to use in my database are however TEXT field type.

I also intend to use a list of KEYWORDS in another field hoping to speed up the process of searching the TEXT field. (So that I can ask the user most of the time to search using keywords that will search a list of keywords than the entire text field in the database) I plan to use PHP to splice out common words from the TEXT field at the time of data entry so that the user is presented with a set of words or word combinations which they can add to the database with each record.

How should I define the KEYWORDS field type? Should it be created on a different table so that each keyword or set of keywords per record goes into a different record in the KEYWORDS?

Say for example here is a sample question.
"The most abuntant type of diatom in the genus diatomaceae is COMPLICATEDaceae"

the keywords I would use would be "most abuntant", genus, diatomaceae, and COMPLICATEDaceae. Since "most abuntant" could also be searched as "most common", how should I define the KEYWORDS table so that adding keywords to the list could be most efficient?

Using Keywords In SQL
I have a column called Condition and I would like to create a sql string for example
SELECT Condition FROM MyTable
But I get an error message
You have an error in your SQL syntax; ErrorNr 1064
I know Condition is a keyword so how can I use this??

Keywords
I am trying to find out how to search for relevant rows. Say someone searches for "cats dogs" and I have a row with the field "cats are nicer than dogs". I would not get any results back using the %$search%. What is the best way to make a search engine?

Two Keywords First Each Keyword Later
data in myTable

(id) myString
(1) I love you.
(2) Here we go.
(3) I love my boy
(4) Exactly wrong
(5) Everything's OK
(6) This is my love.
(7) This is my book.
(8) That is a book.
(9) I knew it.
I have data in myTable like the above.

The following code will produce the following result......

Storing Keywords
I did a search on storing keywords and found a post where someone suggested storing keywords in a 1-to-many table scenario. I'm not sure I follow. Here's what i'm thinking of doing. I'll be storing a Word document that would be tied to various keywords. I want to be able to search by keywords and get back a query of various documents that match up to the keywords.
I figured i'd make a page to list the document and then input keywords and store them in the database for later searching.
What would be the best way to store these keywords for each document?
hope that wasn't to confusing. Thanks for the help.

Multiple Keywords
I am trying to write a little script that folks can use to search my db, it is working fine if you put in single words, such as coats, jackets, etc.

I would really like to be able to pinpoint in down with words like mens coats, ladies jackets, etc.

I can take the words off a form and generate a variable like $lookup which contains the words mens jackets, but when I search I get no hits. How should I create my query to include both words?

Storing Keywords
What is the best way to store keywords in a database table.
I have a document, and want to be able to first, register various keywords associated with the document, and then have a page where the user can type in keywords to do a search for matching documents. Then they would eventually select one document to view based on the keyword search.

So....back to storing the keywords. Do I store them all in one field, comma seperated, or is there a better method?

Match Keywords
I have keywords with logical operators "and(+), or(|), and not(-)", and I want to match them with a row in database with similar keywords.

For examples:

a. (good+(morning|night)) -afternoon
b. good+night -morning <---- this is stored in row in the table in DB

It's easy to send a query to match these 2 without taking account of the "NOT morning" in (b). But if you take account of that, these 2 won't match. The only way I thought of so far is to send a query and match (b), then do a 2nd check with the negative keyword to determine that they don't match. But it is not efficient if there are a lot of positive keywords or many rows like (b).

Multiple Keywords
I have a application that creates a large database (1 Million plus rows) one of the fields of which contains about 100-1000 chracters or so of text. I have used a full text index on this field.
I want to search the whole of the database for any records that contain one of a list of keywords (the list could be 100+ entries long - although I may be able to limit this but not by a great deal)
Is there an easy way to search a table for occurences of records that match one or more of a list of terms?

Reserved Keywords
I'm converting a MS Access database to MySQL and encountered the following column names and I'm not sure if it's okay or better not to use those names:

CODE, MASTER (in lowercase by the way)

MASTER made me doubt since there's the whole replicatin situation with master and slave, so I'm not sure if I can end up with problems naming a column 'master'.

SELECTing Keywords
I have keywords in a table column that are seperated by commas within a single string per row.My SELECT statement currently just uses the LIKE operator to test the keywords column against a user's query.I would instead like to use the = operator against EACH keyword instead of a STRING of keywords together, but I'm not sure of what the best way to go about this would be.

Mysql Keywords
here is a strange problem. Probably, i am doing something too stupidly wrong. Please point out.
I create an ALTER table statement in my php script based on user input . Whenever the statement
contains field name "condition" my sql statement fails. So Code:

ALTER TABLE sid.data ADD COLUMN condition varchar(50);

gives error :You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition varchar(50)' at line 1

while

Code:

ALTER TABLE sid.data ADD COLUMN done varchar(50);

runs perfectly, no errors whatsoever. I am using mysql 5 . Any ideas??

Selecting Certain Data From Keywords
Is it possible to create a table where it selects certain rows from a field with the same value (like a keyword) and not showing other rows because the value is not the same. And to list all the rows in a table using php? for example:

If I had 20 rows and say 3 of them had the value 'cat', and I wanted to display only all the values with 'cat', and at the same time it includes the rest of the fields to do with that certain value. But making sure not to show the other 17 rows in a table using php.

Link Keywords To A Page
so my company tasked me with writing an in house search engine. i have to index gigs and gigs of data (~100+) to do just simple keyword searches. the indexing is easy. my problem is i can't think of an efficient way to link the words to the page they appeared on and then store that relation in a database.

Theory Of Matching Sets Of Keywords?
I have 2 database(A & B) tables with sets of keywords in each rows. I am trying to match one row of database A with one or more rows of database B depended on how close they match each other and based on keywords(how similar they are).

I am wondering if there is already function to do that? I am using Fulltext in mysql, but it seems that it's not good at matching keywords if there are too little terms. Is there already a known published theory on this?

AND Keywords Across Multiple Columns, Without Imap
I have a table with, say, 4 columns: id, headline, story, and byline.

I have a search box that takes space-separated keywords.

I need the search box to query for ALL the keywords (i.e. AND) in all the columns taken as a whole.

For example, if my keywords were "tree" AND "cat"

I would have a hit if a record's headline had the word "tree" and the story had the word "cat."

It would also, of course be a hit if the words "tree" and "cat" were both in either the headline or the story.

But it would NOT be a hit if only "cat" existed in the entire record.

I know there's a way to do this with "MATCH" and "AGAINST" but I can't change the database structure to imap.

Is there a way to do the above using WHERE and LIKE?

I tried something like this, to no avail:

SELECT * FROM news WHERE (id,headline,story,byline) LIKE ('%cat%' AND '%tree%')

Search: Remove Quotes From Database For Search
I am making a book site and currently in the database are book titles likes "John's Trip" and "Peter's new bike". I have a search function where the customer can type in "Peter's" and it will bring up everything with "Peter's" in it.

But i was wonderind is there any way i can search for "Peters" and "Johns".

I Have A Search Form To Search Through The DB.
I have a DB with 50,000 entries. I have a search form to search through the DB. If the recno=30123456 and you search the recno, you get that one file. what if i want a query that searches alll recno's that contain 30?? Can I do that using mysql or do I need to program that in my java app?

Best Way To Search
I am a little confused on the best way to search a mySQL database with PHP.

Which is better?
1. Search with LIKE and a trimmed term

SELECT * FROM tablename WHERE field1 LIKE "%$trimm%" OR field2 LIKE "%$trimm%" OR field3 LIKE "%$trimm%" ORDER BY field1 DESC
2. Using mySQL's 'match against'

Search WHERE SUM
What would be the proper way of generating a query like the following;

SELECT id FROM sales WHERE SUM( totalsales) <= '20000' GROUP BY id;

Assuming the table looks like this;
Sales:

Id| totalsales| monthyear
1 | 100| 2003-09
1 | 10000| 2003-08
1 | 2000| 2003-07
2 | 30000| 2003-05
3 | 10000| 2003-06
3 | 10000| 2003-05

I want the query to return id 1 and 2 as the sum of their totalsales is
smaller then 20000.

DB Search
I am working a search tool for users to find members based on First Character of lastname.
How can I have mysql or PHP only look at the first character of the string?

Search
I have an ASP page that searches a database. Although the entire database is searched, I use a filter on certain fields. The below filter (I could only use a little bit for the post to work)is for a field [LEDef_ActiveInd] that has either the number 1 or 2 or the text of true or false and it works and returns all "LEDef_ActiveInd" that carries the value of true.
..strQS = ..strL & "...LEDef_ActiveInd+true nd all+" & Trim(strR)

What I would like to do is also filter a field [LEDef_Cd] which has text data such as "10-Name" or "40-Name", etc. I need a filter/code that would return the results of all the "10-Names".

My SQL Search
I'm currently using the following mysql statement to perform a search. Right now it is only returning the results which are found in the description field. How do I modify this statement so that it is searching

the following fields: company_name, product_name, description.

SELECT product_id, company_name, product_name, url, description
FROM product, company
WHERE product.product_id = company.company_id AND description regexp
'#FORM.query#

Search
i have several tables. they have some fields in common, but some different ones.

table 1

id author title journal year
1 brown java computing 2005
2 black sql database 2000

table 2

id author title publisher year
1 brown c++ pen 2006

how do i search both tables for i.e. author ? the author can exist in only the one table or both. so if i search brown i get:

id author title journal year
1 brown java computing 2005

id author title publisher year
1 brown c++ pen 2006

if i search black i just get :

id author title publisher year
2 black sql database 2000

Search
I have a search box on my site which works fine but I would like to improve. at the moment it will not return a result for a two word search unless those words are adjacent ie red box but not for return for red big box I would like to make it give results for multiple word searches when the:

words are found
In this order

all words in search adjacent
all words in search (not neccesarily adjacent)
any of the words

I assume the following tells it what to look for and how to display results (I hope so any way) Code:

Search Box
I am trying to have a search box on every web page in the site - like everybody seems to have these days, anyway, according to DW help the search page results holds the key to how it searches the database, but the trouble I am having is what to put in the sql box for the query?

I know SELECT * FROM table will select everything and I know SELECT * FROM table WHERE location = 'town' ORDER BY price etc,etc. But what I need to know is how to have the results page display one set of results if a user enters one topic in the search box and another set of results if something else is entered. And if there is nothing to display, how do you configure that?

SQL To Use For Search
I am creating a script that searches the table "sites" in the database "actionhostsearch".

I have a column in my table called "keywords" and i want to check that against my entered text, I am currently using LIKE but don't get a good result, is there a way I can refine it?

The keywords are space separated, but i want it to search like multiple keywords. Is there anyway of doing this or would i have to separate the keywords out using php?

Search
I've heard that a friend searches by using a "like" querywith a select... But i searched in the mysql manual, and gcoudlnt' find anything on it... What are some other methods of searching in mysql databases?

SQL Search
I am migrating my site over to mysql and was wondering how I can implement my same type of search into the territory of mysql. Basically this is how the search worked for the db files (terribly inefficient but it worked well). Spliting each line at the deliminator, it would match the query against 10 different values and depending on the value being matched if a match was found the item would get a score. Each column had a higher score depending on which was most important. I basically used a regex expression to count the matches and multiply it by the matchscore. Is there anyway to use LIKE and find out how many times it matched? Can I use REGEXP and get a match count with the global switch?

Using LIKE In A Search
First off, I apologize if I am in the wrong forum.

My question is kind of simple, if I use "SELECT from INVENTORY WHERE item_desc like '%blue%' OR item item_desc like '%stand%';

can i assume that LIKE is "not" case sensative?????
so far that is the case, however, I can't find where in the documention to make sure of it. doing a search for LIKE in the documention is just ever so special.
so i want to ask people who know. is LIKE case sensitive, or not?

Search EVERYTHING For ANYTHING
I got a database here with multiple rows/columns inside.
Example:

I have columns date, text1, text2, text3, and so on...

Select * from db where "%" = "1969"

So I wanna search EVERYTHING in the db for ANYTHING that says 1969. Whats the query to do this?

Search?
I builded a little propert database. I added a search function to the site
but have the following problem:
I used "SELECT *
FROM table1 WHERE area ='province' and type like'%type_1%' and town
like'%town_1%' and descr like '%descr_1%' and suburb like '%sub_1%'"
But it do not do the like thing. If I search for 3 bed and the actual data
is 3 bedroom it gets the data, but if I search for 3 bedrooms (extra s) it
do not get it or if I make spelling mistake I do not get a result either. So
it seems that it rather search for the exact term instead of the like term.
How can I get it to really search for only a phrase, part of a word or a
spelling error?

Using LIKE To Search
I have been using the following to search:

WHERE FirstName LIKE '%$firstName%'
AND LastName LIKE '%$lastName%'
AND Email LIKE '%$email%'
AND EmployerLocationName LIKE '%$venue%'
AND State LIKE '%$state%'
AND MemberApproved LIKE '%$status%'
AND tblmanager.mngNBR LIKE '%$mng%'

It has been working well but the only thing I realised with the mngNBR, if the user selects say 'Joe Bloggs' and in the database he is number '2', it also outputs all the results of other managers who have number '2'... so Matt Smith '22', John Smith '24'... I'm assuming this is because I have used like but is there any other way I can do it?

Search Using LIKE
I need to pull all records where the First letter of each word in the record begins with the entered variable. Here's my query:
CODE"SELECT * FROM hgs_contacts WHERE name LIKE '".$_GET['l']."%'";

Search Between A - J
I been looking a ways to do this but I can't seem to do it or get my head around it.
I want to basically search my table for all "titles" begining from A - J.
I want the SQL to check the first letter of the "title" and if the first letter starts with any letter between A - J I want it to be displayed.
How would I do this? So far I have:

Code:

SELECT title
FROM pmc_keyclients
WHERE LEFT(key_title, 1) = 'A'

But this let me search BETWEEN latters

Search By Number
I have a question regarding a mySQl search

Basically I have a list of numbers stored in a field in my database representing different features of a venue

For example 1,16,17,10,8,13,

Now I want to give my client the ability to look for specific venues with a specific feature by typing in the number for the feature they are looking for, ie if they want to find all venues with a bar and 1 = bar then they enter 1 into the search and are presented with all the venues with a bar

Now my problem is if I do a search for Ƈ' using LIKE then and a wild card (ie %1%) I will get all the features that have a 1 (ike 16, 17, 10, 13). Is there anyway I can write a search that will only return venues that just have the number 1?

Also the numbers can appear in ANY order so it might be 10,8,26,1,32 ....

Search Index
I have text stored in a database that I'd like to be searchable. However, searching through long strings and blobs seem like a very inefficient way of doing things.

In MS Server there's an index server that will take all strings and create indexes out of words. This can significantly speed up text searches.

Is there such a feature or tool for MySQL?

Fulltext Search
Below code is from a CMS's search script. When you search for plain words like Britney Spears it displays the results correctly. But when you search for a URL like http://www.sitepoint.com then it does not return any results.
Is there anything wrong with the following SQL code?
...
$this->sql = "SELECT story_id, story_date WHERE $where $sterms ";
...
$where = " MATCH (story_title, story_url, story_tags, story_content) AGAINST ('$words') ";

Note: story_url stores URLs exactly as http://www.sitepoint.com not encoding etc.
story_url field is FULLTEXT same as others.

Search On Dates?
I have 2,000 stories saved in a database (Not my DB) and I want to perform a search on the section of story and the date it appeared on the website.

The trouble is the DB is dodgy and dates are saved in a format like:
2007-1-8 //Today
Ie
YYYY-M-D

Now what I want to do is perform a MySQL statement whereby I perform a search on the date in this format:

2007-1-8 //Today
Ie
YYYY-M-D

IE: "Y-n-j"

And then my SQL finds the date I asked for, but then

- Checks itâ€™s not greater than todayâ€™s date (NOW) by using unix_timestamp on both the appreared field and published field.

And then output all the results to the screen.

Now I thought this may work:

PHP

$sql = "select id,headline,appeared from news_stories
where section = '$searchstring' AND
date_format('$appeared','%Y-%m-%d') =
date_format(appeared,'%Y-%m-%d') AND unix_timestamp(published) <=
unix_timestamp(now())";

Search Functionality
When a website adds search functionality, more so than bulletin boards, but take the Facebook for example (not as complex as what they do but a good example, and the only i can think of at the moment). You can do a basic search for 'John' and the results returned will contain people named John, it will contain people who date a John, anyone who remotely has 'John' show up in any field in their profile.

Is the query for this simply a matter of a large amount of OR statements is there something else to it?

How To Search '+','-' Or '(' In Database
How to search " +, - , ( " signs in mysql database....

Search & Replace?
Newbie here.

I have a DB with two tables.

Table 1 has three fields - a unique ID#, a first name (i.e - John), and a last name (i.e. - Smith).

Table 2 had many fields, one of them being a first name/last name combination (i.e. John Smith).

I want to replace the first/last combo field in table 2 with the unique ID# from table 1 which matches the concat of the first name field and the last name field from table 1.

I know I can do it by hand, but there are thousands of them in table 2 that need to be replaced and the data in table 2 is in the format that I will be receiving from an outside source, so I will need to search & replace this data on a regular basis.

Help With REGEXP Search
I have a database of English words. This thing is huge. Which means that when I do a search like

SELECT word
FROM wordlist
WHERE word LIKE 'k__p'
I get almost 300 results. What I want to do is limit my results to only those words with a repeated letter taking up that middle spot, e.g. "keep" but not "kelp".

Is there any way to do this in a MySQL query, or am I stuck doing this in my application (which is what I'm currently doing)? I've tried some REGEXP tricks I know, but MySQL REGEXP apparently doesn't have capturing subpatterns or backreferencing.

Search-function
Does anyone happen to know how to select info from the database based on multiple strings like so:

SELECT title FROM food WHERE title LIKE '%New%' AND WHERE title LIKE '%York%' AND WHERE title LIKE '%restaurant%'

Add Search By Tags.
I have this setup where in 1 table i've got the content and name, and I currently have a search for them, but I also have a tags table that i would want to search, then return not the tag, but rather the row from table a associated with it.

HTML
TABLE 1 - content
id name content

TABLE 2 - tags
id content_id tag
I have 2 problems. a) if i do "SELECT content_id FROM tags ..." and i get a list of all the matching id's, how to i write a query to return them all? and isn't there a more efficiant way of doing it? like if the search result returns like 200 id's, isn't calling them one by one too much.

Another problem i'm having is that sometimes one row of content might have more then 1 tag that match the search criteria, so it will end up being called twice. how would i prevent that?