Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Possible to alter pd.products_name via p.products_model


Supertex

Recommended Posts

Posted

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?

Posted

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.

Posted

@@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.

Posted

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...