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

I am currently trying to get a data(M_Name) from a table called Merchant.

Following are my codes:

<?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);
}
?>

When I run this PHP file using web browser, I couldn't get the desired name for the merchant even though the data is there in the database...it would just return me "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}

What have I done wrong or what am I still missing? Please help..thanks!

I would rather use LEFT JOIN mysql function and get all relevant data at first query from both of your tables

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

so you won't have to make any extra query and you can store your value directly in your array

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

Then I would like you to remember that mysql_* functions are deprecated so i would advise you to switch to mysqli or PDO

The mysql_query("SELECT M_Name FROM MERCHANT WHERE MERCHANT_ID = '".$row["merchant_ID"]."'"); does not return a value from the DB, you need to follow it up with for example mysql_fetch_array like you did with the other query to the DB.

There is a solution more simple: use a JOIN, which combines two tables.

SELECT offer.*, MERCHANT.M_Name 
FROM offer 
LEFT JOIN MERCHANT ON(MERCHANT.MERCHANT_ID = offer.merchant_ID)

$offer["m_name"] = mysql_query("SELECT M_Name FROM MERCHANT WHERE MERCHANT_ID = '".$row["merchant_ID"]."'")

mysql_query() does not return a string but a resource. You'll have to fetch the result.

Also, don't forget that mysql_* is now deprecated.

[edit]

As stated by Fabio, you'd rather use JOIN on your query. At the moment, you are making a request in your loop. That's useless (INNER JOIN or LEFT JOIN are what you want) and very resource consumming.

look like you have to return an result from query instead of while,

mysql_fetch_array(mysql_query("SELECT M_Name FROM MERCHANT WHERE MERCHANT_ID = '".$row["merchant_ID"]."'"));

but had better you make some error hadling first

You can use below one

$resMerchant = mysql_query("SELECT M_Name FROM MERCHANT WHERE MERCHANT_ID = '".$row["merchant_ID"]."'");

$rowMerchant = mysql_fetch_assoc($resMerchant);
$offer["m_name"] = $rowMerchant['M_Name'];