Tanvir Alam Tanvir Alam - 1 year ago 51
SQL Question

PHP/MySQL table update issue while checking condition in column

enter image description here

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,

echo $actual_link."-".$real_product_id."=".$tblCompInfo->product_id."<br>";


if it was a local server, I would have update my PHP.ini with more memory and do the job.

However, I have a LIVE server and it has to be done in the live server and I have no control or power over PHP.ini.

What to do? How can I do it easily that I will not get a memory issue?

Please help if anyone?

Answer Source

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 /compare