Following this previous question where I forgot to specify that the array could return several products (productnameID) which can be produced by different laboratories, but which was updating the field 'laboratory' of table 'orders_detail' with the first laboraty returned by the query. Now I have changed a few things :
**Previous and actual script**
/* POPULATE the laboratory FIELD */
DB::table('orders_detail')->where('id',$id)->first();
/* QUERY TO BIND THE PRODUCTTYPEID TO A LABORATORY : select laboratories.laboratory FROM orders_detail LEFT JOIN laboratories ON orders_detail.producttypeID = laboratories.id where orders_detail.orderID = $rowID | Instead of the productnameID I'm using now the producttypeID which is appropriate. Eacch producttype belongs to one unique laboratory */
$laboratory = DB::table('orders_detail')
->join('laboratories', 'orders_detail.producttypeID', '=', 'laboratories.id')
->select('laboratories.laboratory')
->where('orderID', '=', $id)
->get();
dd($laboratory) generates following :
Collection {#3584 ▼ #items: array:2 [▼ 0 => {#3581 ▼ +"laboratory": "Cuisine" } 1 => {#3582 ▼ +"laboratory": "Boulanger" } ] }
I have created an order with 2 products so I get the appropriate laboratory for each product "laboratory":
"Cuisine" and "laboratory": "Boulanger" even if I have here a strange format with "laboratory": which I don't need, same for the double quotes wrapping the laboratory name.
Now I need to update my two rows with the same orderID:
/* UPDATE THE laboratory FIELD WITH THE RETURNED VALUE */
DB::table('orders_detail')->where('orderID', '=', $id)
->update(['laboratory'=> $laboratory]);
But of course with the above actual query I get this :
id | orderID | producttypeID | productnameID | laboratory
225 | 206 | 4 | 26 | [{"laboratory":"Cuisine"},{"laboratory":"Boulanger"}]
226 | 206 | 1 | 1 | [{"laboratory":"Cuisine"},{"laboratory":"Boulanger"}]
Instead of this :
id | orderID | producttypeID | productnameID | laboratory
225 | 206 | 4 | 26 | Cuisine
226 | 206 | 1 | 1 | Boulanger
I need now being able to assign the appropriate laboratory to to the appropriate row :
Product '26' belongs to producttypeID '4' and producttypeID '4' is produced in the laboratory 'Cuisine'
Product '1' belongs to producttypeID '1' and producttypeID '1' is produced in the laboratory 'Boulanger'
Table 'laboratories' :
id | laboratory
1 | Boulanger
2 | Chocolat
3 | Magasin
4 | Cuisine
"Rule" is simple : productypeID = laboratories.id, so if producttypeID is 4 than this producttype is produced in laboratory 4 which is Cuisine.
Would appreciate your expertise here again since I'm stuck and I don't know how to build my update query with a foreach loop (I guess). Thanks in advance, cheers, Marc
