MYSQL :: Keyword Searching

Keyword Searching
I've created a web site using MySQL and PHP to interact with the database and with several days of blood, sweat, and many tears I have been successful in being able to set up the database and tables with several search options.

However, I can't seem to get a "keyword" search to pull up the results based on entering a keyword.

I thought this would work but it doesn't:

$get_prods = "select id, prod_name, city, prod_brief, prod_desc from products where prod_keywords and prod_desc like 'prod_keywords%' order by prod_name, city";

Can anyone tell me if I'm close or far away from success?

Searching Keyword
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?

Keyword Searching Accross Multiple Tables
been trying to get this to work correctly for some time now - basically i want to search for multiple keywords accross two tables. The query i have already works to a certain extent but doesnt return the exact results i'd like.

I got two tables, both are for a bulletinboard, one contains the threads (bulletinboard) and the other the replies for each thread (bulletinreplys). They're linked via a column called 'threadid' in the reply table. Code:

THE Keyword
If, for example, you have a database full of movie names, a lot of them will begin with "THE", and you'll usually find such names alphabetically sorted with the 'the' at the end (e.g. "SHAWSHANK REDEMPTION, THE")

other than entering data into the database in this way, does mysql offer any "order by" parameters or other options to enable this type of alphabetical ordering?

IN Keyword
Sorry if this has been answered before, but searching for "IN" returns more results than I have time to look through.Does MySQL allow the IN keyword in queries?
I'm using it, but not getting any results returned, so I'm thinking MySQL doesn't use IN, or i'm doing it wrong

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......

Random Keyword
I have a question about what query I should use to achieve the following...

I have a table called 'keywords' that has the following fields

id (auto_increment)
pagename
keyword

There can be multiple keywords for one pagename, but each keyword has it's own row. For example, this could be in the table:

id pagename keyword
11 test-news test
10 test-news news
9 test-news article
What I want to achieve is to select 1 random keyword for each unique pagename. So there can only be 1 keyword selected for a pagename, but it should happen for all unique pagename entries. What query would I use to achieve this?

How Exactly The LIKE Keyword Works?
I have a table named Inventory and have 3 items in it containing the work w069.

I want to run a query to return the three items.

My query: Select * from Inventory where Description Like 'w069'

It doesn't seem to work, returning zero row.

GO Keyword From SQL Server
In SQL Server I can use GO between statements to execute multiple statements in the query window.

What is the mysql equivalent?

Is 'leave' A Keyword?
I have a column named 'leave'. It works fine under debian and mysql 4.x. But each time under windows XP and mysql 5.X I got a sql syntax error. So is 'leave' a key word in mysql5.x?

Actually I dont want to change the column name now. Is there any other way to solve it?

Search Keyword In Db
Im trying to implement a search function in my database but not sure what would be a smart way to do it.
Suppose I have a blog column and want to search if "middle school" is inside, I would do: columnName like '%middle school%' but if the search key words are "middle school soccer", I don't know how to recognize and break this into two logic parts as "middle school" and "soccer". How would this be implemented or is it has to deal with some complext AI concept?

Right now I only know to use columnName like '%middle%' or columnName like '%school%' or columnName like '%soccer%'.

DISTINCT Keyword
i was wondering if there is a way to select more then on field using the DISTINCT keyword

i have a query like this:
$str_sql = "SELECT id, product_id, name, description, price FROM products ORDER BY name ASC";

However i know adding the Distinct keyword like this won't work

$str_sql = "SELECT id, product_id, DISTINCT(name), description, price FROM products ORDER BY name ASC";

Distinct Keyword
i have the following query.Code:

SELECT DISTINCT(card_no), cardtype FROM orders WHERE acc_id = 5

but it is not doing what i want or expect
what i need is for it to select the distinct records for card_no and just grab the card type but don't add that to the distinct bit...does that make sense?
If anyone can help that would be appreciated. Oh and these are not Credit cards i am dealing with before i get told off

