Finding a word in a database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Finding a word in a database

Post 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.
Charles256
DevNet Resident
Posts: 1375
Joined: Fri Sep 16, 2005 9:06 pm

Post by Charles256 »

look at the SQL command LIKE . That's probably what ya need.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

If you're using mysql you can use full-text search.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

Code: Select all

SELECT * FROM directory WHERE MATCH (directory_id) AGAINST ('10')
did not work... hmm...
korto
Forum Commoner
Posts: 36
Joined: Thu Aug 18, 2005 6:30 am
Location: Greece
Contact:

Post 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?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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.
Gambler
Forum Contributor
Posts: 246
Joined: Thu Dec 08, 2005 7:10 pm

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post 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
Post Reply