Populate Dropdown based on previous selection

How are you storing your stock? What does your database schema look like?

This is my Products model:

class Products extends \Eloquent {

    protected $guarded = ['id'];

    public function categories()
    {
        return $this->belongsTo('Categories');
    }

    public function options()
    {
        return $this->hasMany('Options','product_id', 'id');
    }

    public function availableColours()
    {
        return $this->options()->select('colour')->distinct()->where('stock', '=', 1)->lists('colour');
    }

    public function availableSizes()
    {
        return $this->options()->select('size')->distinct()->where('stock', '=', 1)->lists('size');
    }

    public function availableStock()
    {
        return $this->options()->select('stock')->distinct()->where('stock', '=', 1)->lists('stock');
    }


}

I have a products_options table to store stock, size and colours of each item like so:

CREATE TABLE `product_options` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `size` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `colour` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `stock` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=409 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Products tables schema is

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `seo_title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `seo_description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `content` text COLLATE utf8_unicode_ci NOT NULL,
  `image` text COLLATE utf8_unicode_ci NOT NULL,
  `status` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `slug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `orig_image` text COLLATE utf8_unicode_ci,
  `image2` text COLLATE utf8_unicode_ci,
  `image3` text COLLATE utf8_unicode_ci,
  `orig_image2` text COLLATE utf8_unicode_ci,
  `orig_image3` text COLLATE utf8_unicode_ci,
  `price` float(10,2) DEFAULT NULL,
  `category_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=194 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Then within my view i'm doing the following:

 {{ Form::open(['url' => 'add_to_cart']) }}
                <div class="product-details">
                    @if($item->availableColours())
                    Colour:
                    <select name="colour">
                    @foreach($item->availableColours() as $colour)
                    <option value="{{ $colour }}">{{ $colour }}</option>
                            {{ Session::put('selectedColor', $colour) }}
                    @endforeach
                    </select>
                    @endif
                    <br />

                    @if($item->availableSizes())
                    Size:
                    <select name="size">
                        @foreach($item->availableSizes() as $size)
                        <option value="{{ $size }}">{{ $size }}</option>
                        @endforeach
                    </select>
                    @endif
                </div>
                <div class="product-price">£{{ money_format("%i", $item->price) }}</div>
                <div class="product-stock">
                    @if ($item->availableStock())
                        <h3 class="text-success">In Stock</h3>
                    @else
                        <h3 class="text-danger">Out of Stock</h3>
                    @endif

                </div>
{{ Form::close() }}

But if i select black t-shirt but it only has one stock item size m in black that's what needs to show once I select black for example. So i select black but its only available in size m then size m is what should show in the next dropdown menu. Whereas if I selected white t-shirt there's sizes s.m.l available so that would then show in the size dropdown, does that make sense?

Can't do that with just PHP I'm afraid, at least not without another page load. You should use Javascript to populate the second drop-down based on the color selected in the first dropdown.

The idea is :

When someone select a color you'll send a request to a specific route with the ID of that color and return a JSON response

contains the list of t-shirt that are related to that color.

And using jQuery you can parse that JSON response and make a foreach loop to append the result to the second select element.

Yeah. I think you're going to need a javascript solution for this, since Laravel can't adjust the page once it's already been created. You can decide how in-depth you want to get with your javascript. These are the two solutions I thought of off the top of my head:

1. Use your DB data to create a JSON file that would match size stock to color or
2. Create a bunch of select fields and show/hide them based on the color you've selected.

Your JSON could look something like this:

{
     "black": {
         "small": 30,
         "med": 25,
         "large": 0
     },
     "blue": {
         "small": 0,
         "med": 3,
         "large": 22
     },
     "red": {
         "small": 0,
         "med": 0,
         "large": 3
     }
}