Multiple Keyword Query
Ive currently got a table with (shopId, Keyword) used for searching on a product. So a shop could have multiple keywords and a keyword could be associated with multiple products

so if a user wants to do a such as "stationary AND office NOT warehouse" how would i get from the database all the shopIds that have the keywords stationary and office, but do not contain the keyword warehouse

This would seem like a common problem, what is the best way to perform this type of query?

Keyword SELECT In MySQL
I have an array with a number of keywords (php). I want to SELECT articles in my table that have headlines which contain the keywords in my array.

how do I do this using SELECT?

obviously "SELECT id, headline, permalink, body FROM anews2 WHERE headline LIKE $headlineKeywords" doesn't work. Short of creating a loop in PHP to search for each keyword in my array independently, how can I do this in mySQL?

FYI: I am trying to created a "Related Articles" feature. If a news item is posted then below the news item I want to list all of the related news articles based on keywords in the headline.

Checking If A Keyword Is In The Top 1000
How can I structure a query so that I only get back a result if the keyword I am searching for is in the top 1,000 of the results?

I am currently using the following query and then running through the array using PHP to check if the keyword is in the top 1,000. However, I would like to eliminate the PHP step to save processing time and CPU usage.

SELECT keyword FROM lookup_table ORDER BY lookup_count DESC LIMIT 0, 1000

After getting back the result above I would use a loop to get all 1,000 keywords. If I could just check whether the query succeeded or failed based on the keyword that would be much better.

Is 'finding' A MySQL Keyword Or Such
I'm using PHP to create a simple, dynamic MySQL SELECT query.

The user chooses a selection from a HTML Form SELECT element's many options
and submits the form via a POST action.

The SELECT query is built as follows:

$itemtype = stripslashes(trim($_POST['selType']));
$query = "select * from item where item_subtype like '%$itemtype%'";

I've used:
echo "<center><b>".$query."</b></center><br />";
to see that the queries are being properly constructed.

When the option with the value of 'finding' is chosen, no records are
displayed, although there are many records in the item_subtype field of the
item table that contain the string 'finding', such as 'Earring finding' and
'chandelier finding'.

The display of records works fine when other values are chosen (other than
the value of 'finding')

Help!

Can anybody help me figure out what might be preventing records with
'finding' in the item_subtype field from showing.

I have no problem with any other of the SELECT element's values being passed
to the query.

Here is the 'offending' query: select * from item where item_subtype like
'%finding%'.

Fast Keyword Search
I´m asked to "speed up" a keyword search based on MySQL.
The material i´m given to work with is a quite large MySQL table with
1.5 mio rows, defined something like:

CREATE TABLE datarecords (
id BIGINT(20) NOT NULL auto_increment,
[3-4 other INT type columns],
keywords TEXT NOT NULL,
updated TIMESTAMP(14) NOT NULL,
PRIMARY KEY (id)) TYPE=MyISAM;

The search requires the id-fields of the datarecords table to be
returned, based on keywords within the keywords-field, like so:

SELECT id FROM datarecords WHERE (
(keywords LIKE '%someword%' AND keywords LIKE '%otherword%')
AND
(keywords NOT LIKE '%notthisword%' AND keywords NOT LIKE
'%thisneither%')
)

The table has grown unexpectedly large and will keep growing, so a
different method for keyword searching appears to be required, since
search-speed has decreased significantly (now at about 12 secs,
depending an the number of searched keywords)

Using FULLTEXT indexing has brought no performance increase...

Question:
Does anybody have any suggestion on how to tackle this type of
problem, to get search-speed down to about 2-3 secs?

One way i´m looking at this is to create a seperate keyword-table
something like this:

CREATE TABLE testkwdlist (
keyword varchar(32) NOT NULL default '',
ids text NOT NULL,
PRIMARY KEY (keyword)
) TYPE=MyISAM;

and filling this table up with the keywords and listing the IDs in the
ids-field, doing seperate queries for the positive and negative
searches and compiling the actual resultset in code...

Does this sound it has potential for a performance boost or is it
competely insane?

