Search and Remove


Is it possible to verify or "normalize" a field within mySQL I have a field that stores a lot of phone numbers, but it's possible they can be any length. I want to just grab the area code and prefix for a search on what city and state they belong too, but it's possible there can be a +, 1, or 011 before the area code starts, so it doesn't seem possible to use SUBSTRING since it can start in more than one position.
Posted On: Monday 29th of October 2012 09:14:18 PM Total Views:  74
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Is FULTEXT search the best for my database query

i am building a travellers database. It contains a list of each coutry that a traveller has been to. The column for 'visited countries' will just contained the list of each country that the member has visited. My question is ; what is the best way to search that column for 'visited countries' . E.g if a member wants to search for all members who have visited the USA. i know that FULLTEXT searches will allow me to interate though the column to finding matching country names. However, i also understand that FULLTEXT has limits. for example, if the keyword search appears more than 50% then no result is returned . it's possible taht certain countries will indeed appear more than 50 % of the time; so how do i get aroud this. Is there an alternative search method or better way to search this column for matching countries. i am also concerned about speed. my country list wil ultimately contained millions of countries visited; will the FULLTEXT search be fast enough to return the results Thank you for your kind attention. warm regards Andreea , Quote: Originally Posted by andreea115 I am using MQSQL could you please spell that out in words, i've never encountered that particular acronym Quote: Originally Posted by andreea115 i am not 100 per cent sure i understood what you meant. you have a members table, right with a column for visited countries perhaps if you could show the CREATE TABLE statement for that table, i'll show you how to normalize the countries column for further background, do a search for first normal form
VIEWS ON THIS POST

138

Posted on:

Monday 29th October 2012
View Replies!

2 tables search one return value from the other

I have 2 tables wk_days and start_end Code: +--------------------------+ | wk_days | +---------+----------------+ | ID | DAY | +---------+----------------+ | 1 | Monday | | 2 | Tuesday | | 3 | Wednesday | | 4 | Thursday | | 5 | Friday | | 6 | Saturday | | 7 | Sunday | +---------+----------------+ +------------------+ | start_end | +----+-------+-----+ | ID | START | END | +----+-------+-----+ | 1 | 1 | 5 | | 2 | 3 | 6 | | 1 | 4 | 6 | +----+-------+-----+ So wk_days holds the "index" of start_end. In start_end there are two entries. 1. The first starts on Monday and ends on Friday 2. The second starts on Wednesday and ends on Saturday 3. The third starts on Thursday and ends on Saturday Note: In start_end event 1 has two different start and end values In my syntax I want to search the event id (event id = id in start_end) and return the start and end days, which means replace the numerical value for start and end with the corresponding value in wk_days. So, I'm having problems writing the syntax. Does anyone have and possible direction to point me in
VIEWS ON THIS POST

101

Posted on:

Monday 29th October 2012
View Replies!

Differences in full-text searching in Mysql 5.0 (from 4.1)

Hi ! Whar's the difference in full-text searching in Mysql 5.0 (from version 4.1). Differences in behavior, conventions used, syntax, etc. etc. From what I have observed (very rapidly though and no testing), there are no differences at all. Is this true Thank you
VIEWS ON THIS POST

302

Posted on:

Monday 29th October 2012
View Replies!

Messageboard search feature - locking issues

ps: here is my my.cnf if that helps [mysqld] basedir = /usr datadir = /var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-innodb innodb_table_locks=0 skip-locking max_connections = 650 key_buffer = 256M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 2M sort_buffer_size = 2M table_cache = 1500 thread_cache_size = 128 wait_timeout = 900 connect_timeout = 10 tmp_table_size = 128M max_allowed_packet = 16M max_connect_errors = 99999 read_rnd_buffer_size = 524288 bulk_insert_buffer_size = 8M query_cache_limit = 2M query_cache_size = 128M query_cache_type = 1 query_prealloc_size = 16384 query_alloc_block_size = 16384 thread_concurrency = 8 [mysqld_safe] open_files_limit = 8192 err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick max_allowed_packet = 16M [myisamchk] key_buffer = 64M sort_buffer = 64M read_buffer = 16M write_buffer = 16M
VIEWS ON THIS POST

87

Posted on:

Monday 29th October 2012
View Replies!

Getting better search results

PHP Code: $query="(SELECT*FROMtableWHEREupper(name)like'%$upperKeyword%')"; I would like to change this statement to get better search results. It searches the name of a product. For example, doing a search for 'cat' an animal - would bring up results that: 1. have the word cat anywhere in the title, even if it is part of a word, bringing up useless results like certificate. If I change '%$upperKeyword%' to '$upperKeyword%' it will bring up any product with the first word being Cat - which does help, but then in the instance of a product name Siamese Cats, it wouldn't show that result. What is the best solution for getting better search results
VIEWS ON THIS POST

77

Posted on:

Monday 29th October 2012
View Replies!

REGEXP multipl search terms

