php - 如果我想从两个表中获取数据，最佳做法是什么？

Question

我有两个表，

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

现在想给代理取店名，怎么办？我应该使用两个序列 sql 查询吗，

<?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'];
?>

或者我应该为这个选择创建一个 View ，

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;

或者我应该只使用 sql，

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 and agent.id=$id;

执行此操作的最佳方法是什么？

谢谢。

Answer 1

如果这只是您将在前端页面中使用的特定查询，那么简单的 SQL 应该效果最好。没有必要为您希望从 MySQL 获得的每个可能的结果集创建 View 。

如果我能提出建议，请考虑 SQL92 ANSI 语法

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;

在不情况下，您应该使用循环来从 PHP 多次访问 MySQL 结果集的查找列。