2010-12-04 18:17:31
drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
)
engine=innodb;
 
insert into users (username) values ('f00'),('bar'),('foobar'),('alpha'),('felix');
 
drop table if exists user_hits;
create table user_hits
(
hit_id int unsigned not null auto_increment primary key,
hit_date datetime not null,
user_id int unsigned not null,
key hit_date_user_idx(hit_date, user_id)
)
engine=innodb;
 
drop procedure if exists user_search;
 
delimiter #
 
create procedure user_search
(
in p_username varbinary(32)
)
begin
 
  set p_username = trim(replace(p_username, '%',''));
 
  create temporary table matches engine=memory 
    select * from users where username like concat(p_username,'%');
 
  insert into user_hits (hit_date, user_id) select now(), user_id from matches;
 
  select * from matches order by username;
 
  drop temporary table if exists matches;
 
end #
 
delimiter ;
 
drop procedure if exists user_hits;
 
delimiter #
 
create procedure user_hits
(
in p_user_id int unsigned,
in p_day_interval smallint unsigned
)
begin
 
 select 
  user_id, 
  hit_date, 
  count(*) as hits 
 from 
  user_hits
 where
  hit_date between now() - interval p_day_interval day and now() and 
  user_id = p_user_id
 group by
  hit_date, user_id;
 
end #
 
delimiter ;
 
-- testing
 
select * from users;
select * from user_hits;
 
call user_search('f00');
 
select * from user_hits;
 
call user_search('f');
 
select * from user_hits;
 
call user_hits(1,7);
 
Invalid Email or Password