使用左连接的Mysql查询 - 如果两行相同则使用specialize索引

Question

I have a table called users and another called grouppost in my mysql database. Both tables have the id row in common. So when I make an sql query left joining the two tables how can I decide which id row I want?

Example

$sql = "SELECT gp.*, u.*
      FROM grouppost
      AS gp LEFT JOIN users AS u ON u.username = gp.author
      WHERE gp.gname = ? AND gp.type = ? ORDER BY gp.pdate DESC $limit";
      $stmt = $conn->prepare($sql);
      $stmt->bind_param("ss",$g,$zero);
      $stmt->execute();
      $result_new = $stmt->get_result();
      if ($result_new->num_rows > 0){
         while ($row = $result_new->fetch_assoc()) {
            $grouppost_id = $row["id"]; // I want to get the id from table grouppost but I get the id from the users table
         }
      }

Answer 1

If you want to target it separately in the result set, you can alias it in your select:

SELECT gp.*, u.*, gp.id AS grouppost_id 
FROM grouppost AS gp 
LEFT JOIN users AS u ON u.username = gp.author
WHERE gp.gname = ? 
  AND gp.type = ? 
ORDER BY gp.pdate DESC
$limit