Please help me in solving this MYSQL Issue.

@Sinnbeck thanks for your reply. I have updated my question with the controller code and error.

@Tray2 Thank you for your reply. I checked this error and understand it completely, but you know the same data if I give to customer B who has got only 430 items then it is working perfectly.

@Syed1980 There is something wrong with your data, one or more records don't have the order_unq_id. The amount of rows you insert has no limit, other than that the script has a time limit set in php.ini. So check you data and make sure all fields have the value you need.

You should of course validate before attempting to insert the record, it would die more gracefully then.

@Tray2 Yes you are right, there is something wrong but I don't think that would be with my data. Because I use the same data by copying and pasting to another customer who has got fewer items, it works fine.

You said the script has a time limit, what is this time limit and where can I find php.ini in shared hosting?

@Syed1980 That is usually 60 seconds.

I tell you that the data is the issue, MySQL never lies, if it says a value is null then it is null.

You can of course set the order_unq_id to nullable, but I don't recommend it. Do it temporarily then do a this select

SELECT * FROM orders WHERE order_unq_id IS NULL;

Then you will see which row is the problem.

@Tray2 upon checking this on local machine I get this error also "Warning: Unknown: Input variables exceeded 5000. To increase the limit change max_input_vars in php.ini. in Unknown on line 0". Hence, I added this line of code "php_value max_input_vars 50000" in '.htaccess' file. However, I still do not understand what it is and why I need to increase this max_input value.

@Syed1980 There are limitations to how much you can do in one go, I'm guessing you are getting the values from a csv or similar, if so consider chunking the data and not everything at once..

@tykus thanks for your reply. The same 454th iteration data if I give insert it to customer B who has got only 430 items then it is working perfectly. For example, I have the option to delink any of the items. So, let's say If I delinked item 400 then the 454th iteration data is working fine.

@Sinnbeck thanks for your reply. No, it is giving the same error with this code also. I gave this line of code "php_value max_input_vars 50000" in '.htaccess' file and temporary it solved the issue because I don't think its a permanent solution.

@Syed1980 If you have that many fields in your form, you are definately doing something wrong.

But since you never explain what it is you are trying to do, what the data looks like, and what your code looks like, well then we can't help you.

@Tray2 thanks for your reply. In my question, I've mentioned my store code. On my create.orders page user will select the customer name and all linked items with images to the respective customer will get displayed below which are aprx. 1000 items. The total of 19 fields for each item. Now, please let me know If you want more code or details. Thank you again.

@Syed1980 Displaying 19 values per item isn't a problem, and when the user creates an order for an item you only need to store this

orders

• id
• user_id

item_order

• order_id
• item_id

So there is no need to have that many fields in one table.

@Tray2 Yes I can understand what you are saying, but the logic behind the application is a bit complicated. However, I manage to cut down the colums from 16 to 7 (+ 3 more, for id, created and updated at) as these are mandatory fields as per the application logic.

@Syed1980 Read this, and apply it to your database model.

https://tray2.se/posts/database-design

@Tray2 thank you so very much for sharing this amazing information. I would love to follow from now on.

I also reduce number of columns to only 6 (+3 default) total of 9 now in my orders table. Now I'm facing issue with inserting the data into database. I shall be greateful if you could help me in that. Can I share my current store function so that you can have a look.

@Syed1980 Share away, but I still don't see the need for that many fields in an orders table.

@Tray2 the reason behind these 6 fields is because I'm displaying data from database in the pdf format.

APPOLOGIES! FOR THIS LONG POST.

My application is for an SME (wholesaler) which deals with different products and supply them to super markets (customers). They have more than 1000 products and will be sold at different price to different customers. Each product will be linked to the customer with the given price. My DB has category, items, customer, linked and orders table. I'm fetching the data from linked table while creating the order, I have an select field to select the customer and images with quantity field, price will get displayed below.

Now, lets assume user selects Customer-A which has 800 items linked to this customer, all 800 items will gets displayed below. User will just enter the quantity for 1 or 10 or all 800 items, given by the customer and say submt.

Keeping this logic I wrote this code, I know that something I'm doing wrong but don't know where.

	public function store(Request $request){
    $linkeds = Linked::all();
    $count_linkeds = request()->session()->get('count_linkeds');
    $item_name_input = [];
    $order_unq_id = $request->all('order_unq_id');
    request()->session()->put('order_unq_id', $order_unq_id);
    session()->save();

    $item_quantity = array_filter($request->input('item_quantity'));
    $qnty_given = count($item_quantity);
    dd('quantity=', $item_quantity, 'quantity given for ', $qnty_given, 'items');
    for ($i = 0; $i < $qnty_given; $i++){
        if($qnty_given > 0) {
            dd('quantity if=', $item_quantity);
            $myitem = array(
                'order_unq_id' => $request->order_unq_id,
                'item_name_input' => 'doormat',
                'item_no_input' => '4325',
                'supplier_barcode_input' => '121212335',
                'item_cost_input' => '2.5',
                'item_quantity' => $item_quantity[$i],
                "created_at" => now(),
                "updated_at" => now()
            );
            DB::table('neworders')->insert($myitem);
        }else{
            // unset($myitem);
        }
    }

    return $this->mht_order_pdf();
    return $this->export();
    return redirect()->route('orders.index')->with('message', 'Order Created Successfull');
}

@Syed1980 This will never work

 return $this->mht_order_pdf();
    return $this->export();
    return redirect()->route('orders.index')->with('message', 'Order Created Successfull');

The last to lines of code will never execute. Once you hit the first return the code stops executing.

@Syed1980 what about this dd('quantity=', $item_quantity, 'quantity given for ', $qnty_given, 'items'); isn't it breaking when it hits this line?

