php - 如何使用搜索词使用下拉列表从多个表中选择数据？

Question

我正在尝试使用搜索词从多个表中获取数据。实际上有一个数据库包含不同主题(如饮食、炎症等)的基因信息。有些基因在少数主题中很常见，例如 SIRT1 在饮食和炎症中都很常见，但是当我尝试获取该基因的信息时通过多次选择主题(即表格)，但它仅显示一个主题，即仅来自一张表格。下面是 html 中多选表的代码，它可以选择多个选项，但无法从数据库中选择多个表。

<form method="post" action="test3.php" name="search_form">
    <p align="center">
    <input type="text" name="search" size=15 maxlength=15 >
    <select name="table" size = "2" multiple>
    <option selected="selected"></option>
    <option value="infla_info">Inflammation</option>
    <option value="diet_info">diet</option>
    <option value="obesity_info">obesity</option>
    <option value="stress_info">stress</option>
    <option value="atherosclerosis_info">atherosclerosis</option>
    <option value="retinopathy_info">Diabetic Retinopathy</option>
    <option value="nephropathy_info">Diabetic Nephropathy</option>
    <option value="neuropathy_info">Diabetic Neuropathy</option>
    </select>
    <input type="Submit" name="Submit" value="Gene Search">
    </p>
    </form>

