Supertex Posted January 4, 2014 Posted January 4, 2014 I'm not even sure how to phrase a web search for this, and it's beyond my knowledge. I've been using the following code to do partial-match name edits to some of my products: <?php $mysqli = new mysqli("dbaddress", "dbuser", "dbpass", "dbname"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } $res = $mysqli->query("SELECT products_id, products_name FROM products_description WHERE products_name LIKE 'target_text%%'"); while ($row = $res->fetch_assoc()) { $new_name = preg_replace('/target_text/', 'replacement_text', $row['products_name']); if (!$mysqli->query("UPDATE products_description SET products_name = '". $new_name ."' WHERE products_id = '". $row['products_id'] ."' LIMIT 1")) { echo "MySQL Error: (" . $mysqli->errno . ") " . $mysqli->error; } echo "Updated Product #: <b>". $row['products_id'] ."</b><br />\n\tOld Name: ". $row['products_name'] ."<br />\n\tNew Name: ". $new_name ."<br />\n"; } ?> However, this doesn't suit my current need. I've gone and added a bunch of duplicate products - to preserve certain values in the product names, and the increments in the SKU (product model), then I set the first 3 numerals of my six-digit SKU to differentiate categories, but of course the model field isn't in the products_description table, so I have several categories of products with partially incorrect names/descriptions, and no "handle" by which to manipulate them in phpMyAdmin. I'd like to be able to mass edit the names appropriately, but I don't know how to use p.products_model to select pd.products_name. I assume it can be done, as both tables have 'product_id'. Anyone care to lend expertise here? osC v2.3.1 MySQL v8.0.32 PHP v5.6.40 Installed addons: . Attribute Sets Plus .. Create Account & Manual Order Maker .. Customer Testimonials 2.3.4 .. Customer Blacklist .. Dynamic Info Pages .. FedEx Web Svcs v9 .. Filtered Sales Report .. Generic Box .. Google XML Sitemap SEO .. Maximum Order Value .. Modular Front Page .. Monthly Sales & Tax Report .. Multiple Products Manager .. Must Accept Terms & Conditions .. Order Editior .. PDF Customer Invoice .. Price in Cart Only .. Product Sort/Order .. Product Sort in Cart .. Quantity Discounts .. Restrict Delivery Methods .. SEO Header Tags - Reloaded .. Separate Pricing Per Customer .. Simpler Admin Session Length Control .. Sitemap SEO .. Show Free Ship + Modules .. Specials by Category for SPPC .. Store Mode (open|closed|maintenance) .. Store Pickup Shipping .. Theme Switcher .. Ultimate SEO URLs 5 Pro .. UPS XML Rates & Svcs 1.4 .. USPS methods 7.3.1 .. Who's Online Dashboard . Fixes: Add to cart -> 'product not found' : FIX Login issues with IE 11 : FIX Tools: Incredibly Handy: osC Xref
burt Posted January 4, 2014 Posted January 4, 2014 SELECT pd.products_name, p.products_model FROM products p, products_description pd WHERE p.products_id = pd.products_id AND p.products_model LIKE "target_text%" AND pd.language_id = 1 Untested.
Supertex Posted January 4, 2014 Author Posted January 4, 2014 @@burt Thanks for the quick response. That statement appeared to return the correct data in phpMyAdmin. But when replacing the file's original query, and called it in a browser it -looked- like it worked; that is, it echoed back correctly, showing that changes were made, but it hadn't actually changed anything - catalog remained unaltered. I added p.products_id and pd.products_id to the "select" statement, and it worked. <?php $mysqli = new mysqli("dbaddress", "dbuser", "dbpass", "dbname"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } $res = $mysqli->query("SELECT pd.products_name, pd_products_id, p.products_id, p.products_model FROM products p, products_description pd WHERE p.products_id = pd.products_id AND p.products_model LIKE 'target_value' AND pd.language_id = '1'"); while ($row = $res->fetch_assoc()) { $new_name = preg_replace('/old_text/', 'new_text', $row['products_name']); if (!$mysqli->query("UPDATE products_description pd SET products_name = '". $new_name ."' WHERE products_id = '". $row['products_id'] ."' LIMIT 1")) { echo "MySQL Error: (" . $mysqli->errno . ") " . $mysqli->error; } echo "Updated Product #: <b>". $row['products_id'] ."</b><br />\n\tOld Name: ". $row['products_name'] ."<br />\n\tNew Name: ". $new_name ."<br />\n"; } ?> Thank you, thank you, thank you. osC v2.3.1 MySQL v8.0.32 PHP v5.6.40 Installed addons: . Attribute Sets Plus .. Create Account & Manual Order Maker .. Customer Testimonials 2.3.4 .. Customer Blacklist .. Dynamic Info Pages .. FedEx Web Svcs v9 .. Filtered Sales Report .. Generic Box .. Google XML Sitemap SEO .. Maximum Order Value .. Modular Front Page .. Monthly Sales & Tax Report .. Multiple Products Manager .. Must Accept Terms & Conditions .. Order Editior .. PDF Customer Invoice .. Price in Cart Only .. Product Sort/Order .. Product Sort in Cart .. Quantity Discounts .. Restrict Delivery Methods .. SEO Header Tags - Reloaded .. Separate Pricing Per Customer .. Simpler Admin Session Length Control .. Sitemap SEO .. Show Free Ship + Modules .. Specials by Category for SPPC .. Store Mode (open|closed|maintenance) .. Store Pickup Shipping .. Theme Switcher .. Ultimate SEO URLs 5 Pro .. UPS XML Rates & Svcs 1.4 .. USPS methods 7.3.1 .. Who's Online Dashboard . Fixes: Add to cart -> 'product not found' : FIX Login issues with IE 11 : FIX Tools: Incredibly Handy: osC Xref
♥ecartz Posted January 11, 2014 Posted January 11, 2014 The products_description table is keyed by products_id and language_id. Maybe if (!$mysqli->query("UPDATE products_description pd SET products_name = '". $new_name ."' WHERE products_id = '". $row['products_id'] ."' AND language_id = '1' LIMIT 1")) { echo "MySQL Error: (" . $mysqli->errno . ") " . $mysqli->error; } Line breaks added for readability. Always back up before making changes.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.