mysql - GROUP_CONCAT but with limits to get more than one row -
i developing small jumbled-words game users on ptokax dc hub manage. this, i'm storing list of words inside mysql table. table schema follows:
create table `jumblewords` ( `id` int(10) unsigned not null auto_increment, `word` char(15) not null, primary key (`id`), unique index `word` (`word`) ) comment='list of words used jumble game.' collate='utf8_general_ci' engine=myisam;
now, in game-engine; want fetch 20 words string randomly. can achieve query similar this:
select group_concat(f.word separator ', ' ) ( select j.word word jumblewords j order rand() limit 20) f
but have execute statement everytime list expires(all 20 words have been put before user).
can modify query can fetch more 1 row results generated query have above?
probably easier way solve problem storing random words in temporary table , later extract values. stored procedure perfect that.
delimiter // drop procedure if exists sp_jumblewords // create procedure sp_jumblewords(no_lines int) begin drop table if exists tmp_jumblewords; create temporary table tmp_jumblewords ( `word` varchar(340) not null); repeat insert tmp_jumblewords select group_concat(f.word separator ', ' ) ( select j.word word jumblewords j order rand() limit 20) f; set no_lines = no_lines - 1; until no_lines = 0 end repeat; select * tmp_jumblewords; end // delimiter ; call sp_jumblewords(20);
Comments
Post a Comment