pBhXnqDT

1drop procedure if exists search_companies;
2
3delimiter #
4
5create procedure search_companies
6(
7in p_state tinyint unsigned,
8in p_name varchar(64)
9)
10
11begin
12
13create temporary table search
14(
15id int unsigned not null,
16name varchar(64) not null,
17rank float default 0.0,
18fulltext(name)
19)engine=myisam;
20
21-- ok populate your temp table with all the matches from your big query
22-- i'm just faking something here ok !!
23insert into search
24select distinct c.id, c.name, 0.0 from companies c where name like 'e%';
25
26-- now we want to rank them
27
28-- to do !!
29
30-- now get the data out
31
32select * from search order by rank;
33
34drop table search;
35
36end #
37
38delimiter ;