MYSQL :: Searching MySQL

Searching Mysql.com
A comment was made something to the effect
that such a line in /etc/mysql/my.cnf could be
used to restrict network connections to mysql
to a specified IP address. I'm not quoting exactly,
but the idea is that if it is desired to connect to
mysql tables via say an ODBC connection, a line
like bind-address = 192.168.1.175 would allow
connections from a client pc with that ip addr.

What if I wanted to allow 2 PC's on the LAN to
connect to the mysql server on the linux box?
I couldn't find help on mysql.com for this objective.

Searching With Mysql
My table in MySql looks like this
(id:int, usrid:int, title:char(30), descr:text, imgname:char(30), imgpath:char(30), links:char(20) );

I would like to enable users online search this tables "title" and "desc". What is the best way to enable users to search for a srting of words and find matches that contains the string or part of the string.

TEXT Searching In MySQL
I need to search in a 'text' field (the field is a product description, it has around 4000 characters).

1. What is the best way to do this without using Full Text Search features? I have one or two keywords to search for in the 'description' field only. I have around 2000 records in the table, each record has around 4000 characters in the 'description' field. I simply need to find all the records with a match.

2. If I am going to use FUll Text Search features, what is the syntax for creating the full text search index on the existing database so that the full text search commands will work?

Searching A Mysql Table With Php
whats eneterd in the form lets call it "name" is included in a table feild record it returns the info for that record.

now I could do this with a SELECT * FROM table WHERE fieldname='$name' but then it will only return exact results, i wana try and work out a broader search.

MySQL Query Searching
I have a database that keeps track of a persons name using one varchar field called name... name usually has a persons full name... i.e. James Lee King. i am trying to search the name field for either the full name or a partial part of the name. i.e. james and return every row that contains that name.

When i wrote the php script. It only returns a query when the search equals the full name or if i use the first name of the person. Is there any way to get a partial match using 1 field?

MySQL And Searching TEXT Fields
what the best solution is for making large numbers of
TEXT (or BLOB?) fields searchable. For example, if I have a forum,
what is the best way to be able to search for specific words? How
about exact phrases?

I saw a solution where all words are preindexed in a "dictionary" like
table and then another table stores the word matches. That seems
really fast, but it has two major problems: 1) it can't do exact
matches, and 2) the match table would get insanely big really fast.

MySQL: Fulltext Mode And Searching More Than One Field
some quick background: I'm using MySQL 4.1.18. This is my first experience with MySQL (or any database system for that matter). I do have programming experience.

Ok, now my table:

restaurant_db
|-------------------------------------------|
| Name[varchar(200)] | City[varchar(200)] |
|-------------------------------------------|
| Jade Palace | Laguna Niguel |
| Jade Palace | Aliso Viejo |
|-------------------------------------------|
Basically I'm storing restaurants inside my database. Each restaurant has two fields: name and city. In the example above, there are two jade palaces (restaurants): one in Laguna Niguel and one in Aliso Viejo (cities).

Now I have fulltextmode working perfectly. Both 'Name' & 'City' are fulltext indexed. But here's my problem. Consider the following query:

SELECT name, city, MATCH (Name,City) AGAINST
('jade, laguna') AS score
FROM restaurant_db WHERE MATCH (Name,City) AGAINST
('jade, laguna')
The above code is stripped straight from the MySQL dev docs and modified for my purposes.

Results:

Jade Palace Aliso Viejo 0.3876339495182
Jade Palace Laguna Niguel 0.3876339495182
From the above, one can conclude that it's simply searching one field at a time and once there's a match, it doesn't bother looking for matches in other fields. In the above case, once it found a match for 'jade' (name field), it didn't bother looking into matching 'laguna' (city field).

My desire is to have the above search results reversed so that the Laguna Niguel Jade Palace comes before the Aliso Viejo one by instructing MySQL to search all fields rather than terminate upon the first match. Furthermore (this is probably obvious), I would like MySQL to weigh results with the most matches higher than those with less matches.

MYSQL Fulltext Search - Searching For 3 Character Words
I have fulltext search in my table. The default number of words it can search is 4.
I have requested our hosting support to reduce it to 3. They said it is not possible.

I am looking for ways to search for 3 character words also.

So, I am first checking if the string length of keywords to be searched is greater than 3.
If it is greater than 3, I am searching using mysql fulltext search.

and if it is 3, I am searching like this:
title LIKE '%".$keywords."%' OR descriptions LIKE '%".$keywords."%'