About Using DELAYED Keyword In The INSERT Query
I have a table in my database that needs very fast inserts as hundreds of concurrent users might be inserting rows at a given time. Basically, every time a visitor accesses a web page the time needs to be logged in the table. Since there could be hundreds of insert operation in the queue waiting for their turn, a visitor needs to wait before his entry is inserted. So to speed up things a bit I was thinking of using DELAYED keywords in the INSERT statement. The visitor will issue an INSERT query and then proceed. The entry would be inserted when the table is free to accept new insert query.

Keyword Search - Comma Seperated Values?
1) I have a full text column that contains several paragraphs of content that I want searchable- is it better to have comma separated keywords or is it better to throw all of the text in to a full text TEXT column so it is searchable? Which is better?

2) Currently my SQL query is not giving me the plural of keywords such as "green" as a keyword but the search for "greens" will not produce any result.

3) Also words that appear in the database as "green dog" will not show a result if someone searched for "dog green"

Here is my SQL query

PHP

SELECT *FROM `report`WHERE `company` LIKE '%$P_search%' OR `description` LIKE '%$P_search%'OR `market1` LIKE '%$P_search%'OR `market2` LIKE '%$P_search%'OR `market3` LIKE '%$P_search%'OR `market4` LIKE '%$P_search%'OR `market5` LIKE '%$P_search%'OR `market6` LIKE '%$P_search%'OR `location` LIKE '%$P_search%'OR `date_year` LIKE '%$P_search%'OR `date_month` LIKE '%$P_search%'OR `source` LIKE '%$P_search%'OR `keywords` LIKE '%$P_search%'

GO Keyword Like SQL Server Query Editor--for MYCC?
Is there an equivalent for MyCC, of the go keyword used in SQL servers Query Analyzer to execute multiple statements.

LIke I want to

Use DB1
go

SELECT * from WHATEVER
go

MySql + VB6: Pass Keyword As Fieldname Within Insert Statement
I successfully migrated from access to mysql. The original database had a column name "group"..When i try to pass/insert the value from the form into this field, in the table, i get an error.

I believe group is also a keyword/part of mysql and its causing the error. Is there a way to pass this keyword as the field? Such as wrap it in something (like brackets) to pass the info as a field without changing the column name?

