MYSQL :: Searching On Two Keys With OR?

CREATE TABLE And Primary Keys And Keys
I am going through Mayank Gandhi's article on "Build An Automated PHP Gallery System In Minutes" partly because I want to do just that and also to learn more SQL.

The way he creates a table is with:

CREATE TABLE gallery_category (
category_id bigint(20) unsigned NOT NULL auto_increment,
category_name varchar(50) NOT NULL default Ɔ',
PRIMARY KEY (category_id),
KEY category_id (category_id)
) TYPE=MyISAM;

The article deals more with PHP, so there is not as much detail in the SQL as I would like.

I don't understand why you would use category_id as a PRIMARY KEY and also a KEY. Nor the syntax of the KEY line.

That appears redundent to me. I can't find documentation addressing this on the MySQL web site or in the books that I own.

Triggers As Primary Keys And Foreign Keys?
I have created a sequence and then a trigger to create my primary key. I need to connect to the Customer table via the foreign key Customer_id. However, I get an error saying:
*
ERROR at line 8:
ORA-02270: no matching unique or primary key for this column-list

This is my code:

String Keys Vs. Numeric Keys
I know that using numeric keys is more efficient than string keys but can someone tell me how much more efficient it is.
It would be a lot easier for me to use a string as the key, but someone recommended me to use a numeric key.Is it bad practice to use a string key?

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?

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

Searching Date
I have a date type field in my table. Say the value is this month of this year - what sql would i need to search for dates which are a certain time before todays date, like 3 months?

Fulltext Searching
Fulltext is doing well on searches that have 1 word, but it goes crazy when I use more then 1 word.

Searching By First Letter
I need a query to search for entries in my table where the first letter of last_name and first_name is the selected value. So, when I click on the letter 'a', it would return all entries where the last_name and first_name begin with the letter a.

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 ?

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
?