在多个表中搜索记录，并在php中从mysql中以不同方式打印输出

I have four tables in my database. TABLE1 having 5 columns, TABLE2 having 7, TABLE3 having 9 and TABLE4 having 11 columns respectively.

我的数据库中有四个表。表1具有5列，TABLE2具有7，TABLE3具有9，TABLE4具有11列。

all 4 tables have a column PID, from which i want to make a user query. i.e.

所有4个表都有一个列PID，我想从中进行用户查询。即

SELECT pid FROM ....

the problem i am facing, is, how to choose from all the 4 tables, if my query is not found in TABLE1 then search in TABLE2, and so on.

我面临的问题是，如何从所有4个表中进行选择，如果在TABLE1中找不到我的查询，则在TABLE2中搜索，依此类推。

since all tables have different structure, the result must pass differently for output, like, if query was found in TABLE1 the output would have a table with 5 columns, or if from TABLE4 the output would show 11 columns.

由于所有表都具有不同的结构，因此结果必须以不同方式传递输出，例如，如果在TABLE1中找到查询，则输出将具有包含5列的表，或者如果来自TABLE4，则输出将显示11列。

for now, i can make a query for only 1 table.

现在，我只能查询一个表。

<h2>title</h2>
            <div class="panel panel-primary">
            <div class="panel-heading">SELECT NAME FROM LIST</div>
            <div class="panel-body">
            <form name="dropdown" action="http://search.php" method="post">
            <select class="form-control" data-style="btn-primary" name="p_ID">
<?php
//provide your hostname, username and dbname
$host="localhost"; 
$username="root";  
$password="";
$db_name="mydb"; 
$con=mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name");

$sql = "select PID from TABLE1";
$result = mysql_query($sql);
while($row=mysql_fetch_array($result))
{
echo "<option value=$row[PID]>$row[PID]</option>";
}
?>

    </select><br><br>
            <button class="btn btn-primary center-block btn-lg" type="submit" >Search</button>
                </form> 

            </div>
        </div>
    </div>

this is from Search.php for the output table.

这是来自Search.php的输出表。

<?php   

//provide your hostname, username and dbname
$DB_HOST = 'localhost';
$DB_USER = 'root';
$DB_PASS = '';
$DB_NAME = 'mydb';
$mysqli = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if(!$mysqli) 
{
    echo 'Could not connect to the database.';
} 
else 
{
if(isset($_POST[PID]) )
{
//echo "Input by list<br>";

$query = "select * from TABLE1 where PID like '$_POST[PID]%' ";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
    //$arr = array();
    if($result->num_rows <= 1) 
    {
    echo "<table class=\"table\" ><tr><th>Protein ID:</th>";
        while($row = $result->fetch_assoc()) 
        {
        echo "<td>$row[PID]</td><tr><th>Protein Domain:</th><td>$row[p_name]</td></tr>
        <tr><th>Interacting Proteins:</th><td>";
        $seqsplit1 = wordwrap($row[p_symbol_orf], 60, "-\n", true);
        echo "$seqsplit1</td>";

        echo "</tr><tr><th>Protein Sequence:</th><td><div>";
        $seqsplit = wordwrap($row[p_sequence], 60, "\n", true);
        echo ">$row[p_ts]<br>$seqsplit";
        echo "</div></td></tr><tr><th>Protein Length:</th><td>$row[p_length]</td></tr><tr><td colspan='2'>";

        }
        ?>          

<?php       

    echo "</td></tr></tbody></table>";
    }
    else
    {
        echo "No query found, please search next!";
    }
} 

these are the structure of following tables

这些是下表的结构

TABLE1
PID PNAME PSYMBOL PSEQ PLEN

TABLE2
PID PNAME1 PSYMBOL1 PNAME2 PSYMBOL2  PSEQ PLEN

TABLE3
PID PNAME1 PSYMBOL1 PNAME2 PSYMBOL2 PNAME3 PSYMBOL3 PSEQ PLEN

TABLE4
PID PNAME1 PSYMBOL1 PNAME2 PSYMBOL2 PNAME3 PSYMBOL3 PNAME4 PSYMBOL4 PSEQ PLEN

1 个解决方案

#1

$query = "select * from TABLE1 where PID like '$_POST[PID]%'  ";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);
if($result->num_rows == 0){
    $query = "select * from TABLE2 where PID like '$_POST[PID]%'  ";
    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
}else if($result->num_rows == 0){
    $query = "select * from TABLE3 where PID like '$_POST[PID]%'  ";
    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
}else if($result->num_rows == 0){
    $query = "select * from TABLE4 where PID like '$_POST[PID]%'  ";
    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
}else if($result->num_rows == 0){
    $query = "select * from TABLE5 where PID like '$_POST[PID]%'  ";
    $result = $mysqli->query($query) or die($mysqli->error.__LINE__);
}

to handle table column count conflict, you should use foreach statement

要处理表列计数冲突，您应该使用foreach语句

if($result->num_rows <= 1)    
   while($row = $result->fetch_assoc())
       foreach ($row as $r1=>$r){
           echo $r1.": ".$r."<br/>"; ...
       }