Multiple Stock Columns

Could you please give more information about your database design? How are your product attributes stored? What are the relations?

http://cl.ly/image/0k0M313C1q2S shows the table

So if i sold size 20 the 'stock' column would need to be minus 1 to 0 if i sold size 18 then again stock_1 would be minus 1 to 0

Wouldn't it be better to split this into multiple tables like one for sizes, one for stocks and one for prices?

There are a lot of possibilities to answer this. The question is what do you get from your form when somebody buys a product. If you get "size_4" you can substring the size "4" and do:

// You have "4" as $size and 12 as product ID from you form
$product = Product::find(12);
// Don't really sure if it works, maybe you have to replace this by $product->stock_$size = $product->stock_$size - 1;
$product->stock_$size--;
$product->save();

But imo this really is dirty. You should split information in your database to get a more cleaner way. Then you could do sth like:

// $this->product has been injected by using Laravels dependency injection in your constructor (its just a repository)
$this->product->decreaseStockforSize(4);

Yes each form element has a name of size_1 then has stock_1 size_2 then stock_2 relevant to what they are buying. so need to minus whichever is bought.

Still having trouble with this, not updating the right stock item, what @bart said doesn't work and am not sure how i can get the the right stock value to update it as it's all in the same db row.

Would it be better if i created 3 new migrations like so:

Schema::create('product_colours', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('product_id');
            $table->string('colour');
            $table->string('colour_1');
            $table->string('colour_2');
            $table->string('colour_3');
            $table->string('colour_4');
            $table->string('colour_5');
            $table->string('colour_6');
        });

Schema::create('product_sizes', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('product_id');
            $table->string('size');
            $table->string('size_1');
            $table->string('size_2');
            $table->string('size_3');
            $table->string('size_4');
            $table->string('size_5');
            $table->string('size_6');
        });

Schema::create('product_stock', function(Blueprint $table)
        {
            $table->increments('id');
            $table->integer('product_id');
            $table->string('stock');
            $table->string('stock_1');
            $table->string('stock_2');
            $table->string('stock_3');
            $table->string('stock_4');
            $table->string('stock_5');
            $table->string('stock_6');
        });

Would it make it better to use relationships then subtracting stock would be easier to do?

Your schema design is not normalized, You can at least create 2 table: Products and ProductProperties

Products
  id
  name


ProductProperties
  id
  product_id
  size
  color
  stock

You can even normalize it further.

But there is more than one size, color and stock

@bashy suggestions ?

Sure every record in the Products table has many records in the ProductsProperties table,

Shouldn't you have a pivot for attached sizes and colours for each product?

@bashy - Thats the question, i'm looking for the best way to do this as you can see from the schema above there's multiple stock, size and colours for each product, so wondering the best way.... Advice?

As I already told you splitting the table into multiple one like you did by creating 3 migrations should be perfect. But as @arabsight said the normalized way are two tables like products and properties. Then you can fetch the corresponding entry by ID and decrease its stock.

What does 'normalized way are two tables like products and properties' mean whats a normalized way?

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table ) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.

Its like design patterns for databases. First Normal Form: eliminate duplicative columns from the same table. the Size1-Size4, ... columns are duplicative.

They arent though they are all unqiue entries

Normalized in that case means that you don't have empty fields (simply spoken). Every size-color pair has a stock amount. So you can do something like this:

$properties = Product::with('properties')->whereProductId($productId)->whereSize('s')->whereColor('black');
$properties->stock--;
$properties->save();

So doing the schema i suggested would work then is that what you telling me?

But @lstables you can create a new entry for each set:

// products table
id     name
1      tshirt
2      socks
3      shoes

// properties table
id     product_id           size     color     stock
1       1                    S         black     4
2       2                    41       blue       7
3       1                    M        black     1
4       1                    M        green     0
5       3                    44        blue      10
6       3                    41        orange  1
7       2                    39        yellow  1

Nope what we try to tell you is that you have to normalize your database table to have a more cleaner, performant and more simple to use one! Then you can use a method like the one I suggested above.

Normalizing will make your life easier (adding new sizes and colours will be easy and you will not need to alter the table schema).

@bart I see now, yes makes sense that bloody hell, been having a complete brain freeze and getting mixed up but that is more efficient and makes sense.

Ok that works now but the form within the CMS has multiple options to add more than one product so how would I add that to this new product_options table.

Current code:

$option = new Options;
        $option->product_id = $product->id;
        $option->size = Input::get('size');
        $option->colour = Input::get('colour');
        $option->stock = Input::get('stock');
        $option->save();

But yet the form looks like so: http://cl.ly/image/1y0e050K1a22 so ideally needs to loop through and add the same product id mulitple time based on what's input any ideas @bart

Anyone got an idea on my last post?

if i dd() my inputs i get:

'size' => string '6' (length=1)
  'colour' => string 'Black' (length=5)
  'stock' => string '2' (length=1)
  'size_1' => string '10' (length=2)
  'colour_1' => string 'Cream' (length=5)
  'stock_1' => string '3' (length=1)
  'size_2' => string '0' (length=1)
  'colour_2' => string '0' (length=1)
  'stock_2' => string '' (length=0)
  'size_3' => string '0' (length=1)
  'colour_3' => string '0' (length=1)
  'stock_3' => string '' (length=0)
  'size_4' => string '0' (length=1)
  'colour_4' => string '0' (length=1)
  'stock_4' => string '' (length=0)
  'size_5' => string '0' (length=1)
  'colour_5' => string '0' (length=1)
  'stock_5' => string '' (length=0)
  'size_6' => string '0' (length=1)
  'colour_6' => string '0' (length=1)
  'stock_6' => string '' (length=0)
  'status' => string '0' (length=1)

so i need it to insert into product_options twice on this occasion has size and size_1, colour & colour_1 and stock & stock_1 have values

Ideally using this format and check to make sure its not empty or equals '0'

$option = new Options;
        $option->product_id = $product->id;
        $option->size = Input::get('size');
        $option->colour = Input::get('colour');
        $option->stock = Input::get('stock');
        $option->save();

So how would one achieve this ?

Haven't read all of this but to insert it only if it has a value is via the Input::get() default?

Input::get('size', null);

Or you can do some checks?

(Input::get('size') ?: null);

Just just inserting if as value but to insert multiple times with same product_id @bashy

Its inserting the 1st result twice instead of the result been different values like i show above in the dd() but retain the same product_id

Do a foreach to make sure it works, then move it into a Product method so you can pass data to it and it will do it without the repeated code?

$product->addProductItems();

foreach ($items as $item)
{
    if ($foo == '')
    {
        //
    }
}

Yeah I'm lost, not sure what crazy functions you've got going on there :D

Not sure I follow?