Hi , Currently the following sql is being used to search products, at the moment it's searching whole words of one or emore search terms entered, however I need it to search one AND the other search term, rather then one OR the other search term. Code: SELECT * FROM products WHERE product_name REGEXP '[[::]]|[[::]]' For example typing: gold earring. Would bring back gold necklace and silver earring I can see where the problem is, the REGEXP uses the alternate operator '|', I thought substituting it for the and, would help but apparently not. Hope someone can help.
VIEWS ON THIS POST

87

Posted on:

Monday 29th October 2012
View Replies!

search only some of the records

I am searching a database with around 820,000 records with about 40 fields. I am only searching through it by two fields though. The data in the table goes back a ways (around 5 years) but I am only concerned with searching through the last 100000 records or so. Here is my query: select home_id from home_data where street_number like 'street_number%' and street like 'street%' is there a way to tell it to only look at the last 100000 records and not the whole database The query takes forever and I would like to speed it up.
VIEWS ON THIS POST

99

Posted on:

Monday 29th October 2012
View Replies!

Removing mySQL stopword-file and mySQL minimum lenght (fulltext searches)

, Does anyone of you know how I would be able to remove (I have full root server access) the stopword list in mySQL for fulltext searches and set the minimum word length to 1 I tried that way: * adding that code to my.cnf in /etc: " ft_min_word_len=1 ft_stopword_file="" " * restarting mySQL over WHM/Cpanel * redoing the fulltext indexes but that didnt give any effect. Does anyone of you know a way how to thx, pdoms
VIEWS ON THIS POST

132

Posted on:

Monday 29th October 2012
View Replies!

Using LIKE for search purposes

I'm using LIKE to search for business names in a directory. However, it's being a bit strict. For example, searching for "normans liquor" will not return "Norman's Liquor" because the search string does not include the apostrophe. Is there a way to loosen LIKE's strictness Or am I simply approaching this the wrong way
VIEWS ON THIS POST

110

Posted on:

Monday 29th October 2012
View Replies!

MySQL search with more than 1 criteria

