Fetching Data from Database and Looping through Results
As a developer working with e-commerce platforms like Magento, you often find yourself needing to fetch data from the database and loop through it to perform updates or modifications. In your recent project involving price updates using CSV data, you’ve encountered a challenge related to matching SKUs between the CSV file and the database.
Understanding the Problem
Your current script successfully retrieves data from the CSV and looks up corresponding products in Magento by performing a LIKE search based on the SKU. However, the issue arises when multiple products with similar but not identical SKUs need to be updated for each row in the CSV.
For example:
- The CSV contains an SKU like
1234. - In Magento, you have products with SKUs like
001234_01,001234_02,001234_03, etc., that need to be updated with the information from this row.
Currently, your script only updates one product and then moves on to the next row in the CSV. To solve this, you need to modify the _updatePrices function to loop through all matching products before processing the next row.
Solution Approach
- Fetching Matching Products
First, let’s look at how we can fetch all the products that match a given SKU pattern using a LIKE query:
function _checkIfSkuExists($sku){
$connection = _getConnection('core_read');
$sql = "SELECT * FROM " . _getTableName('catalog_product_entity') . " WHERE sku LIKE '%$sku%'";
return $connection->fetchAll($sql, array($sku));
}
This function returns an array of products where the SKU matches the provided pattern.
- Looping through Matching Products
In your main processing loop, you should check if there are any matching products for a given CSV row:
$skuRows = _checkIfSkuExists($_data[0]);
if(0 < sizeof($skuRows)){
// okay you have records, let's iterate through them
foreach ($skuRows as $row) {
try{
// modify your _updatePrices so as to use record ID from $row and CSV data from $data both
_updatePrices($_data, $row);
} catch(Exception $e){
// exception warning
}
}
}else{
// no records warning
}
Here, if there are matching products (sizeof($skuRows) > 0), you loop through each product using a foreach statement. For each product, you call the _updatePrices function with both the CSV data (using $_data) and the current product record (using $row).
- Modifying the _updatePrices Function
The _updatePrices function needs to be updated to accept both the CSV data and the current product record:
function _updatePrices($csvData, $productRow){
// Rest of your existing code...
$newPrice = $csvData[4];
$specialPrice = $csvData[6];
$status = $csvData[10];
$productId = $row['entity_id'];
$attributeId = _getAttributeId();
// Rest of your existing update queries...
}
In this updated version, you extract the required data from both the $csvData array and the current product row. You then use these values to perform the necessary updates in the database.
Conclusion
To solve your issue with updating multiple products for each CSV row, you need to:
- Fetch all matching products using a
LIKEquery on the SKU. - Loop through the matching products for each CSV row.
- Update the
_updatePricesfunction to accept both the CSV data and the current product record.
By following this approach, you can ensure that your script correctly updates all relevant products for each row in the CSV file before moving on to the next row.
Remember to handle exceptions appropriately to prevent any updates from failing silently. Always double-check your queries and update statements to ensure they are performing the intended operations without causing unintended side effects.
For more information on working with databases and looping structures in PHP, you can refer to the Laravel Company blog, which covers a wide range of development topics.