Welcome to WebmasterWorld Guest from 35.172.195.49

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Search String in a Field

     
11:10 pm on Oct 13, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 25, 2005
posts:392
votes: 0


If I have a field named : fruits
with the following record: apple, banana, mango, melon

How can do a query / loop to extract individual fruit?
And a query that will search a fruit, let say I just want to search for mango

Thanks

2:21 am on Oct 14, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 25, 2005
posts:392
votes: 0


Let me clarify my question
apple, banana, mango, melon
is just a single record (meaning 1 record could have many fruit name)

So my question is how can I search for the all records (rows) with

mango
in it?
8:43 am on Oct 14, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 7, 2007
posts:103
votes: 0


I think that one way to accomplish this would be to use LIKE statement:
SELECT * FROM table WHERE fruits LIKE '%mango%'

Of course this doesn't work if you have fruits that have name of another fruit in the name:
SELECT * FROM table WHERE fruits LIKE '%grape%'
This query will find rows with 'grape' and rows with 'grapefruit'.

More info on pattern matching can be found from MySQL Reference Manual:
[dev.mysql.com...]

9:38 am on Oct 14, 2008 (gmt 0)

Preferred Member

10+ Year Member

joined:Nov 25, 2005
posts:392
votes: 0


Correct. I need a certain function (or what ever) that will find if a certain fruit exist in a row. I need the one that will return true if found an EXACT match.
11:05 am on Oct 14, 2008 (gmt 0)

Junior Member

10+ Year Member

joined:Aug 7, 2007
posts:103
votes: 0


I think your best bet would be to go with the REGEXP query.
I'm not very good with regular expressions, but maybe this will work:

function searchFruit($fruit) {

$sql = "SELECT * FROM table WHERE fruits REGEXP '^".$fruit."$'";

// Rest of the code
}

[edited by: deMorte at 11:05 am (utc) on Oct. 14, 2008]

3:40 pm on Oct 14, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 24, 2005
posts:697
votes: 0


SELECT * FROM table WHERE fruits LIKE 'mango %' OR fruits like '% mango' or fruits like '% mango %'

notice the use of blank spaces, this will help solve the problem deMorte has mentioned, i.e. it will not return grapefruit for grape.

2) Extract that value in a variable
3) split() the Variable into an array using whatever delimeter you have in your field, i think whitespace?
4) run in_array() on that array and that will return true or false for your fruit name

Infact, i think you only needed that Query, rest is not needed i think but i dont like deleting what i have already posted. That Query itself will return only those rows which have exact match on your fruit name

[edited by: Anyango at 3:43 pm (utc) on Oct. 14, 2008]

4:07 pm on Oct 14, 2008 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 24, 2005
posts:697
votes: 0


Sorry i didnt notice your delimeter was a comma. So this should work

SELECT * FROM table WHERE fruits LIKE 'mango,%' OR fruits like '%, mango' or fruits like '%, mango,%'

8:19 pm on Oct 14, 2008 (gmt 0)

Senior Member from FR 

WebmasterWorld Senior Member henry0 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Apr 19, 2003
posts: 4449
votes: 11


Basic of extraction [webmasterworld.com]

The second question: If you are looking for a precise name then any forms of % will bring the exact match plus variations

if you know what you are looking for you will need a subquery such as

WHERE
fruit='$fruit'

$fruit is the one fruit you are looking for