搜索多个表中的记录,并在mysql中从php打印输出 [英] search record in multiple tables and print output differently in mysql from php
问题描述
我的数据库中有四个表。 TABLE1
具有5列, TABLE2
具有7列, TABLE3
具有9和 TABLE4
分别具有11列。
I have four tables in my database. TABLE1
having 5 columns, TABLE2
having 7, TABLE3
having 9 and TABLE4
having 11 columns respectively.
所有4个表均具有<$ c $列c> PID ,我要从中进行用户查询。即
all 4 tables have a column PID
, from which i want to make a user query. i.e.
SELECT pid FROM ....
我面临的问题是,如果在 TABLE1 $ c中找不到我的查询,如何从所有4个表中进行选择$ c>然后在
TABLE2
中搜索,依此类推。
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.
由于所有表的结构都不同,因此结果必须以不同的方式传递输出,例如,如果在 TABLE1
中找到查询,则输出将有一个包含5列的表,或者来自 TABLE4
输出将显示11列。
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.
现在,我只能查询一张表。
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>
这是从 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!";
}
}
这些是下表的结构
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
推荐答案
$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__);
}
要处理表列计数冲突,应使用foreach语句
to handle table column count conflict, you should use foreach statement
if($result->num_rows <= 1)
while($row = $result->fetch_assoc())
foreach ($row as $r1=>$r){
echo $r1.": ".$r."<br/>"; ...
}
这篇关于搜索多个表中的记录,并在mysql中从php打印输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!