Searching Unknown Single Or Multiple Words Against Database Table Field

[justsomeguy]

are the array elements in the " "?

I'm not sure what you mean. The explode function splits a string up based on another string. That line is splitting up $pre_filter on a space, so the elements in the array will be the words that were separated by spaces.

what's purpose $default= single quotes?:

It sets a default value for the variable so that it's not required when you use the function. The second parameter to the function is optional. If it's not sent to the function, then it will be set to an empty string.

[paulmo]

thanks for all the help, jsg. you are the greatest. i've just set up easyphp (the only one i could (con)figure out) on a t42 thinkpad scored on ebay, and copying mysql and php manuals to it as wifi connection is sketchy. playing with text functions on local server should keep me busy for a while.

[paulmo]

jsg, the following code works, but i'm unable to get my head around it, even after consulting my book. i've marked areas of confusion with //. if you can shed some light i'd appreciate it.

$where = ''; //defining an empty variable. why? $common = array(	'to',	'of',  'the',  'this',  'that',  'and');foreach ($get_search as $word) {  if (strlen($word) < 3) //if array element (word) length is 3 or greater (but < looks like 'less than').	continue;  if (in_array(strtolower($word), $common)) //the $common array is for words NOT to search on, but this if statement sounds like "if $word (exploded submit text) and $common (exceptions) ARE in array, keep searching. i understand if $word in array, but $common?	continue;  if ($where != '') //"if $where is not empty," but it's defined as empty variable above? 	$where .= ' OR '; //concatenate empty $where with OR boolean...not understanding this.  $where .= "terms LIKE '%" . mysql_real_escape_string($word) . "%'"; //this looks like a query, but there's no SELECT FROM statement. unsure what the " after the wildcard % does? }

also, i'm using stripslashes to echo apostrophes without "\", and submitted text with apostrophes is finding matches on $row (i've manually \' all apostrophes in data). but i also need to have query ignore all punctuation, including commas, periods, question marks, in both user submitted text and in $row text. for example, submitted "today," or "today?" is not finding match against "today" in table. i'm using

$message = mysql_real_escape_string($message);

but that's not helping here. i've read about FULLTEXT, which seems to ignore punctuation (and stop words!), but mysql manual states that only literal strings may be searched on, and not variables. since i've exploded the user text into an array and variable, it would seem FULLTEXT wouldn't work. true? i'd like to use it if possible, like you suggested. (turn variable into literal string?) anyhow, FULLTEXT index is not working with existing code; tried it. if not possible, i'll have to use php/regex/array(?) to ignore punctuation in both user submitted text and db table. how to get started with that? if the FULLTEXT question is tangential here, i'll post to sql forum. thank you!

[justsomeguy]

$where = ''; //defining an empty variable. why?

$where is used later on, like here:$where .= "terms...If I left out the initialization to start $where as an empty string, PHP would issue an error when I tried to concatenate a value to it, it would say that $where is undefined. It's always a good practice to explicitly declare your variables before you use them. In languages like C, it's required. This C program will not even compile:

#include <stdio.h>int main(){  for (i = 0; i < 10; i++)	printf("%d\n", i);  return 0;}

This one will run:

#include <stdio.h>int main(){  int i;  for (i = 0; i < 10; i++)	printf("%d\n", i);  return 0;}

if (strlen($word) < 3) //if array element (word) length is 3 or greater (but < looks like 'less than').if (in_array(strtolower($word), $common)) //the $common array is for words NOT to search on, but this if statement sounds like "if $word (exploded submit text) and $common (exceptions) ARE in array, keep searching.

Check what the continue statement does. The code is saying that if the length is less than 3, or if the word is common, to continue the loop.http://www.php.net/manual/en/control-structures.continue.php

if ($where != '') //"if $where is not empty," but it's defined as empty variable above?

Yeah, it's empty the first time through the loop, but that doesn't mean it's *always* empty. You can add an echo statement to print $where every time through the loop if you want to see what happens during the loop. Most likely, the second time through the loop $where will not be empty, so you'll add an OR to it to separate the conditions.

$where .= "terms LIKE '%" . mysql_real_escape_string($word) . "%'"; //this looks like a query, but there's no SELECT FROM statement. unsure what the " after the wildcard % does?

It's not a query, it's the where part of a query. The rest of the query is added later. The purpose of the loop is only to build the where clause.It would probably be good if you added a statement to print $where each time through the loop so that you can see what it's doing.

but i also need to have query ignore all punctuation, including commas, periods, question marks, in both user submitted text and in $row text.

The best solution is to use str_replace to strip all punctuation from the search terms before using them to search.

mysql manual states that only literal strings may be searched on, and not variables.

