Page 1 of 1
Finding a word in a database
Posted: Thu Dec 15, 2005 8:42 pm
by Luke
Alright... in my database for my directory, I have a column named categories... in that column there is a list of numbers (category ids) in the following format...
column wrote:1 23 156 87 3
If I wanted to return any row that contained the number 1, how would I do that without returning everything with a 1 in it (whether it was 1 or 112) I want it to only return rows with a 1 in it... I can't figure it out.
Posted: Thu Dec 15, 2005 8:45 pm
by Charles256
look at the SQL command LIKE . That's probably what ya need.
Posted: Thu Dec 15, 2005 8:54 pm
by Luke
well I tried
Code: Select all
SELECT * FROM table WHERE categories LIKE '%1'
No luck. I don't think LIKE is what I need... I'm starting to think I will need to reformat the column to be like this..
But that will be a lot of work... I don't really want to do that... anybody know how I can get this accomplished?
edit:
Code: Select all
SELECT *
FROM directory
WHERE `directory_categories` = '10'
OR `directory_categories` LIKE "%10 "
OR `directory_categories` LIKE " 10%"
OR `directory_categories` LIKE "% 10 %"
Seems to be working
Posted: Fri Dec 16, 2005 7:15 am
by timvw
If you're using mysql you can use
full-text search.
Posted: Fri Dec 16, 2005 2:40 pm
by Luke
Code: Select all
SELECT * FROM directory WHERE MATCH (directory_id) AGAINST ('10')
did not work... hmm...
Posted: Fri Dec 16, 2005 2:47 pm
by korto
I might not have understood well what you are trying to accomplish. Do you want to get only rows of value 1? Are the values of 'categories' of type string?
Posted: Fri Dec 16, 2005 2:57 pm
by josh
Where the row is 1?
Where the row contains 1?
where the row contains 1 but is not 1?
Code: Select all
where `row` like '%1%' and `row` <> 1
Posted: Fri Dec 16, 2005 3:43 pm
by Luke
Nope... still nobody understands what I am trying to do... let me start over:
directory:
Code: Select all
id name description phone_number category
1 Sierra Somethting 5308778123 1 2 564 124
2 comp something 5308778612 10 1 567
3 som somethin 1234567898 12 36
4 1q2 somdthasdf 9876543218 1
5 as;lk soemthin 2342345223 23 25 65 321
Now I want to find every row that contains "1" in the category list, but everything I try returns anything with a 1 in it whether it's 1 or 123
I would only want it to return row # 1, 2, and 4 in this example.
EDIT:
Like I said...
Code: Select all
SELECT * FROM directory WHERE `directory_categories` = '72' OR `directory_categories` LIKE '72 ' OR `directory_categories` LIKE ' 72' OR `directory_categories` LIKE '% 72 %'
seems to be working, but I don't know if it's the most efficient way.
Posted: Fri Dec 16, 2005 4:10 pm
by Gambler
Many-to-many relationships usually should be expressed through separate table.
create table links(
`id` int NOT NULL,
`directory` int NOT NULL,
KEY (`id`)
KEY (`directory`),
);
This is much more efficient.
Posted: Fri Dec 16, 2005 6:15 pm
by Luke
I'm sorry... that sounds helpful, but I don't understand... can you explain a little better?
I got it working, but not as well as I had hoped... the directory isn't very big, so the time it takes to find them isn't very long right now, but I bet if there were more rows, it would take foreve the way I'm doing it (the way I said above)
Here is the directory in action:
http://www.paradisedirect.com/directory.php