CREATE mysql function

Im trying to create a function for mysql so i can use the following query

[COLOR=#000000][FONT=Consolas]mysql_qery[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"SELECT `term` FROM `words` WHERE levenshtein('$word', `term`) BETWEEN 0 AND 4"[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]);
[/FONT][/COLOR]

Code (markup):

How can i create this function

[COLOR=#000000][FONT=Consolas]DELIMITER [/FONT][/COLOR][COLOR=#000000][FONT=Consolas]$$[/FONT][/COLOR]
[COLOR=#00008B]CREATE[/COLOR] [COLOR=#00008B]FUNCTION[/COLOR] LEVENSHTEIN( s1 CHAR([COLOR=#800000]255[/COLOR]), s2 CHAR([COLOR=#800000]255[/COLOR])) RETURNS int([COLOR=#800000]3[/COLOR]) DETERMINISTIC[COLOR=#00008B]BEGIN[/COLOR]    [COLOR=#00008B]DECLARE[/COLOR] s1_len, s2_len, i, j, c, c_temp, cost INT;    [COLOR=#00008B]DECLARE[/COLOR] s1_char CHAR([COLOR=#800000]255[/COLOR]);    [COLOR=#00008B]DECLARE[/COLOR] cv0, cv1 CHAR([COLOR=#800000]255[/COLOR]);    [COLOR=#00008B]SET[/COLOR] s1_len = LENGTH(s1);    [COLOR=#00008B]SET[/COLOR] s2_len = LENGTH(s2);    [COLOR=#00008B]SET[/COLOR] cv1 = [COLOR=#800000]0x00[/COLOR];    [COLOR=#00008B]SET[/COLOR] j = [COLOR=#800000]1[/COLOR];    [COLOR=#00008B]SET[/COLOR] i = [COLOR=#800000]1[/COLOR];    [COLOR=#00008B]SET[/COLOR] c = [COLOR=#800000]0[/COLOR];    [COLOR=#00008B]IF[/COLOR] s1 = s2 [COLOR=#00008B]THEN[/COLOR]        [COLOR=#00008B]RETURN[/COLOR] [COLOR=#800000]0[/COLOR];    [COLOR=#00008B]ELSE[/COLOR] [COLOR=#00008B]IF[/COLOR] s1_len = [COLOR=#800000]0[/COLOR] [COLOR=#00008B]THEN[/COLOR]        [COLOR=#00008B]RETURN[/COLOR] s2_len;    [COLOR=#00008B]ELSE[/COLOR] [COLOR=#00008B]IF[/COLOR] s2_len = [COLOR=#800000]0[/COLOR] [COLOR=#00008B]THEN[/COLOR]        [COLOR=#00008B]RETURN[/COLOR] s1_len;    [COLOR=#00008B]ELSE[/COLOR]        [COLOR=#00008B]WHILE[/COLOR] j <= s2_len DO           [COLOR=#00008B]SET[/COLOR] c = c + [COLOR=#800000]1[/COLOR];           [COLOR=#00008B]IF[/COLOR] s1_char = SUBSTRING(s2, j, [COLOR=#800000]1[/COLOR]) [COLOR=#00008B]THEN[/COLOR]              [COLOR=#00008B]SET[/COLOR] cost = [COLOR=#800000]0[/COLOR]; [COLOR=#00008B]ELSE[/COLOR] [COLOR=#00008B]SET[/COLOR] cost = [COLOR=#800000]1[/COLOR];           [COLOR=#00008B]END[/COLOR] [COLOR=#00008B]IF[/COLOR];           [COLOR=#00008B]SET[/COLOR] c_temp = CONV(HEX(SUBSTRING(cv1, j, [COLOR=#800000]1[/COLOR])), [COLOR=#800000]16[/COLOR], [COLOR=#800000]10[/COLOR]) + cost;                 [COLOR=#00008B]SET[/COLOR] c_temp = CONV(HEX(SUBSTRING(cv1, j[COLOR=#800000]+1[/COLOR], [COLOR=#800000]1[/COLOR])), [COLOR=#800000]16[/COLOR], [COLOR=#800000]10[/COLOR]) + [COLOR=#800000]1[/COLOR];                [COLOR=#00008B]IF[/COLOR] c > c_temp [COLOR=#00008B]THEN[/COLOR]                    [COLOR=#00008B]SET[/COLOR] c = c_temp;                [COLOR=#00008B]END[/COLOR] [COLOR=#00008B]IF[/COLOR];                [COLOR=#00008B]SET[/COLOR] cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + [COLOR=#800000]1[/COLOR];            [COLOR=#00008B]END[/COLOR] [COLOR=#00008B]WHILE[/COLOR];            [COLOR=#00008B]SET[/COLOR] cv1 = cv0, i = i + [COLOR=#800000]1[/COLOR];        [COLOR=#00008B]END[/COLOR] [COLOR=#00008B]WHILE[/COLOR];    [COLOR=#00008B]END[/COLOR] [COLOR=#00008B]IF[/COLOR];    [COLOR=#00008B]RETURN[/COLOR] c;[COLOR=#00008B]END[/COLOR]$$ [COLOR=#000000][FONT=Consolas]DELIMITER [/FONT][/COLOR][COLOR=#000000][FONT=Consolas];[/FONT][/COLOR]

Code (markup):

 

Read http://stackoverflow.com/questions/...f-levenshtein-distance-for-mysql-fuzzy-search It's not as simple as someone giving you a function you can plug in.

 

I do have this but the main problem is its selecting every row

	

$search = $_GET['q'];

$distance = -1;	$res_also = mysql_query("SELECT `m_title` FROM `db`");
	$num_also = mysql_num_rows($res_also);
	
	    if($num_also > 0){
		    while($row_also = mysql_fetch_assoc($res_also)){
						    
			 $lev = levenshtein($search, $row_also['m_title']);
					 
					 // exact match
					 if($lev == 0){
					  
					$closest = $row_also['m_title'];		  
					  $distance = 0;
					  // no need to continue as we have found exact match
					  break;
					 }
					 
					 // if distance is less than the currently stored distance and it is less than our initial value
					 if($lev <= $distance || $distance < 0){
	
				$closest = $row_also['m_title'];
				  $distance = $lev;
					 }
	 		}
			if($distance > 0){
			
			if(!$_GET['auto_sug']){
			$closest_enc = urlencode($closest);
			$_SESSION['auto_sug'] = $search;
				if(!$format || $format == 'all'){
				redirect("/sa?search={$closest_enc}");
				}
			}
		
	   	echo $closest;
     	}//end while
     	}//end num
		}//end search and num

Code (markup):

 

while($row_also = mysql_fetch_assoc($res_also)){}

PHP:

fetches one row at a time. All the code between { and } operates on each row.

 

Problem is , its looping through nearly every row in the database of over 3Million rows. There must be a faster way

 

If you want to check every record in the database, there isn't. If you want to check only certain records, use a WHERE clause using an indexed field.