How to query multiple select elements stored in database

Question

Querying areas of expertise (stored as an array of any selected items) does not work. I'm trying to figure out a way to get registered farmers' areas of expertise when editing. I have two tables. Registered farmers table and professional field table in the database. Specialties are stored in the Specialties table. The registered farmers table contains all data of registered farmers. This is the expertise area table: area_of_expertise_idid_number_of_the_farmerareas_of_expertiseregister_farmer_id1232

P粉060112396 · Answer

You may need to overthink your table design as this looks like a classic 1:n relationship (even if it is 1:1, the information will not change)

Try this:

Farmers:
ID (PRIMARY, AutoIncrement), name, age

Expertises:
farmerID (index), areaOfExpertise

Insert the following (assuming prepared statements):

INSERT INTO `farmers` VALUES (NULL, ?, ?)`

Then you retrieve the "last insert ID" from the active connection and put it into the second table.

INSERT INTO `expertise` VALUES (?, ?)

To retrieve the information you need to do the opposite. To retrieve all farmers you can use query

SELECT * FROM `farmers`

And, to get only specific farmers (assuming you have the ID), you can use:

SELECT * FROM `farmers` WHERE ID = ?

Whether you are viewing all farmers or just one farmer, when evaluating information you will access areas of expertise such as:

SELECT * FROM `expertise` WHERE farmerID = ?

That's what it's all about. Assuming a 1:1 relationship (such as "Each farmer has only one area of expertise"), you can also write it out in one go like this:

SELECT * FROM `farmers`, `expertise` WHERE ID = ? and farmerID = ID;

register_farmer_id	name_of_the_farmer	age_of_the_farmer
1	anonymous	twenty three
2	Mary Doe	34

area_of_expertise_id	id_number_of_the_farmer	areas_of_expertise	register_farmer_id
1	23232223	nourish	1
2	23233453	reward	2