Select from mysql and format them with checkboxes and set checked=“checked” attribute

[thara]

I have two mysql tables named facilities and other one is user_facilities. In facilities table have stored all available facilities and user_facilities table have particular facilities belong to one user.

Now I need to fetch all the facilities from mysql and need to format with checkbox for each facility. While fetching every facilities I need to set checked="checked" attribute to checkboxes which are belong to current user.

I can select all facility from mysql like this:

    $sql = "SELECT id, name
            FROM cuisines";

And this is how it looks my `WHILE` loop.

    $result = '';
    // Fetch all the records:
    while ($stmt->fetch()) {
        
        $result  = "<div class='checkbox'>\n";
        $result .= "    <label>\n";
        $result .= "        <input type='checkbox' name='facilities[]' value='{$id}'> {$name}\n";
        $result .= "    </label>\n";
        $result .= "</div>\n";
        
        $output[] = $result;     
    }

Can anybody tell me how add checked="checked" attribute for these checkboxes which are belong to current user?

This is my user_facilities table

CREATE TABLE IF NOT EXISTS user_facilities(
    user_id INT(4) UNSIGNED NOT NULL,
    facility_id INT(4) UNSIGNED NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Edited July 21, 2015 by thara

[Barand]

You need to query your facility table LEFT JOINED to the user_facility table

SELECT f.facility_name
    , uf.user_id
FROM facility f
    LEFT JOIN user_facilty uf ON f.facily_id = uf.facility_id AND uf.user_id = $userID;

This will give results like

+---------------+---------------+
| facilty_name  | user_id       |
+---------------+---------------+
| air_con       | NULL          |
| wi-fi         | 1             |
| smoking area  | NULL          |
| bar           | 1             |
+---------------+---------------+

Loop through the results outputting the facility name and checkbox.
Check those boxes where restaurant_id is not null

air_con      [ ]
wi-fi        [X]
smoking area [ ]
bar          [X]

Edited July 21, 2015 by Barand