从MySQL数据库中随机组合两个单词

Question

I have a database with nouns and adjectives for example:

id | type    | word  
-----------------------
1  | noun    | apple
2  | noun    | ball
3  | adj     | clammy
4  | noun    | keyboard
5  | adj     | bloody

ect...

I want to create one query what will grab 10 random adjectives and 10 random nouns and put them together.

Having trouble doing it, is this possible?

Thank you!

Answer 1

You can get 10 random elements per type with queries like this:

select word from YOUR_TABLE where type = 'noun' order by rand() limit 10;
select word from YOUR_TABLE where type = 'adj' order by rand() limit 10;

and then put them together in your PHP code, like so:

$phrases = array();

$adj_result  = mysql_query("SELECT word FROM words WHERE type = 'adj' ORDER BY RAND() LIMIT 10");
$noun_result = mysql_query("SELECT word FROM words WHERE type = 'noun' ORDER BY RAND() LIMIT 10");

while($adj_row = mysql_fetch_assoc($adj_result)) {
  $noun_row = mysql_fetch_assoc($noun_result);
  $phrases[$adj_row['word']] = $noun_row['word'];
}

print_r($phrases);

Please note that this code is not very safe (it makes the assumption that the second query always yields as least as many results as the first), but you get the idea.

Edit: Here's a single SQL query that should do it:

select t1.word, t2.word 
from 
  ((select word from YOURTABLE where type = 'adj' order by rand()) as t1), 
  ((select word from YOURTABLE where type = 'noun' order by rand()) as t2) 
order by rand()
limit 10;

EDIT: removed example