I have the above table: tblCompInfo, the product_id value is not 100% accurate and I need to fix it. I have total of 543847 total row with 25 different company and 12 different products.
now, The URL is 100% accurate and as you can see from the image I have highlighted with RED which means they are wrong and GREEN which is what it should be updated to.
I need to update Product_id by parsing through URL and getting the INTEGER and checking it with product table, if its a product, assign the value else assign 0.
I got two solution in my head:
1. EXPORT the entire DATA to EXCEL CVS, change it and UPLOAD it to DATABASE. which means my entire week will be working with EXCEL only.
2. Since I have laravel framework: I can make a function in PHP and get the DATA company wise and UPDATE the table in a foreach loop with condition.
So, to make my life easy, I made the PHP function with a simple solution and it works BUT I get MEMORY ALLOCATION PROBLEM.
$companyID = ??;
$tblCompInfos = tblCompInfo::where('company_id', '=', $companyID)->get();
foreach($tblCompInfos as $tblCompInfo)
$actual_link = $tblCompInfo->url;
$pathlink = parse_url($actual_link, PHP_URL_PATH);
$product_id_from_url = preg_replace("/[^0-9]/", "" , $pathlink);
$FindIfItsInProductTable = Product::find($product_id_from_url);
$real_product_id = $FindIfItsInProductTable == null ? 0 : $product_id_from_url;
DB::table('tblCompInfo')->where('company_id', '=', $companyID)->where('url', '=', $tblCompInfo->url)->update(array(
'product_id' => $real_product_id,
Try this :
UPDATE [table_name] SET product_id = CONVERT(SUBSTR(url, LOCATE('products/', url)+9, LOCATE('/compare',url)-LOCATE('products/', url)+9),UNSIGNED INTEGER)
But this will only works if every
url field has suffix as