>我必须像波纹管一样表
产品表
+---------------------------------------------------+
| id| itemName |itemColor|(some other specification)|
+---------------------------------------------------+
| 1 | item 1 | red | ------- |
| 2 | item 2 | green | -------- |
| 3 | item 3 | blue | ------- |
+---------------------------------------------------+
汇率表
+----------------------------------+
|id|itemFrom|itemTo|rateFrom|rateTo|
+----------------------------------+
| 1| 1 | 3 | 1 | 30 |
| 2| 1 | 2 | 30 | 20 |
| 3| 1 | 1 | 3 | 2 |
| 4| 2 | 1 | 5 | 3 |
| 5| 2 | 3 | 6 | 10 |
| 6| 2 | 2 | 6 | 5 |
| 7| 3 | 1 | 1 | 1 |
| 8| 3 | 2 | 5 | 3 |
| 9| 3 | 3 | 2 | 1 |
+----------------------------------+
现在让我们说$itemfrom="item 2"
(使用 $_GET(我需要像波纹管这样的 json 输出
{
"itemName":"item 1",
"itemcolor":"red",
"exchangeFromRate":"5"
"exchangeToRate":"3"
},{
"itemName":"item 2",
"itemcolor":"green",
"exchangeFromRate":"6"
"exchangeToRate":"5"
},{
"itemName":"item 3",
"itemcolor":"blue",
"exchangeFromRate":"6"
"exchangeToRate":"10"
}
获取输出项目名称,规格和费率的正确查询是什么?我尝试使用"join"语句,但无法正确配置它。
波纹管是我的划痕
$itemfrom=$_GET['itemfrom'];
$qry="select id from productTable where itemName='$itemfrom'"
$result=mysqli_query($conn,$qry) or die("error in selecting ".mysqli_error($conn));
while($row =mysqli_fetch_assoc($result))
{
$itemFromID=$row['id'];
}
$finalqry="select P.itemName,
P.itemColor,
R1.ratefrom
R2.rateto
from ProductTable P
Join ExchangeRateTable R
R1.ratefrom=?????
R2.rateto=???????";
$finalResult = mysqli_query($conn, $finalqry) or die("Error in Selecting " .mysqli_error($conn));
while($row =mysqli_fetch_assoc($finalResult ))
{
$exchangeToNameRates[] = $row;
}
$exchangeToNameRateArrey = ['status' =>"true",
"message"=> "Data fetched successfully!",
'data' =>$exchangeToNameRates];
echo json_encode($exchangeToNameRateArrey );
你不需要两次查询,你可以像下面这样使用查询,并使用连接器和条件连接表:
SELECT P.itemName,
P.itemColor,
R.rateFrom
R.rateTo
from ProductTable P
Join ExchangeRateTable R
on R.itemFrom=P.id
where P.itemName='$itemfrom'
**更新:**
您的第一个查询正常,并找到$itemFromID之后使用此查询,您将看到 Exchange 的所有结果:
SELECT P.itemName,
P.itemColor,
R.rateFrom
R.rateTo
from ProductTable P
Join ExchangeRateTable R
on R.itemTo=P.id
where R.itemFrom='$itemFromID'