MYSQL :: Searching Across Two Columns

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 2 Columns Of The Same Table
I have created one db with one table, several columns. I would like to search 2 columns of that one table. the search is provided through drop-down menu in HTML. This is that part of a code:

$result = mysql_query("SELECT * FROM table WHERE column1 = '%$search1%' and column2 = '%$search2%'") or die ("unsuccesful");
while($r=mysql_fetch_array($result) or die ("unsuccesful"))

Searching In Unicode Columns
I'm trying to do a search in a column which contains unicode strings.
It works allright for english keywords but it doesn't work for unicode characters.

I'm using:
SELECT * FROM `Customers`
WHERE city = 'تهران';

I would use N function if I was using MS SQL SERVER,

It would be like this in MS SQL SERVER:

SELECT * FROM `Customers`
WHERE city = N'تهران';

How can I do that in MYSQL?

Searching Across Multiple Columns
Is it possible to search for a word across multiple columns in a table?

For example:

Table contains columns "Year" "Make" "Model" "Color" (etc etc.....)

Then, using a user entered keyword, generate a query that checks the word against all ( or a list ) of columns in that table?

Something like....

SELECT * from Table where * LIKE '%keyword%'

Logical OR On Columns? (Merging Two Or More Columns Into One Column)
Considering the following example table:

Column1 | Column2 | Column3
===========================
NULL |"Value1" | NULL
---------------------------
"Value2"| NULL | NULL
---------------------------
NULL | NULL |"Value3"

In each row only one column contains an actual value (a string in our
example), the other columns are set to NULL.

I would like to merge those columns into one single column, discarding
the NULL entries.

The desired result would look like this:

Merged_Column
=============
"Value1"
-------------
"Value2"
-------------
"Value3"

A '"SELECT column1 OR column2 OR column3 as "Merged_Column"' doesn't
work unfortunately. Is there an easy way to accomplish this on the SQL
Server?

The next problem I am going to face is that every column is in fact
made of a different data type (ie. Date, Int). Is there a way to
modify the merge select (if that's possible) to have it cast
everything into a single specific type? Ie. make everything in the
merged column a string.

How Do I SELECT FROM Columns Where The COLUMNS Has A PATTERN?
I've been looking far and wide on this one. I'm looking for a way to select from columns where the columns have a certain pattern!

Example:

Instead of doing:

SELECT q1, q2, q3, q4, q5, q6, q7, q8, q9 FROM exam

I would like to do:

SELECT q* FROM exam

or

SELECT q% FROM exam

Whatever will match everything that has the letter q in it. Anyone have an idea? I've been banging my head on this one.

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?

Full Text Index And Full Text Search On Several Columns And Indivdiual Columns
1. Say if I want to do the full text search on three columns, "title", "description", "keyword". The search could be done only on one column, or combine any two columns, or combine all three columns, do I need to create full text index for all these possiblilities, like full text index on each column, and then full text index on any two columns and then full text index on all three columns?

If I have to make so many different kind full text index, that will slow my database performance or not?

2. Will the relevance also be calculated based on which column, such as the "title" will have higher relevance than "keyword", the "keyword" will have higher relevance than "description"?

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?

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?

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.

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
?

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

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

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.

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

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.