我正试图从数据库中提取一个单词列表,以$word1.$word2.$word3的形式创建一个唯一的三个单词组合,并将其分配给一个星形。
我想避免重复的组合——我希望每颗恒星都有一个唯一的三个单词的标识符。
我目前的方法包括创建一个包含所有可能的三个单词组合的数组,然后在将每个组合分配给一颗星后从数组中删除。然而,我打算在单词列表中使用几千个单词,这意味着这个数组将包含数百亿个组合,所以这种方法看起来效率非常低。
如何更有效地实现这一点?我最初的想法是,我应该循环浏览每一颗星,创建并分配一个三个单词的组合,然后将该组合添加到数组中,对于每颗星,检查新生成的组合是否在数组中。
代码
<?php
// Initiate connection to the database...
$db = mysqli_connect('localhost', 'root', '', 'stellar');
// Query database of words
$words_sql = "SELECT * FROM words";
$words_res = mysqli_query($db, $words_sql)or die(mysqli_error());
// Create array of words
$words = array();
// Loop through each word from the database and add each to an array
while($row = mysqli_fetch_array($words_res)){
$words[] = $row['word'];
}
// Create array of all possible three-word combinations, from which we will randomly select our combinations
$triplets = array();
foreach ($words as $word1){
foreach ($words as $word2){
foreach($words as $word3){
if ($word1 !== $word2 && $word2 !== $word3 && $word1 !== $word3){
$triplets[] = "$word1.$word2.$word3";
}
}
}
}
// Pull all stars from database
$stars_sql = "SELECT * FROM stars";
$stars_res = mysqli_query($db, $stars_sql)or die(mysqli_error());
// Loop through every star in the array
while($row = mysqli_fetch_array($stars_res)){
// Store the star name and star_id in variables
$star = $row['star_name'];
$star_id = $row['star_id'];
// Set $three_words as a random combination from the array of possible combinations...
$ran_num = array_rand($triplets);
$three_words = $triplets[$ran_num];
// ...and remove this particular combination, in order to prevent repating combinations
array_splice($triplets, $ran_num, 1);
// Attach the random 3-word combination to the star
echo $star.' '.$three_words.'<br/><br/>';
}
?>
您可以(可能)做一个小调整,让MySQL为您做一些繁重的工作。
$words_sql = "SELECT CONCAT(w1.word,'.',w2.word,'.',w3.word) as triplet
FROM (words w1 JOIN words w2 ON w1.word != w2.word)
JOIN words w3 ON w3.word != w1.word AND w3.word != w2.word";
$words_res = mysqli_query($db, $words_sql)or die(mysqli_error());
// Create array of words
$words = array();
// Loop through each word from the database and add each to an array
while($row = mysqli_fetch_array($words_res)){
$triplets[] = $row['triplet'];
}
这可能是你将要得到的最好的结果,因为在这个过程结束时,你将把所有的三重态都分配给一颗恒星,这意味着无论你是预先生成三重态还是稍后生成,你最终都会生成所有的。
现在,对于三重态的数量远大于你需要命名的恒星数量的情况,有一种替代解决方案:假设你有250万颗恒星,但有2000个单词(或80亿个三重态)。在这种情况下,恒星只是你可能的三重态的一小部分,所以你可以做以下事情:
$words = array();
// Loop through each word from the database and add each to an array
while($row = mysqli_fetch_array($words_res)){
$words[] = $row['word'];
}
// Pull all stars from database
$stars_sql = "SELECT * FROM stars";
$stars_res = mysqli_query($db, $stars_sql)or die(mysqli_error());
// Loop through every star in the array
$used = [];
while($row = mysqli_fetch_array($stars_res)){
// Store the star name and star_id in variables
$star = $row['star_name'];
$star_id = $row['star_id'];
do {
//Generate non-repeating triplet of words (sample without replacement?)
$word1 = array_rand($words);
do {
$word2 = array_rand($words);
} while ($word2 == $word1);
do {
$word3 = array_rand($words);
} while ($word3 == $word2 || $word1 == $word3);
$triplet = $words[$word1].".".$words[$word2].".".$words[$word3];
} while (isset($used[$triplet])); //Try again if we've already used it. Very unlikely.
$used[$triplet] = true; //Keep track of what we've used.
echo $star.' '.$triplet.'<br/><br/>';
}
在第二种情况下,这是有效的,因为我们两次生成同一个三元组的机会非常小,因为可能有三元组的数量,而且我们总共只使用了其中的一小部分。