Get multiple selects checked checkboxes using PHP and MySQL

Question

I need to obtain from the database a specific user with all the interests I have in the [a_users_has_interest] table, and display them in Checkbox, but I also want to show all the Interests at the same time, and the interests of the users are selected

Something like this:

Note: I have the following tables, I attached the SQL and code sample

a_interest: All interests

a_users: All users

a_users_has_interest: All users who have interests

-- ----------------------------
-- Table structure for a_interest
-- ----------------------------
DROP TABLE IF EXISTS `a_interest`;
CREATE TABLE `a_interest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a_interest
-- ----------------------------
INSERT INTO `a_interest` VALUES ('1', 'Deportes');
INSERT INTO `a_interest` VALUES ('2', 'Salud');
INSERT INTO `a_interest` VALUES ('3', 'Belleza');
INSERT INTO `a_interest` VALUES ('4', 'Amor');
INSERT INTO `a_interest` VALUES ('5', 'Internet');

-- ----------------------------
-- Table structure for a_users
-- ----------------------------
DROP TABLE IF EXISTS `a_users`;
CREATE TABLE `a_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a_users
-- ----------------------------
INSERT INTO `a_users` VALUES ('1', 'User 1');
INSERT INTO `a_users` VALUES ('2', 'User 2');

-- ----------------------------
-- Table structure for a_users_has_interest
-- ----------------------------
DROP TABLE IF EXISTS `a_users_has_interest`;
CREATE TABLE `a_users_has_interest` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `interest_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`interest_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of a_users_has_interest
-- ----------------------------
INSERT INTO `a_users_has_interest` VALUES ('1', '1');
INSERT INTO `a_users_has_interest` VALUES ('1', '2');
INSERT INTO `a_users_has_interest` VALUES ('1', '3');
INSERT INTO `a_users_has_interest` VALUES ('2', '1');
INSERT INTO `a_users_has_interest` VALUES ('2', '2');

My sample code to get the data:

SELECT *
FROM a_users_has_interest UHI
LEFT JOIN a_interest I ON I.id = UHI.interest_id
WHERE UHI.user_id = '2'

This shows me the user with the options but I want to show all the interests with and the interests that the user has appear with their ID of interest and interests that do not, then they are empty.

Channaveer Hakari · Accepted Answer · 2017-07-31 19:59:04Z

1

Hi use the following code

db_connect.php

$host       =   'localhost';
$user       =   'root';
$password   =   'root';
$database   =   'skerp';


$connection_error   =   'Sorry!!! We are experiencing problems with the database settings';

$link   =   mysqli_connect($host, $user, $password, $database) or DIE($connection_error);

The following in the code where I used it

<?php
require_once('db_connect.php');

$getAllInterests = mysqli_query($link, "SELECT * FROM a_interests");

$userInterests = mysqli_query($link, "SELECT * FROM a_user_has_interests WHERE user_id = 1";

?>
<!doctype html>
<html lang="en">
   <head>
       <meta charset="UTF-8">
       <title></title>
   </head>
   <body>
    <?php
        while($getAllInterest = mysqli_fetch_assoc($getAllInterests)){
            while($userInterest = mysqli_fetch_assoc($userInterests)){
            ?>
            <input 
            <?php echo ($userInterest['id'] == $getAllInterest['id']) ? 'checked="checked"' : '' ?>
            type="checkbox" name="interests[]" value="<?php echo $getAllInterest['id'] ?>"> <?php echo $getAllInterest['name'] ?>
        <?php
            }
        }
    ?>
   </body>
</html>

2nd Method:

Instead of using loop within loop you can use in_array to check the following code

$getAllInterests = mysqli_query($link, "SELECT * FROM a_interests");

$userInterests = mysqli_query($link, "SELECT interest_id FROM a_user_has_interests WHERE user_id = 1";

<?php
        while($getAllInterest = mysqli_fetch_assoc($getAllInterests)){
        ?>
            <input 
            <?php echo (in_array($getAllInterest['id'], $userInterests)) ? 'checked="checked"' : '' ?>
            type="checkbox" name="interests[]" value="<?php echo $getAllInterest['id'] ?>"> <?php echo $getAllInterest['name'] ?>
        <?php
        }
    ?>

edited Jul 31, 2017 at 19:59

answered Jul 31, 2017 at 19:49

Channaveer Hakari

2,9493 gold badges40 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tadman Over a year ago

die is sufficient. There's no need to yell.

Channaveer Hakari Over a year ago

@tadman Sorry I that was not my intent. Just wanted him to get the clear cut picture.

tadman Over a year ago

@ChannaveerHakari I'm half joking. It's important to try and use methods in their canonical form even if PHP itself is largely case-insensitive. It helps promote consistency and makes looking things up in the documentation more clear.

Collectives™ on Stack Overflow

Get multiple selects checked checkboxes using PHP and MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related