Searching for complete words

[dstonek]

I have a field containing keywords.

Some rows content for this field are something like "dog cat animal horse farm countryside".
At this point I am looking for a MySQL query to get those rows that exactly contain this keyword-field "animal"
If he serches for "anim" no results will be given.
In other words: "select rows where table.keywords contain '$k' as a part of words separated by spaces" ($k= anim ->no results) ($k= animal ->some results)
Thank you

[Pinkmischief]

Hi are you submitting via a submit button or something?

[Pinkmischief]

I think this is what you are trying to achieve hope it helps
i,m still fairly new to php but will try to help u

//If button named submit is pressed
if(isset($_POST['Submit'])){
//make keyword safe input
$keyword = trim(mysql_real_escape_string($_POST['keyword']));
	//Query Table 
	$sql = "select field from table where keyword = '".$keyword."'";
	//perform query
	$query = mysql_query($sql) or die (mysql_error()."".$sql);
	//loop through each instance of animal or whatever was type in keyword field.
	while ($result = mysql_fetch_array($query)){
		echo "<br>".$result[0]."";
	}
}

Regards Pinky

[dstonek]

via query string $_GET[].
Let me see your second post. I posted this first line at the same time as yours.
Thanks

[Pinkmischief]

Ah ok sorry i misunderstood im fairly new but are you trying to take from one page to a new one?

[Pinkmischief]

the code i posted will work for a submit button next to a input field and then if you name it as a function and place it in your page it should work for you as a search facility.

[dstonek]

Pinky,
this is the core of what I am looking for.

$sql = "select field from table where keyword = '".$keyword."'"; 

I get:
You have an error in your SQL syntax near 'FROM fotos WHERE keyword_es = 'trelew'' at line 1

[bradgrafelman]

Looks like you didn't copy his code correctly... you have two different types of quotes in your SQL query.

If you're trying to search for a keyword anywhere within a column, however, this code won't work anyway.

EDIT2: Er... apparently MySQL doesn't have the "\b" word boundary character... so try this instead:

$query = 'SELECT field FROM table WHERE keyword REGEXP \'[^0-9a-z]' . $keyword . '[^0-9a-z]\' OR keyword REGEXP \'[^0-9a-z]' . $keyword . '$\' OR a REGEXP \'^' . $keyword . "[^0-9a-z]' OR a = '$keyword'";

[dstonek]

Yes, mysql line from Pinky would need a LIKE and more else.
You have an error in your SQL syntax near 'REGEXP '\baljibe\b'' at line 1
is what I get when

$keyword="aljibe";
$query_busq2_es = "SELECT ordinal, tercera_clasif, foto, fotow, texto_foto, keyword_es FROM fotos WHERE REGEXP '\b$keyword\b'" ; 

[bradgrafelman]

Ah, you must have read my post before I changed it. Recopy my example query from above and fix the field names and whatnot - I discovered that MySQL's REGEXP doesn't support the word boundary sequence.

[dstonek]

Thanks Brad,
making some changes it works fine.

$query_busq2_es = 'SELECT ordinal, tercera_clasif, foto, fotow, texto_foto, keyword_es FROM fotos WHERE keyword_es REGEXP \'[^0-9a-z]' . $keyword . '[^0-9a-z]\' OR keyword_es REGEXP \'[^0-9a-z]' . $keyword . '$\' OR keyword_es REGEXP \'^' . $keyword . "[^0-9a-z]' OR keyword_es = '$keyword'"; 

I have this more simplified query that works well too.

$query_busq2_es = "SELECT ordinal, tercera_clasif, foto, fotow, texto_foto, keyword_es FROM fotos WHERE concat(' ', keyword_es, ' ') LIKE '% $keyword %'" ; 

Thanks again for you help