php - 无法从 MySQL 中的另一个表获取数据

Question

我目前正在尝试从名为 Merchant 的表中获取数据(M_Name)。

以下是我的代码:

<?php
$response = array();

$link = mysql_connect('localhost','root','') or die ('Could not connect: '.mysql_error());
mysql_select_db('ichop') or die ('Could not connect to database');

$result = mysql_query("select * from offer") or die(mysql_error());

if(mysql_num_rows($result) > 0){
    $response["offers"] = array();

    while($row = mysql_fetch_array($result)){
        $offer = array();
        $offer["offer_id"] = $row["Offer_ID"];
        $offer["start_date"] = $row["Start_Date"];
        $offer["end_date"] = $row["End_Date"];
        $offer["o_desc"] = $row["O_Desc"];
        $offer["short_desc"] = $row["Short_Desc"];
        $offer["merchant_ID"] = $row["Merchant_ID"];
        $offer["m_name"] = mysql_query("SELECT M_Name FROM MERCHANT WHERE MERCHANT_ID = '".$row["merchant_ID"]."'");

        array_push($response["offers"], $offer);
    }
    $response["success"] = 1;

    echo json_encode($response);
} else {
//no offer found
$response["success"] = 0;
$response["message"] = "No offer found";

echo json_encode($response);
}
?>

当我使用网络浏览器运行此 PHP 文件时，即使数据存在于数据库中，我也无法获得所需的商家名称......它只会返回“null”。

{"offers":[{"offer_id":"1","start_date":"2013-05-17","end_date":"2013-05-18","o_desc":"AAA","merchant_ID":"2","m_name":null}],"success":1}

我做错了什么或者我还缺少什么？请帮忙..谢谢!

Answer 1

我宁愿使用LEFT JOIN mysql函数并在第一次查询时从两个表中获取所有相关数据

 SELECT * FROM offer a LEFT JOIN MERCHANT b ON a.Merchant_ID = b.MERCHANT_ID

因此您不必进行任何额外的查询，您可以将值直接存储在数组中

$offer["m_name"] = $row['M_Name'];

那么我希望您记住 mysql_* 函数已被弃用，因此我建议您切换到 mysqli或PDO