for example the correct form (if it wasn't a field name) would be:
"insert into tbl (group) value('" & groupnum.text & "')"

so how can i pass this "keyword" into the mysql database as a field name?

Return Context From Large Column In Keyword Search
Consider this query:
Code:

SELECT Ticket,Content FROM Attachments,Transactions
WHERE Transactions.id=Attachments.TransactionId
AND Content LIKE "%Keyword%";

The 'Attachments' column is quite large. How could I limit the returned text to say 25 characters before and after the keyword?

Automatically Generate Text Excerpt For Text Near Keyword Hits?
I would like to generate an automatic text excerpt showing the text close to the first occurance of a keyword needle in a text haystack.

For example: searching for

keyword = "foo"

in a table called Webpages in the column Content = "Yadda, yadda, yadda. Bla, bla, bla foo yadda yadda yaddda bla bla bla"

would return a column called Excerpt = "...a, bla foo yadda yadd..."

Is this best done with PHP after fetching the entire column Content, or will it be more efficient to do directly with MySQL?

What would that SQL query look like?

The MYISAM Storage Engine And The "KEY" Keyword
1. Does MyISAM support FOREIGN KEY constraints? When I read the online
MySQL Reference Manual, it said "InnoDB also supports FOREIGN KEY
constraints". Does it mean MyISAM does not support FOREIGN KEY constraints?
It this is the case, what should I keep in mind when I design MyISAM tables?

2. I have seen the following CREATE statement somewhere, but I have never
known that the "KEY" keyword can be used that way(the last 4 "KEY"). I
guess the "KEY" is equivalent to "CREATE INDEX". Is it the syntax for older
versions of MySQL? Code:

What's The Advantage Of Using The "JOIN" Keyword?
I have a table L like L(from, to, label)

and a sql query as follows:

SELECT distinct(L.to) FROM l
WHERE l.label = 'label1'
and l.from in
(
SELECT distinct(L.to) FROM Links l
WHERE l.label = 'label2'
and l.from in(
SELECT distinct(to )
FROM Links
WHERE from = 1
and label = 'label3')
)

Is there a way to optimize it? Would it make a difference to use "JOIN"?

Help With Searching WHERE/LIKE/OR
I tried to search this on the forum, but 'where', 'like', and 'or' aren't the greatest of search terms...

Given a keyword, I need to search several fields for matches. I can use %$keyword% format.

This is what I have been trial and erroring:

$query = "SELECT * FROM tablename
WHERE field1 LIKE '%$keyword%'
OR field2 LIKE '%$keyword%'
OR field3 LIKE '%$keyword%'";

Can you see the error here?

Searching
I have an interface for this table setup to allow people to search the table based on several fields.

My question is this, I can find exact matches of, for example, last name. But how can I return a partial match? If I search for "smit" I would like it to return "smith" as a match. Also, if it's possible, "smtih" would match "smith."

Is this possible? I've tried googling and searching here for something like this, but feel that I'm not entering the right words to pull up what I want.

Searching On Ã½
I need to find all the instances of Ã½ in my database, but not y. So, for example:

SELECT * FROM listings WHERE descritpion LIKE '%Ã½%'

produces anything with a y in it. How can I get only those records with a Ã½?

Searching A Row
i need to be able to do a search using the "LIKE" instance through all of them, but id was hoping to avoid having to do something like this:

first_name LIKE "%$form{'FirstName'}%",
last_name LIKE "%$form{'LastName'}%",
company LIKE "%$form{'CompanyName'}%",
street1 LIKE "%$form{'Street1'}%",
street2 LIKE "%$form{'Street2'}%",
etc....
i would have to do this like 40 times to have it search everything.

Searching
I have a table that includes a US zip code column. It stores a 9 digit zip in the form of 12345-6789. I would like to be able to search it with accuracy to only the first 5 digit part, but I'm not getting far with my own ideas. Can this be done?

Searching
i have created a query that is to search text in my database and it goes well. but my friend the other day is complaining that he did not find what he search in the database but actually the data is really in there.. the data contains ampersand(&) like 'tom & jerry' and also a text 'mcs of tampa inc'.
my query is this:

select * from mytable where mytext like '%mcs of tampa inc%'
select * from mytable where mytext like '%tom & jerry%'

in the first query, it runs ok when the text is 'mcs of tampa' but if i complete it to 'mcs of tampa inc' no results has been return.
is theres a special characters their?

Searching A Database?
Does anyone know of any tutorials or documents which would help someone like myself work out how to search a database and calculate relevancy.etc. I'm creating an auction site and so I want people to be able to search both the title and description of what is being sold. I want the results ordered by relevancy. But I have no idea how I'd work out what's more relevant?

Searching Across Two Columns
I'm trying to search two columns - phone_brand and phone_model.

I'm using the following query of which will successfully match something like Nokia or 6133, but not Nokia 6133. I realise this is because the wildcards will only match either side (or one side in the case of the brand column), so I was wondering, what would be the best way to match the whole string?

$sql = "SELECT col1,col2 FROM mytable WHERE (phone_brand LIKE '".$var_keywords."%') OR (phone_model LIKE '%".$var_keywords."%') AND status=1";

Searching Database

i have the following situation: table with about 20 columns and two of them are tolanguages and fromlanguages. User uses search form which is basically two fields(to from) and a button.

What i am trying to do is to make a search by this two columns. I need that keywords from to field be searched in column tolanguages and from field in fromlanguages.

I tried full text search but i don't think it should be used in this situation because it doesn't search specific keywords in specific column. I also have an idea of using regexp but don't know yet how;

Example searches would be:
1. To: English; From: Chinese
2. To: Chinese, English; From: Russian

PS my problem lies in multiple keywords like in ex. 2

Searching Database
I have a MySQL database (InnoDB) that I need to search. I have been trying to output all of the data, they are short news briefs, to a single page and then having Sphider search that page looking for relative links. I am not quite getting the results I need, plus I am worried about scaling problems with this approach.

I have looked at Sphinx but this application runs on IIS6 and it seems that Sphinx will crash when multiple searches are being run on IIS6.

What do you do when faced with doing full text searches on MySQL databases? Is it a problem to convert to MyISAM?

Searching Query Help?
I need to search from a form in my website which has one textbox and i need to search from the text box from all the fields in 2 different tables which have similar data on...

This is what i came up with so far...

SELECT *
FROM female_models
WHERE concat( name, height, waist, hair, eyes, hips, shoe, bust, race ) LIKE '%anna%'

SELECT *
FROM male_models
WHERE concat(name, height, waist, hair, eyes, collar, shoe, chest, race,) LIKE '%anna%'

And these work both on their own, but i need to merge them into one because at the moment it just displays lots of things and not seperate rows from individual search results.

Searching From Two Databases
I have two databases, one called codes and one called crs_wk. One contains a table called school_1 and the other one school_2. Both tables have a column called school_name.

I want a query that searches from both databases.

something like this...

SELECT * FROM crs_wk.school_1 union codes.school_2 where school_name like "cypress%"

but it doesn't work. How do i do this?

Searching For Duplicates
I have a table named "article" with a mere 5,000 rows. I would like to count the duplicate titles. The following query just hangs (or at least takes longer than 10 minutes as I killed it at that point):

SELECT count(*) AS article_count
FROM article AS a1
LEFT JOIN article AS a2 ON a1.title=a2.title

Titles could be as long as 150 characters. The column named "title" is full text indexed. What am I doing wrong?

I would also like to check the article bodies for duplicates. The column named "body" could be as long as 10,000 characters and is full text indexed. Whatever solution there is for title duplicates, I would like it to also work for body duplicates. Finally, the article table is going to get a lot bigger. It needs to work with 100,000 rows or more.

Searching A String
1. I have a list of states in a single variable, as such.

AZ CA NY NM OR

2. What is the best method to pull a match on a single state?

WHERE variable is LIKE value?

In JavaScript, I would use the indexOf like so.

if(var.indexOf(value) > -1){
THEN TRUE
}

Is there something that matchs the JavaScript IndexOf ?

TYIA!

Fuzzy Searching
All new to the mysql database and I wonder if someone knows how to do "fuzzy" searches. Is there already an API for this?

Searching Mysql Db
how would you search a table , in my tables i have columns named description and keywords, I would like to be able to search these columns for words like dvd or sweater, I also have multiple tables how would I search them all for the same word without having multiple queries?

Searching MySQL
I would like to search a MySQL database for a search term. I would also like it to be lenient to users. I want it the search to look for patterns, even if there are blank spaces between them.

Fulltext Searching
How fulltext is working!!?

I have the following table with just one record! And when I do a select
statement, mysql returns no hits!!!

Would you explain to me what is wrong?? Code:

Searching Records
say i have two tables! one tables with car news and the other table with pictures of these cars.

the tables:

cars:
name
model
engine

pictures:
small_pic
large_pic

waht i want to do is get the name and then search the small_pic column in my pictures table and then output this along with my article.

e.g car name: ford i have 3 pictures in my pictures table for this car

ford1.jpeg
ford2.jpeg
ford3.jpeg

i want to output ford1.jpeg!! do i have to use a LIKE query? basically i want to show the article and the picture on the side of it.

Searching Database
I am using php and MySql to code a website. Here is a brief description of the website.

Subscribers can sign up to a search database for people to search for them.

The subscribers are able to check off certain "attributes". And i want people to be able to search for the subscribers by these "attributes".

What would be the best way to build this database and search it.

I was thinking of having each "attribute" its own field. But i dont know how to search them all.