mysql - 连接 2 个具有相同字段名称的表

Question

我有 2 张 table :

authors
+-----------+--------------+------+-----+
| Field     | Type         | Null | Key |
+-----------+--------------+------+-----+
| id        | int(6)       | NO   | PRI | 
| name      | varchar(255) | NO   |     |
| age       | varchar(255) | NO   |     |
+-----------+--------------+------+-----+

books
+-----------+--------------+------+-----+
| Field     | Type         | Null | Key |
+-----------+--------------+------+-----+
| id        | int(6)       | NO   | PRI | 
| name      | varchar(255) | NO   |     |
| pub_date  | datetime     | NO   |     |
| author_id | int(11)      | YES  | MUL | 
+-----------+--------------+------+-----+

我想获取作者信息及其最后撰写的书籍信息。看起来像这样的东西:

{
  name:"Tolkien",
  age:null,
  last_book:{
    name:"The Children of Hurin",
    pub_date:"2007"
  }
}

现在我就是这样做的:

Select Aut.name, Aut.age, Book.name AS Book_name, Book.pub_date 
FROM authors AS Aut 
LEFT JOIN books AS Book ON (Aut.id=Book.author_id) 
WHERE Aut.name="Tolkien" 
ORDER BY Book.pub_date DESC 
LIMIT 1 

这意味着我必须处理结果，然后命令它具有我之前显示的 json。

Answer 1

唯一的方法是后处理，但如果您使用 json_encode 来序列化对象，则没有那么多:

$res = mysqli_query("SELECT ...");
$obj = new stdClass();
if($row = mysqli_fetch_object($res)) {
    $obj->name=$row->name;
    $obj->age=$row->age;
    $book = new stdClass();
    $book->name = $row->Book_name;
    $book->pub_date = $row->pub_date;
    $obj->last_book = $book;
}

print json_encode($obj);