Jump to content

Searching Unknown Single Or Multiple Words Against Database Table Field


paulmo

Recommended Posts

this is for a search box, searching any matching 4+ letter word or words against myisam table text field 'terms.' not getting any mysql errors, just the parse error below. (not sure if the rest of it would work though...) please take a look. thanks.

$message_string =$_POST['message'];$pre_filter=trim($message_string);$get_search=explode(" ",$pre_filter);$sql = "SELECT * FROM beta";foreach ($get_search as $final_string){$sql = "WHERE terms LIKE '%final_string%' or";$sql. = substr_replace($sql,"",-2); //take off the last 'or'$posts = mysql_query($sql);// line above ^ gets Parse error: syntax error, unexpected '=' $n=0;while($row=mysql_fetch_assoc($posts)){$n++;echo $row['terms'];echo "<br>";}

Link to comment
Share on other sites

This line is actually the offending one:

$sql. = substr_replace($sql,"",-2);

The concat-assign operator is one language construct and cannot be used with a space between it, so it has to go like:

$sql .= substr_replace($sql,"",-2);

Link to comment
Share on other sites

thanks synook for that concat advice. still getting

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource //tried 'array' alsoWarning: Cannot modify header information - headers already sent by //after last line of page, and i've included ob_start and ob_end_flush in page. also i'm not using header () on this page.

revised code:

$message_string =$_POST['message'];$pre_filter=trim($message_string);$get_search=explode(" ",$pre_filter);$sql = "SELECT * FROM beta";foreach ($get_search as $final_string){$sql = "WHERE terms LIKE '%final_string%' or";$sql .= substr_replace($sql,"",-2); //take off the last 'or'$posts = mysql_query($sql);$n=0;while($row=mysql_fetch_assoc($posts)){$n++;echo $row['terms'];echo "<br>";}	}

Link to comment
Share on other sites

You don't want to concatenate anything, I'm not sure how the dot got in there in the first place (I believe it's fine to have a space between . and =, but you don't want to concatenate the SQL statement to itself).$sql = substr_replace($sql,"",-2); //take off the last 'or'

Link to comment
Share on other sites

I believe it's fine to have a space between . and =
Hey! I actually tested this one, you get a syntax error :)
Link to comment
Share on other sites

i'm really mashing things up here in my quest to search any word from form variable $message against a table field 'terms.' omitted concatenate . which didn't help. here is the code with my notes as i've been moving stuff around, trying to simplify process (most recently taking out filtering/foreach/or statements and validation). thanks for your help.

$name = $_POST['name'];$message = $_POST['message'];mysql_connect("xxx", "xxx", "xxx")or die(mysql_error());mysql_select_db("xxx") or die(mysql_error());;// filtering v v/*$message = mysql_real_escape_string($message); //sql injection$message = strtoupper($message); $message = strip_tags($message); $message = trim ($message); $name = trim ($name); $name = strip_tags($name); //remove code$name = mysql_real_escape_string($name); //sql injection*/$pre_filter=trim($message);$get_search=explode(" ",$pre_filter);$sql = "SELECT * FROM xxx WHERE terms LIKE '%get_search%'";//$sql = "SELECT terms FROM xxx WHERE MATCH (terms) AGAINST (message)";$result = mysql_query($sql) or die (mysql_error ());$n=0;while($row = mysql_fetch_assoc($result)) {$n++;	echo $row['terms'];	echo "<br>";}/*//$sql = "SELECT * FROM beta";//foreach ($get_search as $final_string)//foreach ($get_search)//{//$sql = "WHERE terms LIKE '%final_string%'"; //or";//$sql = substr_replace($sql,"",-2); take off the last 'or'

Link to comment
Share on other sites

$sql = "SELECT * FROM xxx WHERE terms LIKE '%get_search%'";That query searches for records that contain the text "get_search". The $get_search variable is an array. If you want to search for records that contain any of the words in the array, then you need to produce a query like this:SELECT * FROM xxx WHERE terms LIKE '%word1%' OR terms LIKE '%word2%' OR terms LIKE '%word3%'etc. If you want to search for all words, use AND instead of OR.So you need to loop through the array and build that query. Build the WHERE part in a loop, and then put the query together like this:

$where = '';foreach ($get_search as $word){  $where .= ...}$sql = 'SELECT * FROM xxx WHERE ' . $where;

So $where would contain this part: terms LIKE '%word1%' OR terms LIKE '%word2%' OR terms LIKE '%word3%'

Link to comment
Share on other sites

thanks...

SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'terms LIKE '%word1%' OR terms LIKE '%word2%' OR terms LIKE '%word3%''

i'm seeing the explode/array concept now...this is how i've put your code together. i'm sure something's amiss:

$message = strtoupper($message); $message = strip_tags($message); //$message = trim ($message); //$message = str_split($message); $name = trim ($name); $name = strip_tags($name);$name = mysql_real_escape_string($name); $pre_filter=trim($message);$get_search=explode(" ",$pre_filter);$sql = "SELECT * FROM xxx WHERE terms LIKE '%word1%' OR terms LIKE '%word2%' OR terms LIKE '%word3%'";$where = '';foreach ($get_search as $word){  $where .= "terms LIKE '%word1%' OR terms LIKE '%word2%' OR terms LIKE '%word3%'"; }$sql = 'SELECT * FROM xxx WHERE ' . $where;$result = mysql_query($sql) or die (mysql_error ());$n=0;while($row = mysql_fetch_assoc($result)) {$n++;	echo $row['terms'];	echo "<br>";}

2 $sql queries don't seem right...i'm trying to follow the idea about $where loop to define array.i'm grateful for your help here...would str_split or preg_split("/[\s,]+/") functions be alternative to array method (unsure how to implement, was reading php manual)?

Link to comment
Share on other sites

$where = '';foreach ($get_search as $word){ $where .= "terms LIKE '%word1%' OR terms LIKE '%word2%' OR terms LIKE '%word3%'"; }
Now why would you do that? You're not trying to search for "word1", "word2", and "word3" right? You're trying to search for words in the array I assume, but you aren't using $word in the query. The point is to build a query that contains each word in the array, not just add a static string to the query for every word that shows up.You want to build a string like this:terms LIKE '%word1%'for each word in the array. That DOES NOT mean to search for "word1" for each word in the array, it means substitute the word in the array for "word1" (which I used as an example, not to tell you specifically to write "word1" in the query). Since it needs to be separated using ORs, each time through the loop you check if you need to add an OR.
$where = '';foreach ($get_search as $word){  if ($where != '')	$where .= ' OR ';  $where .= "terms LIKE '%" . mysql_real_escape_string($word) . "%'";}$sql = 'SELECT * FROM xxx WHERE ' . $where;echo $sql;

Link to comment
Share on other sites

The point is to build a query that contains each word in the array, not just add a static string to the query for every word that shows up.
thank you again, justsomeguy! i take it the $mysql echo was to test...anyhow i'm getting echoes from the db table rows, but the matches are inaccurate, as in "let's (or lets) go dancing" is echoing a row with none of these words. i've included this code following the $sql query:
$result = mysql_query($sql) or die (mysql_error ());$n=0;while($row = mysql_fetch_assoc($result)) {$n++;	echo $row['terms'];	echo "<br>";}

couple other questions please: how to escape user apostrophe ' on words so not echo "let\'s" ? i see in my book that \ is escape character but how to apply to user text submit? what are your thoughts on str_split or preg_split functions? those relevant here or not so much? as for my not seeing how %word1% etc. was to demonstrate, it's my inexperience (again). learned a lot about switch/case statements from you last year, and i'll learn from this one too.regards, paulmo

Link to comment
Share on other sites

"let's (or lets) go dancing"Since it's just splitting on spaces, that's going to look for rows that contain any of these:let's(orlets)godancingThat will even match something like "going", since "go" is in that.

how to escape user apostrophe ' on words so not echo "let\'s" ? i see in my book that \ is escape character but how to apply to user text submit?
For text through get or post, you would need to check if the server is using magic quotes and, if so, strip slashes. I have this function to get info from get and post:
function form_var($var, $default = ''){  $retval = $default;  if (isset($_POST[$var]))	$retval = $_POST[$var];  elseif (isset($_GET[$var]))	$retval = $_GET[$var];  if (is_array($retval))  {	foreach ($retval as $k => $v)	{	  $retval[$k] = trim($v);	  if (get_magic_quotes_gpc())		$retval[$k] = stripslashes($v);	}  }  else  {	$retval = trim($retval);	if (get_magic_quotes_gpc())	  $retval = stripslashes($retval);  }  return $retval;}

If you have a value being submitted called "search", you would get it like this:$search = form_var('search');The function will check in post first, then get. It will trim the value to remove leading and trailing spaces, and apply stripslashes if necessary to remove the slashes. It will work with arrays also, if you submit an array. You can also give it a default value to use if the variable wasn't found in either get or post:$search = form_var('search', 'default');If you don't give it a default and it wasn't found, it will just be set to an empty string. That's what I use whenever I need anything from get or post though.You would only use str_split to split up a string based on the number of characters in each chunk, I don't think that applies here. You could use preg_split, but it's going to take longer than explode. preg_split is really only useful if you want to split on a pattern instead of a specific value, like a space. If you're working with specific values the string functions are always faster then the regex functions.

Link to comment
Share on other sites

so splitting on spaces returns any word or isolated character. way to adjust for 3+ character words? thanks for your stringslash code. was reading manual about that, stripslashes_deep...so this is what i've got; still getting "\":

$message = $_POST['message'];$post_message = ucfirst($message);$message = mysql_real_escape_string($message); $message = strtoupper($message); $message = strip_tags($message); //$message = trim ($message); $pre_filter=trim($message);$get_search=explode(" ",$pre_filter);function form_var($message, $default = ''){  $retval = $default;  if (isset($_POST[$message]))	$retval = $_POST[$message];  elseif (isset($_GET[$message]))	$retval = $_GET[$message];  if (is_array($retval))  {	foreach ($retval as $k => $v)	{	  $retval[$k] = trim($v);	  if (get_magic_quotes_gpc())		$retval[$k] = stripslashes($v);	}  }  else  {	$retval = trim($retval);	if (get_magic_quotes_gpc())	  $retval = stripslashes($retval);  }  return $retval;}$message = form_var('message');

also if you would please explain this alias and $k and $v variables:

($retval as $k => $v)	{	  $retval[$k] = trim($v);

big thanks jsg.

Link to comment
Share on other sites

way to adjust for 3+ character words?
foreach ($get_search as $word){  if (strlen($word) < 3)	continue;  if ($where != '')	$where .= ' OR ';  $where .= "terms LIKE '%" . mysql_real_escape_string($word) . "%'";}

After the loop you'll need to check if $where is empty. If it is, no words were longer than 2 characters, or they didn't fill anything out at all.

also if you would please explain this alias and $k and $v variables:
That's a foreach loop. In the loop, $k is the array key and $v is the value.http://www.php.net/manual/en/control-structures.foreach.php
Link to comment
Share on other sites

strlen, great to see it in action, thanks! how about words to except (the and this that etc.)?$k and $v, key and value, read it last week; will review!next i'm doing 3 things, and hoping to "build" them from existing query. please offer basic methods to get me going. 1) str_replace for specific keyword matches, like if match table row with user words steak cheese etc. then echo "you're hungry."2) similar replace for table row match of user string that starts with can, should, will, (or ends with ?) and echo "you're asking a question." 3) capitalize any name in $message string; ted, steve, mary etc. (i'll populate row or import row of names?) thanks

Link to comment
Share on other sites

It sounds like you just want to compare words against arrays of words. You would have one array of common words to remove, another array of names, an array of food, etc. You can use in_array to check if a value exists in an array.

$common = array(  'the',  'this',  'that',  'and');foreach ($get_search as $word){  if (strlen($word) < 3)	continue;  if (in_array($word, $common))	continue;  ...

You might also want to take a look at fulltext searching with MySQL. You can define a fulltext index on a text field in the database, and then use the MATCH..AGAINST in a WHERE clause to search in that field in various ways. You can do a boolean search, which will just return true/false if it was found, or a natural language search where it scores each row by how "relevant" it is, and it will automatically exclude common words that appear in 50% or more of the records.http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Link to comment
Share on other sites

those array words are still being echoed, when i want them ignored (in my newbie way of thinking !==$common):

$where = '';$common = array(	'to',	'of',  'the',  'this',  'that',  'and');foreach ($get_search as $word){  if (strlen($word) < 3)	continue;  if (in_array($word, $common))	continue;  if ($where != '')	$where .= ' OR ';  $where .= "terms LIKE '%" . mysql_real_escape_string($word) . "%'";}

thanks for link to mysql full text. will read that next. looks very cool. guessing string array above is going to work better than str_replace in this instance?

Link to comment
Share on other sites

that's perfect, except when just searching on array words, this that and etc., getting

error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
not that anyone will just search on those words but would like to echo the following (appears after echo $row). it worked before including strtolower function:
$anymatches=mysql_num_rows($result); if ($anymatches == 0) { echo "Please clarify.<br><br> ";

thanks for all the help jsg. you've given me a lot to go on for a while!

Link to comment
Share on other sites

ok i put this after echo code; fixes array word error problem:

if ($where == "") { echo "Please offer more description, {$post_name}."; exit; }

the apostrophes are still giving me trouble, getting this output and error message:

 you\'re i\'m they\'re  //echo messageYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 're i'm they're', NOW())' at line 1//NOW is date/time of user form submit; that works fine

and the (jsg's) code:

function form_var($message, $default = ''){  $retval = $default;  if (isset($_POST[$message]))	$retval = $_POST[$message];  elseif (isset($_GET[$message]))	$retval = $_GET[$message];  if (is_array($retval))  {	foreach ($retval as $k => $v)	{	  $retval[$k] = trim($v);	  if (get_magic_quotes_gpc())		$retval[$k] = stripslashes($v);	}  }  else  {	$retval = trim($retval);	if (get_magic_quotes_gpc())	  $retval = stripslashes($retval);  }  return $retval;}$message = form_var('message');

thanks for help. apostrophes are used often so i've got to get this one.

Link to comment
Share on other sites

It's hard to tell what the problem is from code snippets, run some tests and see if you can determine where the problem is. Create a form that just submits a text field, and use form_var on the submitted data and just print it out, there should not be any quotes. If there are, print the value directly from $_POST and see what's there, and also print the output of get_magic_quotes_gpc() to see if it's enabled or not. According to the SQL error message, the quotes do not have slashes before them.

Link to comment
Share on other sites

i'm trying to get rid of the slashes not the quotes in case that wasn't clear...anyhow i'll try your suggestions and post here. also please (below) are the array elements in the " "? what's purpose $default= single quotes?:

$get_search=explode(" ",$pre_filter);function form_var($message, $default = '')

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...