What's the best practise if I want to fetch data from two table?

I have two tables,

agent(id, name, password, ..., shop_id);
shop(id, name, address, ...);

Now I want to fetch the shop name for an agent, what should I do? Should I use two sequence sql queries,

<?php
$qstr = <<<SQL
SELECT * FROM agent WHERE id=$id;
SQL;
$result = $conn->query($qstr);
$row = $result->fetch_assoc();
foreach ($row as $key => $value)
    $$key = $value;

$qstr = <<<SQL
SELECT name FROM shop WHERE id=$shop_id;
SQL;
$result = $conn->query($qstr);
$row = $result->fetch_assoc();
$shop_name = $row['name'];
?>

Or should I create a view for this select,

CREATE VIEW f_agent AS SELECT agent.id AS id ,agent.name AS name, shop.id AS shop_id, shop.name AS shop_name FROM agent, shop WHERE agent.shop_id=shop.id;

Or should I just use the sql,

SELECT agent.id AS id ,agent.name AS name, shop.id AS shop_id, shop.name AS sh开发者_JAVA百科op_name FROM agent, shop WHERE agent.shop_id=shop.id and agent.id=$id;

What's the best way to do this?

Thanks.

If this is just a specific query you will use in a page in the front end, the simple SQL should work best. No point creating a view for EVERY SINGLE possible result set that you want from MySQL.

If I can make a suggestion however, please consider the SQL92 ANSI syntax

SELECT agent.id AS id ,agent.name AS name, shop.id AS shop_id, shop.name AS shop_name
FROM agent
INNER JOIN shop ON agent.shop_id=shop.id
WHERE agent.id=$id;

Under no circumstance should you multi-trip to MySQL from PHP using the loop for such a lookup column for the result set.

I wouldn't use a view for such a simple query and using two separate results seems pointless so i'd suggest using the last query

I am new to running queries like this, but I find this thread very informative. Would something like this work as well?

$query = "SELECT shop.id, shop.name, agent.name FROM shop LEFT JOIN agent ON shop.id = agent.id";

$res = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($res)){
    echo $row['shop']['name']. " - ". $row['agent']['name'];
    echo "<br />";
}