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.

Professional fields are stored in the professional fields table.

The registered farmers table contains all data of registered farmers.

This is the professional field table:

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

Registered farmer form:

register_farmer_id	name_of_the_farmer	age_of_the_farmer
1	anonymous	twenty three
2	Mary Doe	34

I want to have a registered farmer ID in the professional field table, just like in the registered farmer table. I'm guessing this will be a foreign key.

I'm trying to query all areas of expertise for a certain farmer. The queried data will be displayed on its page for editing.

Storing data in professional fields works well. I use foreach loop to store data. The problem now is querying the data.

please help.

Answer 1

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;