@Tray2 ohh yeah! I got it. How about the 'FOR' loop and 'IF' condition? I think problem is with oher code also.

@msslgomez thanks for your reply. Sorry, I forgot to put '//' before that, it is commented.

@Syed1980 It looks ok, a bit verbose, but ok.

@Tray2 but you know, that code gives me an error while inserting the data.

				"Undefined offset: 0"

I checked with 'dd('quantity=', $item_quantity, 'quantity given for ', $qnty_given, 'items');' it gives me this as result.

NOTE: THE CUSTOMER I HAVE SELECTED HAS GOT 879 ITEMS IN HIS CATALOG AND I GAVE QUANTITY FOR LAST ONLY THREE ITEMS.

				"quantity="
				array:3 [▼
				876 => "2"
				877 => "2"
				878 => "1"
				]
				"quantity given for "
				3
				"items"

I have a second customer with only 1 item in his catalog, when gives the quantity for this item, the code works fine and the order gets inserted. I guess the problem is with the 'FOR' loop.

@Syed1980 Well of course it would give that if the array doesn't have that index.

Your code is not optimal, and the way you have tried to solve your issues is strange and complex.

However since I don't know exactly what you have and what you need you can use the isset method.

if (isset($item_quantity[$i]) {
	$item_quantity  = $item_quantity[$i];
else {
	$item_quantity = 0;
}

$myitem = array.... //The rest of your code.

@Tray2 As you suggested I change the code and it give me the below error.

"Trying to access array offset on value of type int"

		for ($i = 0; $i < $qnty_given; $i++){
        if(isset($item_quantity[$i])){
            $item_quantity  = $item_quantity[$i];
        }else{
            $item_quantity = 0;
        }
        $myitem = array(
            'order_unq_id' => $request->order_unq_id,
            'item_name_input' => 'doormat',
            'item_no_input' => '4325',
            'supplier_barcode_input' => '121212335',
            'item_cost_input' => '2.5',
            'item_quantity' => $item_quantity[$i],
            "created_at" => now(),
            "updated_at" => now()
        );
        DB::table('neworders')->insert($myitem);
    }

@Syed1980 You are still trying to access the value of the array.

Ok, back to basics.

 if(isset($item_quantity[$i])){
            $item_quantity  = $item_quantity[$i]; // This is bad, you are overwriting the the variable.
        }else{
            $item_quantity = 0;
        }

Change to

 if(isset($item_quantity[$i])){
            $itemQuantity  = $item_quantity[$i];
        }else{
            $itemQuantity = 0;
        }

Then here you use the new $itemQuantity variable instead.

$myitem = array(
            'order_unq_id' => $request->order_unq_id,
            'item_name_input' => 'doormat',
            'item_no_input' => '4325',
            'supplier_barcode_input' => '121212335',
            'item_cost_input' => '2.5',
            'item_quantity' => $itemQuantity,
            "created_at" => now(),
            "updated_at" => now()
        );

@Tray2 sorry about my mistake in basics, Thank you.

I tried with the given code, however, the insert is working fine but with some issues. If I select the customer with multiple items and give the quantity for more than 1 item then it inserts the 'quantity' as "0". It works fine and inserts correct data when selecting the second customer who has only 1 item in the catalog.

		for ($i = 0; $i < $qnty_given; $i++){

        if(isset($item_quantity[$i])){
            $itemQuantity  = $item_quantity[$i];
        }else{
            $itemQuantity = 0;
        }

        $myitem = array(
            'order_unq_id' => $request->order_unq_id,
            'item_name_input' => 'doormat',
            'item_no_input' => '4325',
            'supplier_barcode_input' => '121212335',
            'item_cost_input' => '2.5',
            'item_quantity' => $itemQuantity,
            "created_at" => now(),
            "updated_at" => now()
        );
        DB::table('neworders')->insert($myitem);
    }
	

@Syed1980 That is because you are looping over everything every time, you need to rethink your logic, and only loop over what is necessary.

@Tray2 Thank you again, I will try my level best.

@Tray2 Could you please tell me how to write this query for laravel eloquent or raw query?

	SELECT
n.order_unq_id AS order_unq_id,
n.item_name_input AS item_name_input,
n.item_no_input AS item_no_input,
n.supplier_barcode_input AS supplier_barcode_input,
n.item_cost_input AS item_cost_input,
n.item_quantity AS item_quantity,
i.item_unit_measure AS item_unit_measure FROM  neworders n,  items i

I'm trying to join two tables without relation (no relation between neworders & items).

@Syed1980 That is a really bad practice, it is called a carthesian product.

What it does is take every neworders record and joins it with every items record, that mean if you have then items and 100 orders you get a result of 1000 records.

You really should have a many-to-many relationship between orders and items.

1. n.order_unq_id
2. n.item_name_input,
3. n.item_no_input,
4. n.supplier_barcode_input,
5. n.item_cost_input ,
6. n.item_quantity,
7. i.item_unit_measure

Let's go over these.

1. Should be in orders
2. Should be in items
3. Should be in items
4. Should be in items
5. Should be in both items and orders. The price can change for the item but never for the order.
6. Should be in orders
7. Should be in items

@Tray2

		1-  order_unq_id = only in orders
		2- item_name_input = both table items and orders
		3- item_no_input = both table items and orders
		4- supplier_barcode_input = only in orders and another table
		5- item_cost_input  = only in orders and another table
		6- item_quantity = only in orders
		7- item_unit_measure = only in items

@Syed1980

1. Yes
2. No, it should only be in items, you use the relation between orders and items.
3. See 2.
4. See 2, and the other table should have a reference to items,
5. Shou1d be in both items and orders.
6. Correct
7. Correct