输出表中的所有数据并显示哪些行属于用户

I have a roles table which contains various user roles.

A pivot tables joins this to my users table.

users
id | name

roles
id | title

user_role
user_id | role_id

I would like to display all roles that exist and pre tick the ones that belong to a specific user.

How would I go about this, I take it I cannot do this in mySQL?

I was considering getting all roles with one query, then with another query get all roles that belong to a specific user.

Then loop through all roles and if there is a match with the specific users roles, output a checked box instead of an unchecked one.

Is there a better way?

Something like

SELECT R.id as role_id,
       R.title as role_title,
       UR.user_id as user_id,
       U.name as user_name
  FROM Roles R
  LEFT JOIN user_role UR
         ON UR.role_id = R.id
        AND UR.user_id = :myuserid
  LEFT JOIN users U
         ON U.id = UR.user_id

should return a complete list of roles, with either a NULL or the user id in the user column to indicate any roles that the user has been granted (user identified by id as :myuserid)

I think normal SQL Query can do most of the work: Checkout this SQLFIddle for sample of what your query might look like

SELECT r.* FROM roles r LEFT JOIN user_role ur ON  r.id=ur.role_id WHERE ur.user_id=3;