How to make search functions?

Haris · 09-29-2007, 09:06 PM

I currently use MYSQL queries for searching data. How can I create a function which gets the closest data relative to the search term?

CMellor · 09-29-2007, 09:44 PM

Hope you don't mind a bit of reading. Check this out: http://dev.mysql.com/doc/refman/5.1/...xt-search.html

Haris · 10-02-2007, 02:14 PM

FULLTEXT is not supported in INNODB and foreign keys are not supported in MYISAM. :(

Tanax · 10-02-2007, 07:20 PM

PHP Code:


			
$pSql = sprintf("SELECT * FROM ".$system->db->table['players']." WHERE name LIKE %1$s OR level LIKE %1$s OR vocation LIKE %1$s",
        %$search%);    
        $pResult = $system->db->query($pSql);

If I search for Ser, I will get Server, and Service and other words with "ser" in it, IF they exists in the database ofcourse.

Haris · 10-02-2007, 07:26 PM

Did you read my post above?

Tanax · 10-02-2007, 07:34 PM

You can't use %$keyword% ?

Haris · 10-03-2007, 12:23 AM

Quote:

Originally Posted by Tanax

You can't use %$keyword% ?

I cannot use the FULL TEXT searching query since my database is INNODB.

Tanax · 10-03-2007, 05:17 AM

And what does that mean?
Can't you just say yes or no? >.<

Can you or can you not use %$search% ?

Haris · 10-03-2007, 07:50 AM

I can use the sprintf.

Tanax · 10-03-2007, 07:59 AM

So use it then? :P And use %$search%

Wildhoney · 10-03-2007, 11:20 AM

Quote:

Originally Posted by Tanax

So use it then? :P And use %$search%

Definitely the way to do it if you're looking for a string in a string. You also have regex, but unless the above proves too basic, then regex is too complex.

Tanax · 10-03-2007, 08:18 PM

What? Wildhoney is not gonna come with something better than my suggestion? :O W000t?!

This is my lucky day.. :D

Wildhoney · 10-03-2007, 10:20 PM

Haha. No, no, you seem to have gone about it the right way for what he wants :) !

Tanax · 10-04-2007, 09:56 AM

Haha, thanks :)

btw, Haris..

% is a like.. a universal mark, that basicly means "anything".
So you search for anything-$searchword-anything.

So if you search for ape, you would get the results
ape
apes
tape
rape
mapex

etc..

That's why they are good ;)
Also if you search a database, the database "post" would be a string, so you if you don't use the %, you have to search for the whole post to get a result.

Let's say you have a news post with this:
"Once upon a time there was an ape..."

If you don't use %, and search for ape, you don't get anything, cuz you have to search for "Once upon a time there was an ape" to a result.
IF you use, you would get the result "ape" and the newspost "once upon a time there was an ape" :)

Salathe · 10-04-2007, 11:34 AM

To to tie things up a little bit, here's some more information that you might like to know (or might at least entertain reading my post for). The percent symbol (%) in this case is a wildcard character meaning essentially 'zero or more characters'. To build on the above posts, searching for '%ape%' would match results such as ape, aperitif, tape, psychotherapeutic to name a few. This can be a good feature or bad, depending on the circumstances. Generally people know the word that they want to search for and in this case it would be better to only bring back records with the discrete word ape, rather than records with words containing the letters ape in them. Achieving this won't be covered in this post, but instead I'll move on to what I originally wanted to mention.

As well as the percent wildcard, you can also use an underscore (_) which is another wildcard but behaves differently. Like the percent, it matches any character but this time it will match one, and only one character, rather than 'zero or more'. To give an example, using '_ape' would match records; cape, tape but not lapel, paper, apes, drape, 'blue cape' and so on.

Tanax · 10-04-2007, 02:54 PM

Yea but salethe, if you search in a database row, the whole content of that row is a string.

Lorem ipsum something blablabla aye sit bla hey! que?

If you search for aye, you won't get any result, because there IS something before aye aswell as after.
So, you won't get any results from that.

That's why you need % on both sides of the search term.

Salathe · 10-04-2007, 03:45 PM

I'm not here to disagree with your post, just to give a fuller understanding of what can and can't be done. In most cases, using the % wildcard around your keyword(s) is what you'll want.

Tanax · 10-04-2007, 10:31 PM

Ahh :P Sorry

Haris · 10-06-2007, 01:03 PM

Thanks for the help!

It worked.

Edit:

Getting unexpected % error:

PHP Code:


			
            $szSQL = sprintf("SELECT user,id,rank FROM users WHERE user LIKE '%s' ORDER BY id", %$szUsername%);

Karl · 10-06-2007, 02:22 PM

Hey, you need to encapsulate the percentage signs within your string. Change this:

PHP Code:


			
$szSQL = sprintf("SELECT user,id,rank FROM users WHERE user LIKE '%s' ORDER BY id", %$szUsername%);

to this:

PHP Code:


			
$szSQL = sprintf("SELECT user,id,rank FROM users WHERE user LIKE '%s' ORDER BY id", "%$szUsername%");