all, first i want to appologise if the question is not in his place here but i`m really desperate, here is my question: i have 1 MySQL db with a couple of tables with lots of columns ... i was requested to make a search engine for this db and it was an offer i could not refuse, but when i began work on the scripts i found a problem ... how to make the script search the tables for more than 1 word ... i tried Code: SELECT * FROM `table` WHERE `word` LIKE `%something%` and the script works like a charm ... but if someone tries to search for "something new" ... how should i proceed I was brainstormed to try the 2 word with separate sql searches ... and after the 2 searches are over, compare the results and leave only the results that contain both words ... but if there are more than 2 words it gets ugly for the MySQL server ... please if someone has an idea how should i proceed share it with me, i would be very grateful.
VIEWS ON THIS POST

137

Posted on:

Monday 29th October 2012
View Replies!

Simultaneous searching over different columns?

I am trying to build a system that will allow for a "Google-like" search of our site. Each page consists of a title and various other content (TEXT) fields. What I'd like to do is a search something like: select pageid from pages where title is like '%query%' or summary is like '%query%' or content is like '%query%'... for all 20 different query terms. Then I'd like to automatically highlight the matching result. Now, it seems like a bit inefficient to essentially do the same query on so many different columns...the alternative is to do a separate select statement per column (i.e., limit the WHERE to 1 column), but that also seems inefficient. Any ideas
VIEWS ON THIS POST

88

Posted on:

Monday 29th October 2012
View Replies!

Msyql search like %

I am a beginner. I have a simple query select username from tbl_user where username like '%james%' It looks like there's no way to improve this Because it's too simple. Can I index username or it doesn't help because of like '% Thank you for any help.
VIEWS ON THIS POST

141

Posted on:

Monday 29th October 2012
View Replies!

Help with a MySQL search query

, on my website, users can post ads (items) and attach up to 3 photos for each item. I have a search page where users can select a category, subcategory, a province, a city and then search for an item. My problem is that if an item has more than 1 picture attached to it, in the search results page, the same item appears twice, but with a different picture (ex: if the item has 1 picture it appears once, if it has 2 pictures, it appears twice, if it has 3 pictures, it appears 3 times. SAME item, just the pictures are different). Here's my function : Code: $query = "SELECT item.*, picture.id as pictureId, picture.title as pictureTitle, picture.filePath as pictureFilePath, city.id as cityId, city.name as cityName, province.id as provinceId, province.name as provinceName, locationType.id as locationTypeId, locationType.name as locationTypeName FROM (item, city, province, locationType) LEFT JOIN picture ON item.id=picture.fk_item_id WHERE item.fk_subcategory_id=$subcategoryId AND (item.fk_user_id > 0) AND item.fk_city_id=city.id AND item.fk_province_id=province.id AND item.fk_locationType_id=locationType.id"; I would like to limit somehow that - if the item has more than 1 picture attached - only ONE item is displayed in the search results page, with one picture (first, or random).
VIEWS ON THIS POST

170

Posted on:

Monday 29th October 2012
View Replies!

How to implement query to search (tag, id) database?

So say I have a table full of one word tags, each associated with an ID (foreign key) to something else (product, article, whatever). Each ID can have mupltiple tags, too, such as round, blue, fast... What's the best way to search on a table like this for IDs matching specific search terms The search criteria can me rather complex, too and include NOT. An example would be finding all IDs that are ((blue AND round) AND NOT fast). Hopefully that explains it.
VIEWS ON THIS POST

207

Posted on:

Monday 29th October 2012
View Replies!

Problem with search query using 'LIKE'

Can we modify search query using LIKE %search% so that for eg: when we search for 'men' output contains only those with word 'men' and not with words 'women' , 'sacrament','menthol' etc.or is there a different query for this.Please help.I AM DESPERATELY IN NEED OF THE QUERY. , sorry, lnxgeek, but LIKE will not do want he wants please read his question again, it's right there in plain sight AjithTV, you need to use REGEXP and specifically search for men using word boundaries ... WHERE somecol REGEXP '[[::]]'
VIEWS ON THIS POST

127

Posted on:

Monday 29th October 2012
View Replies!

Anyway to do an AND/OR for a search?

Didn't get any sleep on this... Now I have ONE text field, let's say the user searches "blue 2007 Mustang". My Table looks like: ID | COLOR | MAKE | MODEL | YEAR 1 blue Ford Mustang 2007 I have a query that looks like "SELECT * FROM cars WHERE color LIKE '%search%' OR model LIKE '%search%' OR year LIKE '%search%'" Obviously this will work if the user searches "2007" or "Mustang" but not "2007 Mustang"! Now how in the heck can I do this I've searched on here, looked into my MySQL bible book, googled it, looked at opensource search engines and I don't get it still! Then I tried a fulltext search, I had to convert my table to MyISAM (also made FULLTEXT columns: ALTER TABLE cars ADD FULLTEXT INDEX (model, color)) and tried this query: SELECT * FROM cars WHERE MATCH (model, color) AGAINST ('Green Mustang') and I get this warning: "Can't find FULLTEXT index matching the column list". There has got to be a way to make a non-fulltext search I wish there was a AND/OR opperator I'm currently trying xOR but it doesn't seem to be working like I would like it to...
VIEWS ON THIS POST

128

Posted on:

Monday 29th October 2012
View Replies!

Two unrelated tables in search results?

Hi. I have two unrelated tables and I need to use them both for search results (they both provide the same "kind" of data but the tables and columns are quite different). Can somebody help me understand how to do this
VIEWS ON THIS POST

169

Posted on:

Monday 29th October 2012
View Replies!

Help: Best ways to streamline saved search keywords

I'm looking for the best way to implement saved search keywords, used to search another table in my database. I have a table (searchkeywords) that stores my keywords like so: user_id, search_id, key_words which contains the following as an example: 1, 9, "Red Carpet" I then search the database by breaking up the key_words and adding them to a select statement like so: PHP Code: SELECT*FROMusersINNERJOINsearchkeywordsONuser_id Foreachuserkeywords\t{ \t\tBreakthekeywordsintoLIKEstatments \t\tSELECT*FROMcontentWHEREcontent.titleLIKE'%red%'ANDLIKE'%carpet%' } I do this because I want it to find "Today on the red carpet" as well as "The red velvet carpet sale" I'm doing this query 1000s of times and it isn't very efficent. I'm using fulltext search already, but I thought I could combine these into one statement. I thought about creating a different input for each keyword like so: 1,9,"Red" 1,9,"Carpet" But I don't see how that would help me because I need to be sure BOTH terms are found in my search. I also thought if there was someway to create the mySQL statement and store and execute that like so: 1,9," LIKE '%red%' AND LIKE '%carpet5' " But I don't know if that is possible. Anyway, any input is greatly appreciated.
VIEWS ON THIS POST

124

Posted on:

Monday 29th October 2012
View Replies!

Need help with a search query

ok so i have an array that contains a bunch of words. I want to use this to search through a table i have and pull out the ones that contain all of these variables. this is how my table is setup uniqueID | ownerID | tag where tag is what i want to search in for those array variables above. So each of these 'tags' belong to something else referenced by ownerID. So most of these tags will have the same owner. Some might even have the same tag, but different owner. Ok so what I want to be able to do is pull out the ownerID that contains all the elements of the array. So is there a way to accomplish this simply by a query(s) I'm using php, so the other way would be to run loops, but that might require alot of queries depending on how many elements are in the array and comparing the ownerid. thx
VIEWS ON THIS POST

153

Posted on:

Monday 29th October 2012
View Replies!

Fulltext search help

Folks: I am having a bear of a time doing a fulltext search for IP addresses. I wanted to use a fulltext search since I am searching many fields... any help on syntax I think it is taking the "." as some other character example select field1,2,3,4, from table where match(field1,2,3,4,5,6,7,8,9) AGAINST ('10.10.10.10' ); or in IN BOOLEAN MODE
VIEWS ON THIS POST

131

Posted on:

Monday 29th October 2012
View Replies!