Best Method to store a Checkbox

Good morning everyone,
I have a checkbox form, where the user selects one or more options for each category. I need to save the selection made in a mysql database.

the database:

CATEGORY
id_category: INT PRIMARY AI
name: VARCHAR (30)
PRODUCT
id_product: INT PRIMARY AI
id_category INT
name: VARCHAR (30)

initially I thought of using the php implode function and saving only the indexes, separated by a dot, in the user table.

for example, if user select:
Meat (as category),
tbone and cicken (as product).
Fruit (as category),
pear, apple and cherry (as product).


i have something like: 1.3,1.15,9.5,9.18 (9 category . 18 product in this case)

at this point I would insert this string into the user table.
the moment I need to access the data, I would have to use the exoplode command twice.
one to divide the categories and one to divide the products.
with the result, I would make a query to the categories table and one to the products table.

Do you think this is a good way to approach this type of situation or is there a better way to save a series of checkboxes?

Keep them seperated.

And if you want to let users select items on your website and save this selection, then you will need another table: shopping_list (for example). And in this table you will have columns: shopping_list_id - total_price - etc - etc - category_id - product_id, etc - etc..  Everything will be clear for later queries. No need to keep them as a string like 19.3 and try to explode later.

Keep them seperated.

And if you want to let users select items on your website and save this selection, then you will need another table: shopping_list (for example). And in this table you will have columns: shopping_list_id - total_price - etc - etc - category_id - product_id, etc - etc..  Everything will be clear for later queries. No need to keep them as a string like 19.3 and try to explode later.

ok, thanks for the reply.
so the best thing is to create a table like this:
user_preference, with inside:
id, user_id, product_id

and insert a new line for each checkbox the user selects.
then make a query with a join to extract the name of the product and its category from the "product" table.

for example, if the user with id = 1 selects pear and apple,
I will have two lines like this in the user_preference table:

user_id: 1 product_id: 5
user_id: 1 product_id: 19

as regards the variables, is it correct to build an array and then do a foreach with an insert for each preference?

considering that the user selects a maximum of 3 options.

Thank you

Why don't you also add the category_id to your user_preference table? What happened to that?

Do you mean limiting the user for maximum 3 options?
First, I would go with frontend control & warning with JS. And then I would control it again in Controller and proceed.

Why don't you also add the category_id to your user_preference table? What happened to that?

Do you mean limiting the user for maximum 3 options?
First, I would go with frontend control & warning with JS. And then I would control it again in Controller and proceed.

yes you are right. also added category_id.

sorry if it took me a while to respond, but I was developing the right method to generate the page that displays the form with the checkboxes.

I think I created a bit of chaos and I think I went around the same query several times to get the data out.

PHP Code:

class SchedaAlimentiController extends BaseController {

    public function SchedaAlimenti() {
        
        $db = \Config\Database::connect();
        $builder = $db ->table('categorie_alimentari');
        $builder->select('id_categoria_alimentare ,nome');
        $query = $builder->get();
        $data['categoria'] = $query->getResult();

        $db = \Config\Database::connect();
        $builder = $db ->table('categorie_alimentari');
        $builder->select('categorie_alimentari.id_categoria_alimentare, categorie_alimentari.nome AS categoria, alimenti.nome AS alimento, alimenti.id_alimento');
        $builder->join('alimenti', 'categorie_alimentari.id_categoria_alimentare=alimenti.id_categoria_alimentare');
        $query = $builder->get();
        $data['alimenti'] = $query->getResult();
        
        return view('utente/schedaAlimenti', $data);

    }
} 


In the view:

PHP Code:

<?php
foreach ($categoria as $riga):
    echo "<strong>" . $riga->nome . "</strong><br>";
    foreach ($alimenti as $alimento):
        if ($alimento->categoria === $riga->nome){
            echo $alimento->alimento . "<br>";
        } 
    endforeach;
    echo "<br>";
endforeach;
?>

Example result:
Yogurt
Yogurt Magro
Yogurt Intero
Yogurt Greco Intero

Legumi
Fagioli
Lenticchie
Ceci
Piselli
Fave

do you think this is a good way to download and visualize the data, or can you give me some pointers to improve the code?

obviously then in the view there will be the CSS and the entire part relating to the form and for each product there will be the relevant checkbox.

any advice is welcome

First, If I were you I would do the sql part in the Model file. Also a foreach in another one is nod adviced. It's not good. You need to handle it in sql.


In your Model :

PHP Code:

public function getCategoriesWithProducts() {
        $builder = $this->db->table('categories');
        $builder->select('categories.category_name, products.product_name');
        $builder->join('products', 'categories.category_id = products.category_id', 'left');
        $builder->orderBy('categories.category_name');
        return $builder->get()->getResultArray();
    } 


In Your Controller:

PHP Code:

public function showCategoriesWithProducts() {
    $categoryModel = new CategoryModel();
    $data['results'] = $categoryModel->getCategoriesWithProducts();
    return view('categories_view', $data);
} 


And In your View:

PHP Code:

<?php foreach ($results as $row): ?>
    <h2><?= $row['category_name']; ?></h2>
    <p><?= $row['product_name']; ?></p>
<?php endforeach; ?>

In bocca al lupo!