That's talking about SQL variables. You're not using any SQL variables in the query, only literal strings.

anyhow, FULLTEXT index is not working with existing code; tried it.

Fulltext searching uses a different query, not the LIKE query being used here. Check the MySQL manual for the syntax to use to perform a fulltext search, but this code isn't executing a fulltext search.

[paulmo]

wow, thanks so much for those explanations! i get the initial empty variable now; read about continue statement...seems it should have been named 'skip' since it skips. i'll print $where to see what it does through loop. did you build variable $where loop, and not use WHERE conditional followed by $word variable, because you've made an array loop to set strlen and skip stop words? will check out str_replace. have wanted to use that. will also experiment with fulltext syntax. even if i use fulltext i'll use this array code as a first "filter" because some array words will echo a statement before the rest gets sent to fulltext search (if possible). thanks jsg for the great lesson!

[justsomeguy]

The where clause is built in the loop because it needs to loop over the words to search for. In other words, the where clause needs 1 condition per word, so it uses a loop to build the where clause, and then adds that onto the rest of the query.

[paulmo]

ok i get it, one condition per word, thanks. tried fulltext again: even with existing code, some fulltext features are apparent. for example i'm getting 3 letter matches, like "tod" will echo matched "today." and punctuation in data is not preventing matches, where user "today," would previously not match "today"...now it is. same with ? .! fulltext stopwords are not in effect though.i'd like to go <4 letters in this foreach, and let words "air, sea, key" etc. pass through to $.where. how to proceed?

foreach ($get_search as $word){  if (strlen($word) < 4//, $word !='air sea key') ?? 	continue;  if (in_array(strtolower($word), $common))	continue;

[justsomeguy]

foreach ($get_search as $word){  $word = trim($word);  $skip = false;  if (strlen($word) < 4)  {	if (!in_array(strtolower($word), array('air', 'sea', 'key')))	  $skip = true;    }  if (in_array(strtolower($word), $common))	$skip = true;  if ($skip)	continue;

[paulmo]

that's working beautifully; thanks! just as i was beginning to understand 'continue' as 'skip,' please explain 'skip' function. i searched manual and no matches. thank you jsg.

[justsomeguy]

There's no skip function, I just used a variable that I called $skip to hold a boolean value for whether or not the loop should continue.

[paulmo]

[paulmo]

jsg, this code works but i'm missing brackets and 'else' statements. wondering if that's necessary or anything else i should do to chain of if statements. my head's spinning trying to figure out $skip true/false. seems inverted in what it does and what i'd think it would do. maybe you could shed light on the boolean situation. i basically know it refers to and or or. thank you

 if (in_array(strtolower($word), $common))	$skip = true;	if (in_array(strtolower($word), array('cat')))echo "Cats have their own agenda. </br></br>";if (in_array(strtolower($word), array('peace',)))echo "Relaxing is good.</br></br>";

the rest of previous code follows...

if ($skip)	continue;

etc.(code wasn't executing when adding $skip = true/false to my if statements)

[justsomeguy]

The skip variable is just being used to keep track of whether or not to use the continue statement. It's just there so that there only needs to be 1 continue statement, just to simplify things. You can remove it if you want and just use continue statements. This does the same thing as the code I posted above:

foreach ($get_search as $word){  $word = trim($word);  if (strlen($word) < 4)  {	if (!in_array(strtolower($word), array('air', 'sea', 'key')))	  continue;  }  if (in_array(strtolower($word), $common))	continue;  ...

[paulmo]

thanks for explanation. checked out fulltext query, and min/max word length is static as far as not permitting exceptions (like sea, air, bee if choosing >4 min word length). so the code you've helped/written for me is better in that i've got exceptions and 4 letter min word. what i need to do now is when a match is made in if statement, like here,

if (in_array(strtolower($word), array('happy', 'glad',))) echo "You are feeling good, {$post_name}";

, the string text $where gets matched to a particular table. the matches in that table will correspond to echo statements in another table with same row #. (several if/in_array statements will continue on through until 1) initial match is made and associated table match begins, or 2) no match is made, so echo 'please write again.')for example, in if statement above, if string matches 'happy,' then the string gets searched against 'happy_table.' the word match(es) there will echo corresponding text in another table, 'happy_echo', with same row# to avoid confusion. does my process seem right, or efficient? i think i'm suggesting a table join?

[justsomeguy]

If that's what you want to do, I'm not sure it's up to me to say whether it's "right" or not. I mean, if you want your application to check for keywords that change the table to search in, that's fine with me.

[paulmo]

ok, thanks. seems often there's a different way of doing something so thought i'd float that one by. will proceed and post if/when i get hung up.