This works well if the keyword is a single word with 3 characters.
If it is a keyphrase, it is not working because string length is greater than 3.
example: 'psp downloads', 'wii downloads' etc..

Is there any better method to search for 3 character words in the search terms?

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";

Keyword Searching
I have a table of events which holds data such as title, description, town etc. When adding an event the user is also asked for a list of keywords which they enter into a textarea and separate with commas. At the moment these keywords are just entered as one field into the database which has a datatype 'TEXT'.

So what is the best way to query the database when a seach is performed? For example, if a user searches for 'music festival' then I'd need to search the events title and keywords for the given search term. So is it best to perform a FULLTEXT search on the keywords or store the keywords differently?

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?

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.

Searching Database
I am helping someone with a website that was setup, they have a problem searching the database, if you don't put the phrase in exactly as it is in the database, it won't return anything. They would like to search for xxx, and have it return anything that has xxx in the title of the product.

Searching On Two Keys With OR?
Which version is targetted for optimization of OR searching on two keys,
that is, "select * from sometable where f1 = 123 or f2 = 123",
as described in http://www.mysql.com/doc/en/Searching_on_two_keys.html
?

Three And Less Characters Searching
I've got a database that has boolean search on it, but from what I read and see from my results, MySQL ignores words with three or less characters, is there a way to force this? or how I can deal with having two and three character words searched?

FULLTEXT Searching For *?
I have just discovered the power of FULLTEXT. But i would really like to be able to search for words like *smile*. That is, smile with astersisks around it. Is there an escape character for * that lets me do this?

Searching Archives
I just joined this list and would like to search the archives before I ask a =
question. I there a way to search.

Searching By Tags
I have three tables: `photos`, `tags` and `tags_photos`.

The `photos` table contains a unique ID and a bunch of other stuff, the
`tags` table contains a unique ID and a tag name, and the `tags_photos`
contains the photo ID and the tag ID for an association between a tag
and a photo.

In other words, any photo may have many tags and any tag may have many
photos.

This is great for filtering photos based on tag, but I'm now trying to
allow users to search photos on multiple tags. I want users to be able
to enter a list of tags and either search for photos with "all these
tags" or "any of these tags".

I tried the query "SELECT a.* FROM photos AS a, tags_photos AS b WHERE
b.photo=a.id AND b.tag IN ($tags)" where $tags is a comma-separated
list of the tag IDs from the tag names the user entered, but of course
it returns photos multiple times if they have more than one of the
entered tags.

Any ideas?

Searching A Range
I have a database where the customer wants to be able to find a price range (she has entered a definite price for each item)... how do I set it up for that sort of search?

Searching And Indexes
I have a fair sized table now (1,955,041 rows) and it currently has two indexes:
PRIMARY is the ID number
and
Keywords is a FULLTEXT index of the Keywords column (Text).

In my first query type I also check two other fields: Disabled and Toplevel

SELECT * FROM files WHERE Disabled='0' AND TopLevel='clipart' AND MATCH (Keywords) AGAINST ('+tree' IN BOOLEAN MODE) ORDER by ID;

In my first query type I do not have keywords but am rather checking to see if an image belongs to a particular category like so:

SELECT * FROM files WHERE Disabled='0' AND TopLevel='clipart' AND (Category='Food' OR Category2='Food' OR Category3='Food' OR Category4='Food') ORDER by ID

My question is, are there any other indexes (or anything else) that I can do that will speed up the results?

+----------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------------+------+-----+---------+----------------+
| ID | int(20) unsigned | | PRI | NULL | auto_increment |
| Disabled | char(2) | | | 0 | |
| TopLevel | varchar(50) | | | | |
| Keywords | text | | | | |
| Category | varchar(100) | | | | |
| Category2 | varchar(20) | | | | |
| Category3 | varchar(20) | | | | |
| Category4 | varchar(20) | | | | |

Searching Through Different Fields
I'm trying to create a search bar on my webpage. My web page is a clothes shop and I want to be able to search by brand, shop name, dept etc... Each of these is a field in the table product. Code:

Optimized Searching
I would like to make a search engine that is general and looks multiple tables with different categories...

