Cake newbie. Please help me with database selection from dropdown menu

Hey there everyone,

Thanks in advance for your help.

I have the following two very basic database tables:

CREATE TABLE categories(
    categoryName VARCHAR(255) NOT NULL,
    categoryDescription TEXT

CREATE TABLE products(
    productName VARCHAR(255) NOT NULL,
    productPrice DECIMAL,
    availableStock INT,
    category_id INT NOT NULL,
    FOREIGN KEY category_id(id)
    REFERENCES categories(id)

In my very basic .ctp file I have the following:

<h1>Available Categories</h1>

<select clubCategory>
<?php foreach ($categories as $category): ?>

    <option><?= $category->categoryName ?></option>

<?php endforeach; ?>

What I would like to do is store that selection, then use it to find the products using the foreign key. What is an elegant way to solve this problem using the CakePHP framework? Is it sensible to use the PRODUCTS database inside the CATEGORIES page?

Thank you again, kindly and sincerely.


If I understand you correctly, what you want to achieve is simply a view page with a list of categories, and when you click a category it should get selected and the products should show up in that same view.

You can use either the Categories/index.ctp or the Products/index.ctp (or a custom ctp) for that; CakePHP doesn’t force you to use one, it depends on the specific logic of your application which one you choose.

Since products belong to categories and categories have many products (given that you have set up the relationships in your models properly), you can simply load product data from inside of a categories action like so

$this->Categories->Products->find( ... )

and vice versa.

So in practice, you could for instance use your products index action to display both the categories and products, and leave the categories index action empty (or use it in a different way). I hope this makes sense!

Thanks for your help so far,

I know this is pretty basic stuff, but if you could hold my hand just a little longer I’d really appreciate it.

I have added two lines to my controller

class CategoriesController extends AppController
    public function displayCategories()
        $categories = $this->Categories->find('all');
        $this->set('categories', $categories);
        $products = $this->Categories->Products->find('all');    // added this line
        $this->set('products', $products);                       // added this line

I don’t think I should be passing ‘all’ to the second find method -I’d only like to display those products of the relevant category:

<form action="#" method="post">
<select name=clubCategory>
    <option value="">Select a club type</option>
    <?php foreach ($categories as $category): ?>
    <option value = $count ><?= $category->categoryName ?></option>
    <?php endforeach; ?>

    <input class="button" type="submit" value="Submit the form"/>


How do I move the selected categories into a foreach loop of products.

Again thank you,

I’ve only been at this a week and moving from C++ to php/MySQL/CakePHP is a huge learning curve for me.

Sure, no problem!

First of all, I’m not sure why you’d want to put your categories in a foreach loop of products?

Let me describe how this type of functionality would practically work most of the time:

Normally, you’d have the list of category options in one div, and the products in another, independent of one another.
When you select a category, the user should be redirected to a different page (i.e. /categories/17 or if you work with slugs something like /running-shoes). It’s preferable to do this with links (or a get form) instad of a post form, since it’s simpler and user friendlier (post form has no url).

The GET parameter in the url of this page (the id “17” or the slug “running-shoes”) would allow us to fetch the appropriate products and select the active category.

So your action could look like this:

class CategoriesController extends AppController
    public function displayCategories ($slug = '') //CakePHP automatically makes the GET param available as the method parameter
        $categories = $this->Categories->find('all');

        $products = $this->Categories->Products->find()->where(['slug' => $slug])->all();

        $this->set(compact('categories', 'products')); //You can use compact() to set many view variables at once

Btw, the slug is just a sort of URL safe alias you can use to create “pretty URLs”:

You could also add a condition in your action to test whether the id/slug is set. If not, you could create a different find function, perhaps something to find the most popular or newest products.

I’ve solved my issue using HTML and PHP, though I am not sure if I’ve violated the principals of MVC.

My page is here.

Thanks again!

As long as it works you should be fine :slight_smile:
Good luck with your project!