同时使用两个查询。 PDO

Question

Okay so, I don't really know anything about PDO, my friend just asked me to post this here since he's not very good at English. Anyway, this is how he explained it to me:

The code provided is supposed to get a couple of values, save them, and it's supposed to get something out of another table with the help of the values gotten from earlier. The problem according to my friend is that it doesn't get the second value.

Code:

$user_email = $_SESSION['user_email'];
$query = $db->prepare("SELECT username,id,password FROM user WHERE email=:email");
$query->bindParam(':email', $user_email, PDO::PARAM_INT);
$query->execute();
$row = $query->fetch();
$user_username=$row['username'];
$user_group=$row['group'];   
$query_group = $db->prepare("SELECT color,name FROM group WHERE id=:id");
$query_group->bindParam(':id', $user_group, PDO::PARAM_INT);
$query_group->execute();
$row = $query_group->fetch();
$group_color=$row['color'];
$group_name=$row['name'];

Answer 1

The word group used as a table name needs to be enclosed in backticks. group is a reserved key word (GROUP BY clause).

SELECT 
    color, 
    name 
FROM `group`
WHERE id = :id

Using the above would work.

---

You can shorten the entire code by using a JOIN clause too. As commented above by Prix, the code shall be:

$user_email = $_SESSION['user_email'];
$query = $db->prepare("SELECT 
    u.username,
    u.id,
    u.password,
    g.color,
    g.name
FROM user u
JOIN `group` g
    ON g.id = u.id
WHERE u.email = :email");
// I think emails are supposed to be `PDO::PARAM_STR`
$query->bindParam(':email', $user_email, PDO::PARAM_INT);
$query->execute();
$row = $query->fetch();
$user_username = $row['username'];
$group_color = $row['color'];
$group_name = $row['name'];