php - mysql+php查询查询两张表得到结果

Question

我有两张 table 。

表 1 包含字段:

| Ensemble_ID | varchar(50) | NO   | PRI |         |       |
| Target      | text        | YES  |     | NULL    |       |
| Gene_Length | int(5)      | YES  |     | NULL    |       |
| miRNA       | varchar(50) | NO   | PRI |         |       |
| position    | int(4)      | YES  |     | NULL    |       |
| Prediction  | text        | YES  |     | NULL    |       |

我的表 2 包含字段:

|Ensemble_ID   | varchar(50)  | NO   | PRI |         |       |
| miRNA        | varchar(50)  | NO   | PRI |         |       |
| miRNA_Length | int(2)       | YES  |     | NULL    |       |
| mfe          | decimal(2,0) | YES  |     | NULL    |       |
| pvalue       | decimal(4,0) | YES  |     | NULL    |       |
| no_of_seeds  | int(1)       | YES  |     | NULL    |       |

我需要像这样的结果

|Ensemble_ID    |Gene Length|miRNA|miRNA Length|mfe|P-value|Position|Prediction|No of Seeds|

我是mysql新手。谁能帮我写一个查询。

感谢帮助。

这是我的 php 附件 .. 由于显示查询错误，我无法获取结果

<?php
$a = $_REQUEST["miRNA"];
$b = $_REQUEST["target"];
// $result = db::table("`table`") -> pluck("*") -> where("miRNA",$a) -> select() -> get();
$mysqli = new mysqli("localhost", "root", "password", "mysql");
$a = $mysqli -> escape_string($a);
$b = $mysqli -> escape_string($b);
$a = $mysqli->query("SELECT * FROM bio3 WHERE miRNA = '$a' AND Target LIKE '%$b' INNER JOIN bio4 on bio3.ensemble_id = bio4.ensemble_id  ORDER BY bio4.pvalue ASC;");


// $result = $a -> fetch_assoc();
$i = 0;
while ($row = $a -> fetch_assoc()) {
$result[$i] = $row;
$i++;

}
$mysqli->close();
for($a=0;$a<sizeof($result);$a++){
print '<tr>
<td>'.htmlentities($result[$a]["Target"]).'</td>
<td>'.htmlentities($result[$a]["Gene Length"]).'</td>
<td>'.htmlentities($result[$a]["miRNA"]).'</td>
<td>'.htmlentities($result[$a]["miRNA Length"]).'</td>
<td>'.htmlentities($result[$a]["mfe"]).'</td>
<td>'.htmlentities($result[$a]["pvalue"]).'</td>
<td>'.htmlentities($result[$a]["position"]).'</td>
<td>'.htmlentities($result[$a]["Prediction"]).'</td>
<td>'.htmlentities($result[$a]["No of Seeds"]).'</td>
</tr>';
}
?>

Answer 1

连接两个表，例如:

SELECT t1.Ensemble_ID, t1.Gene_Length, t1.miRNA, t2.miRNA_Length, t2.mfe, t2.pvalue, t1.position, t1.Prediction, t2.no_of_seeds
FROM table1 t1 INNER JOIN table2 t2
ON t1.Ensemble_ID = t2.Ensemble_ID
WHERE t1.miRNA = 'abc' 
AND t1.target LIKE '%xyz' 
ORDER BY t2.pvalue ASC;