and you could populate the fields using something like this (It uses JQuery, but it doesn't have to)

http://jsfiddle.net/99wm9zpL/1/

The above ways are preferred but you CAN do it in PHP (or more of the way of submitting the form on change :P).

With PHP it will require a page refresh, but can be done. I would use form model binding with default set for your color and sizes. When the user changes color they would be submitting the form and return that color with associated sizes. So a query for the default page load, the post to another route return the same view and variables with a different dynamic query.

jQuery or JS I knew I'd have to do, it was more a question of how i can do this based on what I have already.

You have enough information here to start, make the routes for the AJAX and come back with any problems.

Not sure how to make a json file of the data and match it all up based on first selection though :(

Question is how can I create a json file from the DB & save it somewhere within my app.

I can't create a bunch of selects has there's over 200 products and all loaded dynamically.

Response::json. Add the array of data? Basic PHP JSON/array stuff :)

You don't have to create a JSON file, you can just create a route for example :

Route::post('related-sizes', 'SizesController@sizes');

when the user change the select input, you listen for that and post to that route

$.post( "related-sizes", id_of_the_selected_element, function( data ) {
  // here you can parse the result returned from the controller
});

and in your size methode

public function sizes()
{
    $id = Input::get('color');
    // do your query here
    Response::json( $result );
}

@bashy realised when I put that if i just return a $var it's parsed to JSON (smacks head!)

@RachidLaasri think I understand now, so I just need to pass ID's from each select to new methods and do some JS to so this?

A existing track that will help. https://laracasts.com/discuss/channels/general-discussion/populate-dropdownlist-with-array-of-strings-at-the-controller

then i could setup a data-color-id on that select to get the id check that againest some queries, think i got this now, thanks guys for the help and advice.

Your HTML

<select name="colour" id="colors">
    @foreach($item->availableColours() as $colour)
    <option value="{{ $colour }}">{{ $colour }}</option>
    @endforeach
</select>

Your Js code :

<script type="text/javascript">
    $(function(){

        $("#colors").change(function(){

            var value = $(this).val();

            $.post( "related-sizes", value, function( data ) {
              // here you can parse the result returned from the controller and append it to the size select input
            });

        });

    });
</script>

when i user selects an option the code will post to this route

Route::post('related-sizes', 'SizesController@sizes');

Your sizes method :

public function sizes()
{
    $result = // do your query here 
    Response::json( $result );
}

@RachidLaasri LEGEND!

@RachidLaasri so within the JS how do I get back the json then show next select based on previous?

How will I post data back and forth form js to php based on what you said?

Just use a normal form (as POST)? Depends how you're doing it.

Also this may help in populating the select options http://stackoverflow.com/questions/2722709/select-options-from-json

Thinking Ajax might be quicker but not sure how to get data back and forth from php to js then once i hve got it back check it to then show the next select menu

<script type="text/javascript">
    $(function(){

        $("#colors").change(function(){

            var value = $(this).val();

            $.post( "related-sizes", value, function( data ) {
              alert(data);
        // this variable holds the response from your PHP
            });

        });

    });
</script>

Ah right gotcha

you can set all your names as text and id's as value in select options and use jquery-chained plugin . I used it in project and it is great . the only problem is if some parent select is empty .

OMG, really struggling with this, think all my queries are messed up too, anyone fancy giving me a hand?

Post your code

Products Model

class Products extends \Eloquent {

    protected $guarded = ['id'];

    public function categories()
    {
        return $this->belongsTo('Categories');
    }

    public function options()
    {
        return $this->hasMany('Options','product_id', 'id');
    }

    public function availableColours()
    {
        return $this->options()->select('colour')->distinct()->where('stock', '=', 1)->lists('colour');
    }

    public function availableSizes()
    {
        return $this->options()->select('size')->distinct()->where('stock', '=', 1)->lists('size');
    }

    public function availableStock()
    {
        return $this->options()->select('stock')->distinct()->where('stock', '=', 1)->lists('stock');
    }


}

Options Model

class Options extends \Eloquent {

    protected $guarded = ['id'];

    public $table = 'product_options';

    public $timestamps = false;

    public function products()
    {
        return $this->belongsTo('Products','id','product_id');
    }

}

View

{{ Form::open(['url' => 'add_to_cart']) }}
                <div class="product-details">
                    @if($item->availableColours())
                    Colour:
                    <select name="colour">
                    @foreach($item->availableColours() as $colour)
                    <option value="{{ $colour }}">{{ $colour }}</option>
                            {{ Session::put('selectedColor', $colour) }}
                    @endforeach
                    </select>
                    @endif
                    <br />

                    @if($item->availableSizes())
                    Size:
                    <select name="size">
                        @foreach($item->availableSizes() as $size)
                        <option value="{{ $size }}">{{ $size }}</option>
                        @endforeach
                    </select>
                    @endif
                </div>
                <div class="product-price">&pound;{{ money_format("%i", $item->price) }}</div>
                <div class="product-stock">
                    @if ($item->availableStock())
                        <h3 class="text-success">In Stock</h3>
                    @else
                        <h3 class="text-danger">Out of Stock</h3>
                    @endif

                </div>
{{ Form::close() }}

Idea is select a colour, it needs to check for stock levels make sure there's at least 1 stock then check what sizes are available then make this appear another select menu.

Controller method (relating to view posted earlier in post)

public function categories($slug)
    {
        $product = Products::where('slug', '=', $slug)->where('status', '1')->orderBy('name', 'asc')->paginate(10);
        $categories = Categories::where('id','!=','1')->orWhere('id','<>','10')->orWhere('id','!=','11')->get();

        return View::make('site.categories', compact('product','categories'));
    }