Level 75
Have you tried doing dd at various points to verify you're getting what you expect. Also make use of the network tab.
Sep 20, 2020
27
Level 3
DB::Insert - SQL Syntax Error
Hi everyone
I am reading products in from a woocommerce API and saving them to my database. I want to creat a check that says if the product exists then update the price & stock information - If it doesnt exist then create a new product.
Following guidance yesterday the Insert is working nicely but I now can't get the Update SQL syntax correct...
Can anybody help? here's the code:
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
$woocommerce = new Client(
$this->storeUrl,
$this->consumerKey,
$this->consumerSecret,
);
for ($page = 1; $page<2; $page++) {
$products = $woocommerce->get('products',
[
'page' => $page,
'per_page' =>100
]);
foreach($products as $product) {
$stockItem[] = [
'product_code' => $product->sku,
'name' => $product->name,
'description' => $product->description,
'cost_price'=> $product->price,
'list_price' => $product->regular_price,
'stock_quantity' => $product->stock_quantity
];
}
/// ddd($stockItem); // works ok
$item = DB::select('select * from isl_products where product_code = product_code',$stockItem[1]);
//dd($item); // Works Well -> Selects 100 Items From Database that already exist
if(isset($item)) {
$updateItem[] = [
'product_code'=> $product->sku,
'cost_price'=> $product->price,
'list_price' => $product->regular_price,
'stock_quantity' => $product->stock_quantity
];
// dd($updateItem); // working well - > returns array of 1 Item that needs updating
// DB::table('isl_products')->set($updateItem); // This Did not work
DB::update('update isl_products set $updateItem'); // This does not Work
} else {
DB::table('isl_products')->insert($stockItem);
}
}
return view('products.store')->with(['products'=>$products]);
}
Any Help would be very much appreciated
Matt
Level 27
Does DB::table('isl_products')->set($updateItem); throw any errors?
DB::update('update isl_products set $updateItem'); will definitely not work , $updateItem is an array, PHP is going to throw exceptions as it doesn't know how to turn it to a string.
1 like
Level 27
Updates almost always requires an ID to be useful. Do you have something on $item->id? If yes, replace the update code with:
$affected = DB::table('isl_products')
->where('id', $item->id)
->update($updateItem);
1 like
Level 3
Hi lara - thanks for getting back. Yeah the first question
DB::table('isl_products')->set($updateItem);
returned a bad method so I changed it to -
DB::table('isl_products')->update($updateItem);
and got the following error:
Illuminate\Database\QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list' (SQL: update isl_products set 0 = {"id":21756,"cost_price":"8.4","list_price":"8.4","stock_quantity":116})
You may notice I changed the product_code to id because that would make sense on what you were saying in part 2 ...
now dd($updatedItem) gives me:
array:1 [▼
0 => array:4 [▼
"id" => 21756
"cost_price" => "8.4"
"list_price" => "8.4"
"stock_quantity" => 116
]
]
when I run
$affected = DB::table('isl_products')
->where('id', $item->id)
->update($updateItem);
I get the following error :
ErrorException Trying to get property 'id' of non-object
I definitely think you're on to something though - just got to get the syntax right
Level 27
It is weird why $updateItem["id"]exists, it could not be seen from your code.
Anyway, if you have $updateItem["id"], try below:
$affected = DB::table('isl_products')
->where('id', $updateItem["id"])
->update($updateItem);
1 like
Level 3
Hey Lara
Yeah that's my bad ... what you said about using 'id' made sense to me, so I added it to the original $items array... prior to that - I was trying to update off product_code - as that it set as unique in the database.
I've made the change and the result is the same but this time undefined index... I'm wondering if its because the array is in the array and we need to drill to another level ? :/ I just cant get it working..
here's the full code again:
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
$woocommerce = new Client(
$this->storeUrl,
$this->consumerKey,
$this->consumerSecret,
);
for ($page = 1; $page<2; $page++) {
$products = $woocommerce->get('products',
[
'page' => $page,
'per_page' =>100
]);
foreach($products as $product) {
$stockItem[] = [
'id' => $product->id,
'product_code' => $product->sku,
'name' => $product->name,
'description' => $product->description,
'cost_price'=> $product->price,
'list_price' => $product->regular_price,
'stock_quantity' => $product->stock_quantity
];
}
// dd($stockItem);
$item = DB::select('select * from isl_products where product_code = product_code',$stockItem[1]); //error in SQL - Cannot Find Column $stock
//dd($item); // Works Well -> Selects 100 Items From Database that already exist
if(isset($item)) {
$updateItem[] = [
'id'=> $product->id,
'cost_price'=> $product->price,
'list_price' => $product->regular_price,
'stock_quantity' => $product->stock_quantity
];
// dd($updateItem); // working well - > returns array of 1 Item that needs updating
$affected = DB::table('isl_products')
->where('id', $updateItem['id'])
->update($updateItem);
// dd($updateItem);
} else {
DB::table('isl_products')->insert($stockItem);
}
}
return view('products.store')->with(['products'=>$products]);
}
Your desire to help and your patience is very much appreciated - thank you :)
Level 27
@mattbylett Can show the DDL (full structure) of isl_products table?
Level 3
Does the Migration Help? Is that what you mean?
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateIslProductsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('isl_products', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('product_code')->unique();
$table->string('name');
$table->string('description');
$table->integer('cost_price');
$table->integer('list_price');
$table->integer('stock_quantity')->nullable();
});
}
Not sure if I have you're after here :(
Level 27
@mattbylett I sense quite a bit of confusion in concepts based on your earlier code.
See if this gets what you want, then we can clarify any questions you might have.
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
$woocommerce = new Client(
$this->storeUrl,
$this->consumerKey,
$this->consumerSecret,
);
for ($page = 1; $page<2; $page++) {
$woo_products = $woocommerce->get('products',
[
'page' => $page,
'per_page' =>100
]);
foreach ($woo_products as $woo_product) {
$woo_item = [
// 'id' => $woo_product->id,
'product_code' => $woo_product->sku,
'name' => $woo_product->name,
'description' => $woo_product->description,
'cost_price'=> $woo_product->price,
'list_price' => $woo_product->regular_price,
'stock_quantity' => $woo_product->stock_quantity
];
// dd($woo_item);
$db_item = DB::select("select * from isl_products where product_code = {$woo_item["product_code"]} limit 1");
if (isset($db_item)) {
$affected = DB::table('isl_products')
->where('id', $db_item['id'])
->update($woo_item);
} else {
DB::table('isl_products')->insert($woo_item);
}
}
}
return view('products.store')->with(['products'=>$woo_products]);
}
1 like
Level 3
Hey Lara,
Yeah I'm just learning this stuff aye ? :) absolutely loving it but got a loooonng way to go before it makes total sense to me!
Your code is much cleaner than mine I see that... I run into a problem at the $db_item throws the following error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'C1419' in 'where clause' (SQL: select * from isl_products where product_code = C1419-4 limit 1)
I tried to dd($db_item) and got the same error...
Is it meant to be this hard lol ? and always with the SQL :/
Level 27
@mattbylett my bad, add in the quotes to become $db_item = DB::select("select * from isl_products where product_code = '{$woo_item["product_code"]}' limit 1");
1 like
Level 27
@mattbylett the clues are usually in the errors, but understanding the clues require being more familiar with SQL, PHP and algorithms in general.
1 like
Level 3
yeah thats the problem I'm suffering with - I was baffled as to why the error was with the SELECT statement and not the Update statement because I could see that it was finding the product so why do you need a column when you're just getting the product !
Having being given the answer - now it makes sense :)
Now we're back to square one though and the id is an Undefined index :(
ErrorException
Undefined index: id
I'm so sorry this is being so painful
Level 3
dd($db_item);
array:1 [▼
0 => {#2744 ▼
+"id": 101
+"product_code": "C1419-4"
+"name": "Champion M5 x 20mm C/Sunk Socket Head Cap Screw -10pk"
+"description": "<p>Champion M5 x 20mm C/Sunk Socket Head Cap Screw -10pk</p>"
+"cost_price": 45
+"list_price": 45
+"stock_quantity": 3
}
]
Level 27
this should fix it
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
$woocommerce = new Client(
$this->storeUrl,
$this->consumerKey,
$this->consumerSecret,
);
for ($page = 1; $page<2; $page++) {
$woo_products = $woocommerce->get('products',
[
'page' => $page,
'per_page' =>100
]);
foreach ($woo_products as $woo_product) {
$woo_item = [
// 'id' => $woo_product->id,
'product_code' => $woo_product->sku,
'name' => $woo_product->name,
'description' => $woo_product->description,
'cost_price'=> $woo_product->price,
'list_price' => $woo_product->regular_price,
'stock_quantity' => $woo_product->stock_quantity
];
// dd($woo_item);
$db_item = DB::select("select * from isl_products where product_code = '{$woo_item["product_code"]}' limit 1");
if (count($db_item) > 0) {
$affected = DB::table('isl_products')
->where('id', $db_item[0]->id)
->update($woo_item);
} else {
DB::table('isl_products')->insert($woo_item);
}
}
}
return view('products.store')->with(['products'=>$woo_products]);
}
1 like
Level 3
so close but still no cigar :(
Now it tells me :
Error
Cannot use object of type stdClass as array
Is this a json_decode() situation on affected?
I'll have a play around with this later and let you know how I go - for now you've done enough
Thank you so much for your help Lara.. Whats the best way for me to give you Kudos?
Level 27
Where is json_decode() coming from? Don't make changes until we have this DB issue resolved first.
Set the 2nd param to be true for associative array instead of object.
json_decode(..., true);
Level 3
I didn't know if json_decode() was the answer to the DB issue ... This is one of those scenarios where more knowledge / understanding would lead to better interpretation of the clues...
Right now this is all a bit over my head :(
Level 27
Which line is "Error Cannot use object of type stdClass as array" pointing to?
If $db_item is an array of stdClass, try $db_item[0]->id
Level 27
Sorry to ask in such a manner, are you a developer or a business owner trying to automate stuff?
Level 3
No worries Lara - not a developer unfortunately- wanna be perhaps ! I’m keen to learn as much as I can so I can apply it in my business and help others that struggle ...
I’m just self taught though ... don’t do well with formal education but then again - this laravel stuff has really inspired me to learn more and maybe I’ll revisit something more formal
Level 3
$db_item = DB::select("select * from isl_products where product_code = '{$woo_item["product_code"]}' limit 1");
if (count($db_item) > 0) {
$affected = DB::table('isl_products')
->where('id', $db_item[0]['id']) // This Line again
->update($woo_item);
} else {
DB::table('isl_products')->insert($woo_item);
}
}
Level 3
That did it - You're a legend :)
Here's the final code in case it helps someone else :
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
$woocommerce = new Client(
$this->storeUrl,
$this->consumerKey,
$this->consumerSecret,
);
for ($page = 1; $page<2; $page++) {
$woo_products = $woocommerce->get('products',
[
'page' => $page,
'per_page' =>100
]);
foreach ($woo_products as $woo_product) {
$woo_item = [
// 'id' => $woo_product->id,
'product_code' => $woo_product->sku,
'name' => $woo_product->name,
'description' => $woo_product->description,
'cost_price'=> $woo_product->price,
'list_price' => $woo_product->regular_price,
'stock_quantity' => $woo_product->stock_quantity
];
// dd($woo_item);
$db_item = DB::select("select * from isl_products where product_code = '{$woo_item["product_code"]}' limit 1");
if (count($db_item) > 0) {
$affected = DB::table('isl_products')
->where('id', $db_item[0]->id)
->update($woo_item);
} else {
DB::table('isl_products')->insert($woo_item);
}
}
}
return view('products.store')->with(['products'=>$woo_products]);
}
Thanks so much for all your patience and support lara
Level 27
Ok, let me correct my earlier example
1 like
Level 3
@laracoft It's hard to know which answer to mark as best so I picked the one that solved the issue. Thanks again
Level 3
@laracoft Hi Lara, got a follow up question related to yesterdays thread...
I'm trying to add the image field to the $woo_item but can't seem to access it no matter which way round I try it...
this is the dd($woo_products)
0 => {#1742 ▼
+"id": 21917
+"name": "Champion M5 x 20mm C/Sunk Socket Head Cap Screw -10pk"
+"slug": "champion-m5-x-20mm-c-sunk-socket-head-cap-screw-10pk"
+"permalink": "https://islindustrial.nz/product/champion-m5-x-20mm-c-sunk-socket-head-cap-screw-10pk/"
+"date_created": "2020-09-19T16:42:31"
+"date_created_gmt": "2020-09-19T04:42:31"
+"date_modified": "2020-09-19T16:42:31"
+"date_modified_gmt": "2020-09-19T04:42:31"
+"type": "simple"
+"status": "publish"
+"featured": false
+"catalog_visibility": "visible"
+"description": "<p>Champion M5 x 20mm C/Sunk Socket Head Cap Screw -10pk</p>"
+"short_description": ""
+"sku": "C1419-4"
+"price": "44.5"
+"regular_price": "44.5"
+"sale_price": ""
+"date_on_sale_from": null
+"date_on_sale_from_gmt": null
+"date_on_sale_to": null
+"date_on_sale_to_gmt": null
+"price_html": "<span class="woocommerce-Price-amount amount"><bdi><span class="woocommerce-Price-currencySymbol">$</span>44.50</bdi></span>"
+"on_sale": false
+"purchasable": false
+"total_sales": 0
+"virtual": false
+"downloadable": false
+"downloads": []
+"download_limit": -1
+"download_expiry": -1
+"external_url": ""
+"button_text": ""
+"tax_status": "taxable"
+"tax_class": ""
+"manage_stock": true
+"stock_quantity": 3
+"stock_status": "instock"
+"backorders": "no"
+"backorders_allowed": false
+"backordered": false
+"sold_individually": false
+"weight": ""
+"dimensions": {#1747 …3}
+"shipping_required": true
+"shipping_taxable": true
+"shipping_class": ""
+"shipping_class_id": 0
+"reviews_allowed": false
+"average_rating": "0.00"
+"rating_count": 0
+"related_ids": array:5 [ …5]
+"upsell_ids": []
+"cross_sell_ids": []
+"parent_id": 0
+"purchase_note": ""
+"categories": array:3 [ …3]
+"tags": array:1 [ …1]
+"images": array:1 [ …1]
+"attributes": []
+"default_attributes": []
+"variations": []
+"grouped_products": []
+"menu_order": 0
+"meta_data": array:1 [ …1]
+"_links": {#1755 …2}
}
Based on our solution from yesterday I tried:
foreach ($woo_products as $woo_product) {
$woo_item = [
// 'id' => $woo_product->id,
'product_code' => $woo_product->sku,
'name' => $woo_product->name,
'description' => $woo_product->description,
'cost_price'=> $woo_product->price,
'list_price' => $woo_product->regular_price,
'stock_quantity' => $woo_product->stock_quantity,
'excerpt' => $woo_product->short_description,
'product_image' => $woo_product['images']->src // This is the Problem Line
];
This returned the following error:
Error Cannot use object of type stdClass as array
So I tried ....
$woo_item = [
// 'id' => $woo_product->id,
'product_code' => $woo_product->sku,
'name' => $woo_product->name,
'description' => $woo_product->description,
'cost_price'=> $woo_product->price,
'list_price' => $woo_product->regular_price,
'stock_quantity' => $woo_product->stock_quantity,
'excerpt' => $woo_product->short_description,
'product_image' => $woo_product->images['src'] // I thought this would work - in fact this was my first attempt really - then remembered our solution yesterday
];
and got :
ErrorException Undefined index: src
Am I missing something stupid ? I've tried storing the image url as a string and an image and I get the same error
Thanks in advance for any guidance Matt :)
Level 27
images is an array, You have to write it as $woo_product->images[0]["src"].
It would be good if you start a new discussion and reference it to this question. :)
Please or to participate in this conversation.