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

Code: Select all

'1' '12' '2345' '123'
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?

Code: Select all

where `row` = 1
Where the row contains 1?

Code: Select all

where `row` like '%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