What is the strategy to achieve that? I did a join and made what seemed like a good search engine, but it consumes too much mySQL power :-( It searches through all of the records (27.000)... But I don't know how to made any other way...

Searching Algorithm
I'm developing a database for a research firm which has data they want
to search for specific criteria against varying base line data - some
of the questions that have been asked to get the raw data (which I
will import into MySQL) are like this:

how often do you use mouthwash?
etc.
etc.

The responses for each question was structured with only 4 answers
from a large group of people:
no answer
low
medium
high

Using various baselines of people, I want to build a query that will
accurately find matches and to also allow me to find 'close' matches
- for example:

if base line #1 is for a consumer who uses mouthwash a 'medium' amount
of times per week, I want to find in the database how many 'hits'
there are for that question - but I also want to capture 'close' hits
like in this example, for this specific question, if the search finds
'low' instead of 'medium' I'd like to count that as a 'close' hit vs a
direct hit (match) - then when the query is over, I'd have say, 80%
matches with (for example) 60% of those direct hits and 20% close
hits - I'm using PHP so I can use that to keep track of direct hits
and 'close' hits.

Searching In Innodb
Does innodb support fulltext indexes and searching on those indexes in MySQL 4.0?

Searching Columns?
i have a user management system in the making that stores info in a mysql DB.
For various admin functions i want to be able to search for users. I want to be able to search by fields: 'username' 'fname' 'lname' and 'id'

can i do something like:
"SELECT * FROM UMS_Users WHERE $field CONTAINS '$search'"

where $field is the field to search by and '$search' is the search term.

Searching On Two Keys With OR
Which version is targetted for optimization of OR searching on two keys,
that is, "select * from sometable where f1 = 123 or f2 = 123",

Searching Two Databases
I was asked to create a second knowledge base which pretty much consists of articles (id title description and the post). I am now have to make another one for restricted users. How would I change my sql to search both of these databases?

The first database is called 'article'. I have a second database called 'informationtech'.

Would I add 'AS score FROM article AND informationtech'?

"SELECT id,title,description,DATE_FORMAT(date,'%M %D, %Y') AS date,post, match(title, description) AGAINST ('$search_value' IN BOOLEAN MODE) AS score FROM article where match(title, description) against('$search_value' IN BOOLEAN MODE) ORDER BY score DESC" .

Fultext Searching
I would like to set up a search for some sections of my site. When I try to add fulltext, I get the error that the table type doesn't support fulltext. This may be because it is not MyASI...or whatever that is. Anyway, what are my options? Do I need an index? Would searching without one be painfully slow?

Can I just conver the table type or will this cause unexpexted problems? The average row has about 1-8-25 words and I shouldn't exceed a few hundred for a while. This is a brand new site.

Searching A Table
I have a book at home that can probably help me figure it out. But until I return there this weekend, can someone give me a quick example of how to search a table and then out put a specific row. Perhaps a simple table such as

Name Scores
Paul 30
Tom 50
John 40

I'm just looking to search the table, find the match, and the output the correct scores.

Advanced Searching
I want to search the complete tables i am having. The searching should be like the following....Say if i give the word 'caring' as the keyword for search the i should get the result from the fields which is similar to caring..

ie it should display all the fields having data ..care,caring,very caring..etc

hope u all got it..I used the fulltext function and also match() But i am not getting the desired result.

Searching A Column
I have a database of filenames that when someone searches for a filename it uses LIKE against the column for wildcard matching.Some of the files I have entered recently have a $ (dollar sign) as part of the filename, and they are not showing up in the results. Do I need to slash them in order to work?

like '%test$test%'

Searching Across Table
Suppose I have 5 fields in my database (A, B, C, D, E). Is it possible to write a query that searches all of them?
I know that I can do
Code:

select A from my_table where a like '%ponies%' or b like '%ponies%' or c like '%ponies%' or d like '%ponies%' or e like '%ponies%'

But if I have 100 fields, that would be unwieldy. I could do this very easily with PHP but would prefer to optimize the DB query.

Searching MySQL

Im trying to search a MySQL DB but i get the following error instead: Error SQL-query : SELECT * , MATCH ( introduction ) AGAINST ( 'NASA' ) AS SCORE FROM project_stories LIMIT 0 , 30 MySQL said: #1191 - Can't find FULLTEXT index matching the column list

Im trying to search a MySQL DB but i get the following error instead:

Error

SQL-query :

SELECT * ,
MATCH (

introduction
)
AGAINST (
'NASA'
) AS SCORE
FROM project_stories
LIMIT 0 , 30

MySQL said:

#1191 - Can't find FULLTEXT index matching the column list