Search in multiple tables using MySQL and PHP

Question

I am building an online glossary with PHP and MySQL. The search page consists of two dropdown-lists and a search field where the user inputs the desired term.

I have created eight tables:

four for the languages with their variants (e.g. US and UK for English)
four for the definitions for each language (English definitions, French definitions, etc.).

I am stuck at the point where the user inputs the term to get the desired translation and the corresponding definition. How I can I build an SQL query that matches the target language and provides a definition depending on the selection in the dropdown-list? Here's the code that I have written so far.

HTML form - Query File

     <!-- 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 - Retrieve File

<?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 Definition file

/*!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

Please explain more the objective of your form: a glossary or a translation form? Enter what to get what? Please also provide some data and a working example of desired result per a specified search. — Parfait, Dec 26 '17 at 21:07
Thank you for the quick feedback. The form allows the user to select the source and the target language. The user selects the language pair, then inserts a term in the search field and gets the translation of his term. You enter the term "Perfect", select English as source and French as target from the drop-down lists, and the result should show the following: Source: Perfect Target: Parfait. — N_user, Dec 26 '17 at 21:18
Possible duplicate of [SELECT \* FROM multiple tables. MySQL](https://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql) — Wiguna R, Dec 26 '17 at 21:18
"I have created eight tables" - why? it should just be one, even if you are relying on different collations. — symcbean, Dec 26 '17 at 21:22
@symcbean: I thought that using more tables would provide more flexibility, but thinking about it right now, I realize that it may not be necessary as you suggest. — N_user, Dec 26 '17 at 21:53

score 0 · Accepted Answer · answered Dec 26 '17 at 22:26

As advised, normalize your database schema to scale with one long table with indicators instead of similarly structured tables of different names. For instance the below translations of Perfect will need to cross-match with every other possible pairing:

TranslationTerms

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    في احسن الاحوال
...

TranslationMatches

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

Then in PHP use a self-join query of terms table that both connect to matching table

$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>';

Thank you very much for your help. I will check your suggestions and get back to you if I have any doubts. — N_user, Dec 27 '17 at 00:17

score 0 · Answer 2 · answered Dec 27 '17 at 01:52

In addition to Parfait answer, you may want to add extra column "match_percentage" to the TranslationMatches table so that one term can have multiple matches and each match have matching strength or percentage because words in languages rarely have 100% match and have context dependant translation. This way for each term you show the user multiple words that may be the correct translation ordered by match_percentage column. I hope this helps.

Welcome, I hope it's helpful for your project. – USER249 Dec 28 '17 at 15:02 — USER249, Dec 28 '17 at 15:02

Search in multiple tables using MySQL and PHP

2 Answers2