我正在使用PHP和MySQL构建在线词汇表。
搜索页面由两个下拉列表和一个搜索字段组成，用户可以在其中输入所需的字词。
我创建了八个表：
四种语言及其变体形式（例如，美国和英国代表英语）
每种语言的定义（英语定义，法语定义等）为四个。
我停留在用户输入术语以获得所需翻译和相应定义的位置。
如何建立与目标语言匹配的SQL查询，并根据下拉列表中的选择提供定义？
这是我到目前为止编写的代码。
HTML表单-查询文件
<!-- Create Form -->
<!-- Send data with the 'post' method to the file called 'retrieve.php' -->
<form action="retrieve.php" method="post">
<!-- Choose source language -->
<!-- Flex Container, 1st div -->
<div class="flex-container">
<!-- Flex Container, 1st div -->
<div>
<p class="instruction-frame" id="srclanguage">Source Language<br />
<!-- Indication for single selection -->
<span class="smallfont">Select a single language</span></p>
<!-- Adjust the width of the select list-->
<select class="my_dropdown" name="source" size="5">
<option cvalue="">-- Select --</option>
<option value="Terms_de">German</option>
<option value="Terms_us">English - US</option>
<option value="Terms_uk">English - UK</option>
<option value="Terms_fr">French</option>
<option value="Terms_ardz">Arabic - Algeria</option>
<option value="Terms_arbh">Arabic - Bahrain</option>
<option value="Terms_artd">Arabic - Chad</option>
<option value="Terms_arkm">Arabic - Comoros</option>
<option value="Terms_ardj">Arabic - Djibouti</option>
<option value="Terms_areg">Arabic - Egypt</option>
<option value="Terms_arer">Arabic - Eritrea</option>
<option value="Terms_ariq">Arabic - Iraq</option>
<option value="Terms_aril">Arabic - Israel</option>
<option value="Terms_arjo">Arabic - Jordan</option>
<option value="Terms_arkw">Arabic - Kuwait</option>
<option value="Terms_arlb">Arabic - Lebanon</option>
<option value="Terms_arly">Arabic - Libya</option>
<option value="Terms_armt">Arabic - Malta</option>
<option value="Terms_armr">Arabic - Mauritania</option>
<option value="Terms_arma">Arabic - Morocco</option>
<option value="Terms_arom">Arabic - Oman</option>
<option value="Terms_arps">Arabic - Palestine</option>
<option value="Terms_arqa">Arabic - Qatar</option>
<option value="Terms_arsa">Arabic - Saudi Arabia</option>
<option value="Terms_arso">Arabic - Somalia</option>
<option value="Terms_arsd">Arabic - Sudan</option>
<option value="Terms_arsy">Arabic - Syria</option>
<option value="Terms_artz">Arabic - Tanzania</option>
<option value="Terms_artn">Arabic - Tunisia</option>
<option value="Terms_arae">Arabic - UAE</option>
<option value="Terms_arye">Arabic - Yemen</option>
</select>
</div>
<!-- Flex Container, 2nd div -->
<div>
<p class="instruction-frame" id="trgtlanguage">Target Languages<br />
<!-- Indication for multiple selection -->
<span class="smallfont">Hold Ctrl for multiple selection</span></p>
<!-- Allowing multiple selection -->
<select class="my_dropdown" name="target" size="5" multiple="multiple">
<option value="">-- Select --</option>
<option value="Terms_de">German</option>
<option value="Terms_us">English - US</option>
<option value="Terms_uk">English - UK</option>
<option value="Terms_fr">French</option>
<option value="Terms_ardz">Arabic - Algeria</option>
<option value="Terms_arbh">Arabic - Bahrain</option>
<option value="Terms_artd">Arabic - Chad</option>
<option value="Terms_arkm">Arabic - Comoros</option>
<option value="Terms_ardj">Arabic - Djibouti</option>
<option value="Terms_areg">Arabic - Egypt</option>
<option value="Terms_arer">Arabic - Eritrea</option>
<option value="Terms_ariq">Arabic - Iraq</option>
<option value="Terms_aril">Arabic - Israel</option>
<option value="Terms_arjo">Arabic - Jordan</option>
<option value="Terms_arkw">Arabic - Kuwait</option>
<option value="Terms_arlb">Arabic - Lebanon</option>
<option value="Terms_arly">Arabic - Libya</option>
<option value="Terms_armt">Arabic - Malta</option>
<option value="Terms_armr">Arabic - Mauritania</option>
<option value="Terms_arma">Arabic - Morocco</option>
<option value="Terms_arom">Arabic - Oman</option>
<option value="Terms_arps">Arabic - Palestine</option>
<option value="Terms_arqa">Arabic - Qatar</option>
<option value="Terms_arsa">Arabic - Saudi Arabia</option>
<option value="Terms_arso">Arabic - Somalia</option>
<option value="Terms_arsd">Arabic - Sudan</option>
<option value="Terms_arsy">Arabic - Syria</option>
<option value="Terms_artz">Arabic - Tanzania</option>
<option value="Terms_artn">Arabic - Tunisia</option>
<option value="Terms_arae">Arabic - UAE</option>
<option value="Terms_arye">Arabic - Yemen</option>
</select>
</div>
<!-- Position the submit button at the bottom -->
<div class="button">
<div class="row" id="button">
<div class="col-lg-12">
<div class="input-group">
<input type="text" class="form-control" name="search" placeholder="Enter your term..." aria-label="Enter your term here">
<span class="input-group-btn">
<!-- Choose different color for the button through 'btn-primary' -->
<!-- type 'submit' to call the file 'retrieve.php' once the user clicks the button -->
<button class="btn btn-primary" type="submit">Search</button>
</span>
</div>
</div>
</div>
</div>
</div> PHP-检索文件
<?php
//Connect to the MySQL server with mysqli
require_once 'login.php';
$conn = new mysqli($hostname, $username, $password, $database);
if ($conn->connect_error)
{
echo "Connection to database failed. Please retry later.";
exit;
}
//Reduce length of variable names
//Check if mandatory fields have been filled in
if (isset($_POST['source'])) {
$source=$_POST['source'];
}
else {
echo"<p class=\"warning\">"."A mandatory field is still empty. Please retry again."."<br />"."<br />"."<a href=\"query.php\">"."<img src=\"stock-home.png\" alt=\"Home\" />"."</a>"."</p>";
exit;
}
if (isset($_POST['target'])) {
$target=$_POST['target'];
}
else {
echo"<p class=\"warning\">"."A mandatory field is still empty. Please retry again."."<br />"."<br />"."<a href=\"query.php\">"."<img src=\"stock-home.png\" alt=\"Home\" />"."</a>"."</p>";
exit;
}
if (isset($_POST['search'])) {
$search=$_POST['search'];
}
else {
echo"<p class=\"warning\">"."A mandatory field is still empty. Please retry again."."<br />"."<br />"."<a href=\"query.php\">"."<img src=\"stock-home.png\" alt=\"Home\" />"."</a>"."</p>";
exit;
}
if (!$search || !$source || !$target)
{
echo"<p class=\"warning\">"."A mandatory field is still empty. Please retry again."."<br />"."<br />"."<a href=\"query.php\">"."<img src=\"stock-home.png\" alt=\"Home\" />"."</a>"."</p>";
exit;
}
//Remove whitespace from beginning and end of the string
$search=trim($search);
//Escaping control characters
if (!get_magic_quotes_gpc())
{
$source = addslashes($source);
$target = addslashes($target);
$search = addslashes($search);
}
//Test
$query = "SELECT * FROM legal_glossary.determs WHERE ".$source." LIKE '%".$search."%'";
$result = $conn->query($query);
$num_results = $result->num_rows;
echo '<p>Number of entries found: '.$num_results.'</p>';
?>
SQL定义文件
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ardef` (
`ardef_id` int(11) NOT NULL AUTO_INCREMENT,
`ardef` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`Terms_id` int(11) DEFAULT NULL,
PRIMARY KEY (`ardef_id`),
KEY `fk_arabic-definitions_idx` (`Terms_id`),
KEY `ardefix` (`ardef`),
CONSTRAINT `fk_arabic-definitions` FOREIGN KEY (`Terms_id`) REFERENCES `determs` (`Terms_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ardef`
--
CREATE TABLE `arterms` (
`arterms_id` int(11) NOT NULL AUTO_INCREMENT,
`Terms_arma` varchar(128) DEFAULT NULL,
`Terms_ardz` varchar(128) DEFAULT NULL,
`Terms_arbh` varchar(128) DEFAULT NULL,
`Terms_artd` varchar(128) DEFAULT NULL,
`Terms_arkm` varchar(128) DEFAULT NULL,
`Terms_ardj` varchar(128) DEFAULT NULL,
`Terms_areg` varchar(128) DEFAULT NULL,
`Terms_arer` varchar(128) DEFAULT NULL,
`Terms_ariq` varchar(128) DEFAULT NULL,
`Terms_aril` varchar(128) DEFAULT NULL,
`Terms_arjo` varchar(128) DEFAULT NULL,
`Terms_arkw` varchar(128) DEFAULT NULL,
`Terms_arlb` varchar(128) DEFAULT NULL,
`Terms_arly` varchar(128) DEFAULT NULL,
`Terms_armt` varchar(128) DEFAULT NULL,
`Terms_armr` varchar(128) DEFAULT NULL,
`Terms_arom` varchar(128) DEFAULT NULL,
`Terms_arps` varchar(128) DEFAULT NULL,
`Terms_arqa` varchar(128) DEFAULT NULL,
`Terms_arsa` varchar(128) DEFAULT NULL,
`Terms_arso` varchar(128) DEFAULT NULL,
`Terms_arsd` varchar(128) DEFAULT NULL,
`Terms_arsy` varchar(128) DEFAULT NULL,
`Terms_artz` varchar(128) DEFAULT NULL,
`Terms_artn` varchar(128) DEFAULT NULL,
`Terms_arae` varchar(128) DEFAULT NULL,
`Terms_arye` varchar(128) DEFAULT NULL,
`Terms_id` int(11) DEFAULT NULL,
PRIMARY KEY (`arterms_id`),
KEY `fk_arterms_idx` (`Terms_id`),
CONSTRAINT `fk_arterms` FOREIGN KEY (`Terms_id`) REFERENCES `determs` (`Terms_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `arterms`
--
CREATE TABLE `dedef` (
`dedef_id` int(11) NOT NULL AUTO_INCREMENT,
`dedef` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`Terms_id` int(11) DEFAULT NULL,
PRIMARY KEY (`dedef_id`),
KEY `fk_german_definitions_1_idx` (`Terms_id`),
KEY `dedefix` (`dedef`),
CONSTRAINT `fk_german_definitions_1` FOREIGN KEY (`Terms_id`) REFERENCES `determs` (`Terms_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `dedef`
--
CREATE TABLE `determs` (
`Terms_id` int(11) NOT NULL AUTO_INCREMENT,
`Terms_de` varchar(128) DEFAULT NULL,
`Terms_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Time when record was updated.',
`Terms_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when record was created.',
PRIMARY KEY (`Terms_id`),
KEY `determ` (`Terms_de`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `endef` (
`endef_id` int(11) NOT NULL AUTO_INCREMENT,
`endef` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`Terms_id` int(11) DEFAULT NULL,
PRIMARY KEY (`endef_id`),
KEY `fk_english-definitions_idx` (`Terms_id`),
KEY `endefix` (`endef`),
CONSTRAINT `fk_english-definitions` FOREIGN KEY (`Terms_id`) REFERENCES `determs` (`Terms_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `enterms` (
`enterms_id` int(11) NOT NULL AUTO_INCREMENT,
`Terms_uk` varchar(128) DEFAULT NULL,
`Terms_us` varchar(128) DEFAULT NULL,
`Terms_id` int(11) DEFAULT NULL,
PRIMARY KEY (`enterms_id`),
KEY `fk_enterms_1_idx` (`Terms_id`),
KEY `ukterm` (`Terms_uk`),
KEY `usterm` (`Terms_us`),
CONSTRAINT `fk_enterms_1` FOREIGN KEY (`Terms_id`) REFERENCES `determs` (`Terms_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `frdef` (
`frdef_id` int(11) NOT NULL AUTO_INCREMENT,
`frdef` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
`Terms_id` int(11) DEFAULT NULL,
PRIMARY KEY (`frdef_id`),
KEY `fk_french-definitions_idx` (`Terms_id`),
KEY `frdefix` (`frdef`),
CONSTRAINT `fk_french-definitions` FOREIGN KEY (`Terms_id`) REFERENCES `determs` (`Terms_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `frterms` (
`frterms_id` int(11) NOT NULL AUTO_INCREMENT,
`Terms_fr` varchar(128) DEFAULT NULL,
`Terms_id` int(11) DEFAULT NULL,
PRIMARY KEY (`frterms_id`),
KEY `fk_frterms_idx` (`Terms_id`),
KEY `frterm` (`Terms_fr`),
CONSTRAINT `fk_frterms` FOREIGN KEY (`Terms_id`) REFERENCES `determs` (`Terms_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `frterms`
--
LOCK TABLES `frterms` WRITE;
/*!40000 ALTER TABLE `frterms` DISABLE KEYS */;
INSERT INTO `frterms` VALUES (1,'Contrat de mariage',1),(3,'Extrait du casier judiciaire',4),(4,'Registre des marriages',2);
/*!40000 ALTER TABLE `frterms` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2017-12-20 16:50:28
最佳答案
如建议的那样，规范化数据库模式以使用一个带有指示器的长表而不是结构相似的不同名称的表来扩展。例如，Perfect的以下译本将需要与其他所有可能的配对进行交叉匹配：
翻译术语
TermID Langauge Variant Term
1 English US Perfect
2 French France Parfait
3 German Germany Perfekt
4 Spanish Spain Perfecto
5 Swahili Kenya Inafaa
6 Arabic Kuwait في احسن الاحوال
...
翻译比赛
MatchID SourceTermID TargetTermID
1 2 1
2 3 1
3 4 1
4 5 1
5 6 1
6 1 2
7 3 2
8 4 2
9 5 2
10 6 2
11 1 3
12 2 3
13 4 3
14 5 3
15 6 3
16 1 4
17 2 4
18 3 4
19 5 4
20 6 4
21 1 5
22 2 5
23 3 5
24 4 5
25 6 5
26 1 6
27 2 6
28 3 6
29 4 6
30 5 6
然后在PHP中使用self-join术语表查询，它们都连接到匹配表
$source = addslashes($source);
$target = addslashes($target);
$search = addslashes($search);
// LIST OF ALL TARGET TERMS
$query = "SELECT trgt.Term
FROM TranslationMatches m
INNER JOIN TranslationTerms src
ON m.SourceTermID = src.TermID AND src.Language = ?
INNER JOIN TranslationTerms trgt
ON m.TargetTermID = trgt.TermID AND trgt.Language = ?
WHERE src.Term LIKE ?;"
$stmt = $conn->prepare($query);
$stmt->bind_param("sss", $source, $target, "%".$search."%")
$stmt->execute($query);
$stmt->store_result();
$num_results = $stmt->num_rows;
echo '<p>Number of entries found: '.$num_results.'</p>';