PHP and MySQL Get multiple selects checked checboxes

Question

PHP and MySQL Get multiple selects checked checboxes

Navigation

#1 by (2 votes)

1

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

Something like this:

Note: I have the following tables, I enclose the SQL and the code example

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 with his interest ID and the interests that are not, then they are empty.

I really appreciate your help

mysql php

asked by Learning and sharing 31.07.2017 в 21:29

source

1 answer

How to get the number of days, hours and minutes elapsed from a date? in C # Change border-bottom-color in Material Design input

score 2 · Answer 1

What you can do is invert the LEFT JOIN in the following way

SELECT I.id, 
       I.name, 
       CASE WHEN UHI.user_id IS NULL THEN 0 ELSE 1 END as 'HasInterest'
FROM a_interest I
LEFT JOIN a_users_has_interest UHI 
  ON I.id = UHI.interest_id
  AND UHI.user_id = '2'
-- ORDER BY I.id
ORDER BY 'HasInterest', I.id

This shows you all the interests and a 1 in case the user_id = 2 has it as one of your interests and 0 otherwise. Something like this:

id  name        HasInterest
=== =========== ===========
1   Deportes    1
2   Salud       1
3   Belleza     0
4   Amor        0
5   Internet    0

Regarding the order, you can choose to always appear in the same order or first display the selected items.