在这段 PHP 代码之后，用于获取选定表中给定基因的信息，如下所示:

  <?php
    $table=$_POST['table'];
    $search=$_POST['search'];

    if ($search == "" or $table=="")
    {
    echo"<a href='a.html'> back </a>";
    echo "<p>You forgot to enter the Gene Name";
    exit;
    }
    else
    {

   $con=mysqli_connect("localhost", "root", "", "t2diacod");
    if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

 //$table = array("diet_info","infla_info","blue","yellow"); 
 // if (is_array($table))
 // {
 //foreach ($table as $t){  

    if ($table=="diet_info")
    {

    $sql = mysqli_query($con, "select * from diet_info where Gene_symbol = '$search'");
    $num_row=$sql->num_rows;

    if ($num_row == 0)
    {
    echo "Sorry, Invalid Gene Name.";
    }
   else
   {
  echo "<h1> DIET </h1>";

    echo "<table border='0', width='1000'>
<tr>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Locus_Id</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Gene_Symbol</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Gene_Name</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Synonyms</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Chromosome</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Gene Summary</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Homologene_Id</font></th>
</tr>";
    for($i=0;$i<$num_row;$i++)
{

$result=mysqli_fetch_assoc($sql);
  echo "<tr bgcolor = '#E6E6FA'>";
    $strName2 = $result["Locus_id"];
  $strLink2 = "<a href = 'diet_gene_go.php?gene_id=" . $result['Locus_id'] . "'>" . $strName2 . "</a>";
    echo "<td align=center>" . $strLink2 . "</td>";
  $strName1 = $result["Gene_symbol"];
  $strLink1 = "<a href = 'diet_gene_symbol.php?gene_symbol=" . $result['Gene_symbol'] . "'>" . $strName1 . "</a>";
    echo "<td align=center>" . $strLink1 . "</td>";
  echo "<td align=center>" . $result['Gene_name'] . "</td>";
  echo "<td align=center>" . $result['Synonymns'] . "</td>";
  echo "<td align=center>" . $result['Chromosome_no'] . "</td>";
   echo "<td height='50px'><div style='height:200px; width:400px; overflow:scroll;'>" . $result['Gene_summary'] . "</div></td>";
 //$strName = $result['FirstName'] . " " . $result['LastName']
      $strName = $result["Homologene_id"];

       // Create a link to person.php with the id-value in the URL
       // $strLink = "<a href = 'person.php?id = " . $result['id'] . "'>" . $strNavn . "</a>";
  $strLink = "<a href = 'diet_homologene.php?homologene_id=" . $result['Homologene_id'] . "'>" . $strName . "</a>";

   echo "<td align=center>" . $strLink . "</td>";
  // echo "<td align=center>" . $result['PMID'] . "</td>";
   echo "</tr>";

    //This counts the number or results - and if there wasn't any it gives them a little message explaining that


}
   }
    }

    if ($table=="infla_info")
    {

    $sql = mysqli_query($con, "select * from infla_info where Gene_symbol = '$search'");
    $num_row=$sql->num_rows;

    if ($num_row == 0)
    {
    echo "Sorry, Invalid Gene Name or you are looking at the wrong place double check the Gene name and search it again.";
    }
   else
   {
       echo "<h1> Inflammation </h1>";
       echo "<table border='0', width='1000'>
<tr>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Locus_Id</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Gene_Symbol</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Gene_Name</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Synonyms</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Chromosome</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Gene Summary</font></th>
<th bgcolor = '#2F4F4F' align = center><font color = '#fff'>Homologene_Id</font></th>
</tr>";
        for($i=0;$i<$num_row;$i++)
{

$result=mysqli_fetch_assoc($sql);
  echo "<tr bgcolor = '#E6E6FA'>";
    $strName2 = $result["Locus_id"];
  $strLink2 = "<a href = 'infla_gene_go.php?gene_id=" . $result['Locus_id'] . "'>" . $strName2 . "</a>";
    echo "<td align=center>" . $strLink2 . "</td>";
  $strName1 = $result["Gene_symbol"];
  $strLink1 = "<a href = 'infla_gene_symbol.php?gene_symbol=" . $result['Gene_symbol'] . "'>" . $strName1 . "</a>";
    echo "<td align=center>" . $strLink1 . "</td>";
  echo "<td align=center>" . $result['Gene_name'] . "</td>";
  echo "<td align=center>" . $result['Synonymns'] . "</td>";
  echo "<td align=center>" . $result['Chromosome_no'] . "</td>";
   echo "<td height='50px'><div style='height:200px; width:400px; overflow:scroll;'>" . $result['Gene_summary'] . "</div></td>";
 //$strName = $result['FirstName'] . " " . $result['LastName']
      $strName = $result["Homologene_id"];

       // Create a link to person.php with the id-value in the URL
       // $strLink = "<a href = 'person.php?id = " . $result['id'] . "'>" . $strNavn . "</a>";
  $strLink = "<a href = 'infla_homologene.php?homologene_id=" . $result['Homologene_id'] . "'>" . $strName . "</a>";

   echo "<td align=center>" . $strLink . "</td>";
  // echo "<td align=center>" . $result['PMID'] . "</td>";
   echo "</tr>";

    //This counts the number or results - and if there wasn't any it gives them a little message explaining that


}
   }

    } ....... and so on for all tables

请帮助我得到我想要的东西。

Answer 1

您需要更改多选下拉列表的名称。使用 [] 命名(将包含多个选定值的数组发布到 php)

因此，将 name="table" 更改为 name="table[]"

<select name="table[]" size = "2" multiple>
    <option selected="selected"></option>
    <option value="infla_info">Inflammation</option>
    <option value="diet_info">diet</option>
    <option value="obesity_info">obesity</option>
    <option value="stress_info">stress</option>
    <option value="atherosclerosis_info">atherosclerosis</option>
    <option value="retinopathy_info">Diabetic Retinopathy</option>
    <option value="nephropathy_info">Diabetic Nephropathy</option>
    <option value="neuropathy_info">Diabetic Neuropathy</option>
</select>

<input type="Submit" name="Submit" value="Gene Search">
</p>
</form>

在 PHP 中，您需要对每个选择进行循环，如下所示:

$tables=$_POST['table'];

//test for table selection, (validation)
if(count($tables)==0){
    echo "Please select table to search in.";
}else{
    foreach($tables as $table){   //perform search for each selected tables
        //you might want to use switch case here
        switch($table){
            case "infla_info":
                    //code to search in infla_info table
                    break;

            case "diet_info":
                    //code to search in diet_info table

            ...
            ...
        }    
    }
}