Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Easypopulate - product attributes


coolhandluke

Recommended Posts

I have gotten easypopulate.php to work well in my initial test simply uploading some products and categories.

 

Now that I have a comfort level, I wanted to finalize the conversion script I am writing to map my data feed of product info into the easypopulate.php required format.

 

However, I do not see where/how easypopulate provides for product attributes. That is, for each record in my tab delimited file that will be uploaded to my database, shouldn't I be able to define one or more product attributes associated with each product (record)?? I would assume there should be fields like v_products_attributes_1, .... or something?

 

Does easypopulate address the product attribute info? If so, please point me to detailed information about how to accomplish this.

If not, how (and why) are other people using easypopulate to update their dbs.

 

Thanks!!

Link to comment
Share on other sites

April, I have made edits to this and they seem to working. However, I have made other edits and such to clean up the easypopulate.php script that is posted for download. I've never contributed before, so I'm not sure how to circulate the changes I made... I assume I need to document them, and 'undo' other modifications I might have made.

 

I am currently still testing it, so I am not quite ready to share it yet.

 

Basically, it works as follows:

- For each attribute, 3 fields are needed.

1. v_products_options_1_1 option 'type', like 'Size', 'Color', ..

2. v_products_attributes_price_1_1 'signed' price of option

3. v_products_optons_values_1_1 option value name

 

You can have upto 5 attribute 'triplets' per record, per language. If a product would have more than 5 attributes, you can ad a second record for that product with additional attribute triplets. This works because when the script gets to the second record, it will see that record already exists in the db, and simply update that record with the new attributes.

 

The second triplet, for the second attribute, would look like this

v_products_options_1_2

v_products_attributes_price_1_2

v_products_optons_values_1_2

 

The '2' in this case, simply implies this is the 2nd of 5 attributes. It has no meaning once the data is written to the database. However, the first digit, the '1' in this case, indicates which language these attributes are for. That value, in this case the 1, MUST correspond to the language id in your database. So if english were '1' in your database, then these attrs would be for english.

 

The first item tells what type of option you are adding, i.e. color, size, ...

The second is the price, if applicable, to that option, i.e. 20, -10.50, ...

The first bit, if not a number, must be a + or -. It defaults to +

The thrid item is the value, i.e. blue, red, small, large, XL, ...

 

The 'out of the box db' requires updates to the following for:

 

products_attributes

products_options

products_options_values

products_options_to_products_options_values

 

So the edit simply looks for the attribute type in products_options. If it isn't there, it creates it, else nothing

Same with products_options_values

Then, if necessary (if either of those was created), a record in the prod_opt_to_prod_opt_val... table is created.

Finally, armed with all three of these values, we look for a products_attributes record. If it exists, then we update the price and price prefix (sign, +-). If it doesn't exist, we create a record with all the required values.

 

As confusing as it might seem if you are not an avid programmer, it is quite simple and I am baffled as to why no one has done this yet?

 

Again, I'm leary about posting it as I'm not sure of the process and I have other edits (I added products_cost to the products table and I support that in easypopulate.php so I can track my costs of goods in the db...don't know why that isn't there either), but perhaps I can send this to you at your own risk, of course.

Link to comment
Share on other sites

Coolhandluke youre the man! I was having the same issues you were. I haven't had enough time to dedicate to it but I can't see how it was never added either. Seems easy enough. I'm gonna play with it today.

-=neo=-

The problem with winning the rat race is at the end you're still a rat.

Link to comment
Share on other sites

The red code is what I added....the black code is what is already there...use it as a reference to know where to add it.

 

Again, not fully tested, but seems to be doing well. Use at your own risk... I take no responsibility for any problems this might create for you....

 

See my earlier post that explains what you need to add to your input file...Its defined in the code here as well in the last section... There is a comment area that outlines it....

 

Good luck!

 

Add stuff in red.....

********************************************************

function walk( $item1 ) {

 

global $filelayout, $filelayout_count, $modelsize;

 

global $active, $inactive, $langcode, $default_these, $deleteit, $zero_qty_inactive;

 

global $epdlanguage_id, $price_with_tax, $replace_quotes;

 

global $default_images, $default_image_manufacturer, $default_image_product, $default_image_category;

 

global $separator, $max_categories;

 

$max_attr = 5; //GSW

 

$max_lng = 5; //GSW

// first we clean up the row of data

 

 

**************************************************

 

Add stuff in red...

**************************************************

 

$theparent_id = $thiscategoryid;

 

$v_categories_id = $thiscategoryid; // keep setting this, we need the lowest level category ID later

 

}

 

}

 

}

 

 

 

$prodexists = ""; //GSW

 

if ($v_products_model != "") {

 

// products_model exists!

 

array_walk($items, 'print_el');

 

 

 

// First we check to see if this is a product in the current db.

 

$result = tep_db_query("SELECT products_id FROM ".TABLE_PRODUCTS." WHERE (products_model = '". $v_products_model . "')");

 

 

 

if (tep_db_num_rows($result) == 0) {

 

// insert into products

 

 

 

$prodexists = "0"; //GSW

$sql = "SELECT MAX( products_id) max FROM ".TABLE_PRODUCTS;

 

$result = tep_db_query($sql);

 

$row = tep_db_fetch_array($result);

 

$max_product_id = $row['max']+1;

 

if (!is_numeric($max_product_id) ){

 

$max_product_id=1;

 

}

 

$v_products_id = $max_product_id;

 

echo "<font color='green'> !New Product!</font><br>";

 

 

 

$query = "INSERT INTO ".TABLE_PRODUCTS." (

 

*******************************************************

 

Add stuff in red.......

*******************************************************

";

 

$result = tep_db_query($query);

 

} else {

 

// existing product, get the id from the query

 

// and update the product data

 

$prodexists = "1"; //GSW

 

$row = tep_db_fetch_array($result);

 

$v_products_id = $row['products_id'];

 

echo "<font color='black'> Updated</font><br>";

 

$row = tep_db_fetch_array($result);

 

$query = 'UPDATE '.TABLE_PRODUCTS.'

 

SET

 

products_price="'.$v_products_price. '"

 

,products_cost="'.$v_products_cost.

 

'" ,products_image="'.$v_products_image;

 

 

 

// uncomment these lines if you are running the image mods

*************************************************************

 

Add stuff in red....

**************************************************************

// end support for Linda's Header Controller 2.0

 

$result = tep_db_query($sql);

 

}

 

}

 

}

 

}

 

//GSW

 

/*

 

Let's put the attribute logic in here

 

First, we need to see if we have attr values and if so

 

we have complete sets

 

We would expect a set of three for each attribute for each

 

language:

 

v_products_options_1_1 name of option 'type'

 

v_products_attributes_price_1_1 'signed' price of option

 

v_products_optons_values_1_1 option value name

 

.

 

.

 

v_products_options_1_n

 

v_products_attributes_price_1_n

 

v_products_optons_values_1_n

 

 

 

To support a second language, it looks like this

 

** HOWEVER, the actual digit representing the language must

 

match the language id in the languages table of the db

 

 

 

v_products_options_2_1

 

v_products_attributes_price_2_1

 

v_products_optons_values_2_1

 

.

 

.

 

v_products_options_2_n

 

v_products_attributes_price_2_n

 

v_products_optons_values_2_n

 

 

The 'max_attr = 5' and 'max_lang = 5 ' statements above could be changed, but probably won't want to.

Based on this, 'n' in the above example could not be greater than 5. If you will have more than 5 attributes for a product, simply create two records for that product in your input file. When the 2nd record is processed, this script will recognize its existence in the database, and thus simply update any values, including the addition of additional attributes.

If you will be supporting more than 5 languages, you'll need to 'up' this setting. I've only tested for a single language, so keep that in mind...

Finally, multiple records for the same product do not have to be sequential, however, your attributes will be added based on the sequential order of these records in your input file.... Therefore, if you want, for example, a size attribute to show up listed as (S, M, L, XL, XXL, XXXL), as opposed to some other way (M, S, XL, L, XXXL, XXL), then enter the record with the first 5 attributes assigned accordingly (in the order you want), and the second record would have a single attribute for size as XXXL...A small point, but relevent.

 

*/

 

for($lng = 1; $lng <= $max_lng; $lng++) {

 

for($attr = 1; $attr <= $max_attr; $attr++) {

 

$prodopt = 'v_products_options_' . $lng . '_' . $attr;

 

$prodoptval = 'v_products_options_values_' . $lng . '_' . $attr;

 

$prodoptprice = 'v_products_attributes_price_' . $lng . '_' . $attr;

 

$prodoptkey = $filelayout[$prodopt];

 

$prodoptvalkey = $filelayout[$prodoptval];

 

$prodoptpricekey = $filelayout[$prodoptprice];

 

 

 

if ($items[$prodoptkey] != '' and $items[$prodoptvalkey] != '') {

 

//echo "<br>$prodopt is $items[$prodoptkey]<br>";

 

//echo "<br>$prodoptval is $items[$prodoptvalkey]<br>";

 

 

 

$sql_opt = "SELECT products_options_id FROM ".TABLE_PRODUCTS_OPTIONS." WHERE products_options_name = '$items[$prodoptkey]' AND language_id = '$lng'";

 

$sql_optval = "SELECT products_options_values_id FROM ".TABLE_PRODUCTS_OPTIONS_VALUES." WHERE products_options_values_name = '$items[$prodoptvalkey]' AND language_id = '$lng'";

 

 

 

// If the value exists, move-on, if not, create it

 

$rsl_opt = tep_db_query($sql_opt);

 

$row_opt = tep_db_fetch_array($rsl_opt);

 

if ($row_opt[products_options_id]) {

 

$products_options_id = $row_opt[products_options_id];

 

}

 

else {

 

// Not auto-incremented, so lets get the next id

 

$sql = "SELECT products_options_id FROM ".TABLE_PRODUCTS_OPTIONS." WHERE language_id = '$lng' ORDER BY products_options_id DESC LIMIT 1";

 

$rsl = tep_db_query($sql);

 

$optid = tep_db_fetch_array($rsl);

 

if ($optid[products_options_id]) {

 

$optid[products_options_id]++;

 

$products_options_id = $optid[products_options_id];

 

}

 

else {

 

$optid[products_options_id] = 1;

 

$products_options_id = $optid[products_options_id];

 

}

 

unset($query);

 

$query = "INSERT INTO ".TABLE_PRODUCTS_OPTIONS." (

 

products_options_id,

 

language_id,

 

products_options_name)

 

VALUES (

 

$optid[products_options_id],

 

$lng,

 

'$items[$prodoptkey]')";

 

$rsl = tep_db_query($query);

 

}

 

 

 

$rsl_optval = tep_db_query($sql_optval);

 

$row_optval = tep_db_fetch_array($rsl_optval);

 

if ($row_optval[products_options_values_id]) {

 

$products_options_values_id = $row_optval[products_options_values_id];

 

}

 

else {

 

// Not auto-incremented, so lets get the next id

 

$sql = "SELECT products_options_values_id FROM ".TABLE_PRODUCTS_OPTIONS_VALUES." WHERE language_id = '$lng' ORDER BY products_options_values_id DESC LIMIT 1";

 

$rsl = tep_db_query($sql);

 

$optvalid = tep_db_fetch_array($rsl);

 

if ($optvalid[products_options_values_id]) {

 

$optvalid[products_options_values_id]++;

 

$products_options_values_id = $optid[products_options_values_id];

 

}

 

else {

 

$optvalid[products_options_values_id] = 1;

 

$products_options_values_id = $optid[products_options_values_id];

 

}

 

unset($query);

 

$query = "INSERT INTO ".TABLE_PRODUCTS_OPTIONS_VALUES." (

 

products_options_values_id,

 

language_id,

 

products_options_values_name)

 

VALUES (

 

$optvalid[products_options_values_id],

 

$lng,

 

'$items[$prodoptvalkey]')";

 

$rsl = tep_db_query($query);

 

}

 

 

 

// Ok, we've got an opt and an opt val and either

 

// verfied it exists or created it in the db

 

// Now, we create the do the same for the prod_attr and

 

// create the prod_opt_to_prod_opt_val record

 

if ($products_options_id and $products_options_values_id) {

 

$sql = "SELECT * FROM ".TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS." WHERE products_options_id = '$products_options_id' AND products_options_values_id = '$products_options_values_id'";

 

$rsl = tep_db_query($sql);

 

unset($row);

 

$row = tep_db_fetch_array($rsl);

 

if($row) {

 

//echo "found prodopttoprodoptval rec<BR>";

 

}

 

else {

 

unset($query);

 

$query = "INSERT INTO ".TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS." (

 

products_options_id,

 

products_options_values_id)

 

VALUES (

 

'$products_options_id',

 

'$products_options_values_id')";

 

$rsl = tep_db_query($query);

 

}

 

}

 

// If a new product, just update prod_attr,

 

if ($prodexists) {

 

$sql = "SELECT * FROM ".TABLE_PRODUCTS_ATTRIBUTES." WHERE options_id = '$products_options_id' AND options_values_id = '$products_options_values_id' AND products_id = '$v_products_id'";

 

$rsl = tep_db_query($sql);

 

unset($row);

 

$row = tep_db_fetch_array($rsl);

 

if ($row) {

 

// We found a record,so lets update the price

 

$opt_price = "";

 

$opt_prefix = "";

 

if ($items[$prodoptpricekey]) {

 

$bit1 = substr($items[$prodoptpricekey],0,1);

 

if ($bit1 == "-" or $bit1 == "+") {

 

$opt_prefix = $bit1;

 

$opt_price = substr($items[$prodoptpricekey],1);

 

}

 

else {

 

$opt_prefix = "";

 

$opt_price = $items[$prodoptpricekey];

 

}

 

}

 

unset($query);

 

$query = "UPDATE ".TABLE_PRODUCTS_ATTRIBUTES."

 

SET options_values_price = '$opt_price', price_prefix = '$opt_prefix' WHERE options_id = '$products_options_id' AND options_values_id = '$products_options_values_id' AND products_id = '$v_products_id'";

 

$rsl = tep_db_query($query);

 

}

 

else {

 

// No rec, so set our flag so we will force

 

// a record to be created

 

$prodexists = 0;

 

}

 

}

 

if (!$prodexists) {

 

$opt_price = "";

 

$opt_prefix = "";

 

if ($items[$prodoptpricekey]) {

 

$bit1 = substr($items[$prodoptpricekey],0,1);

 

if ($bit1 == "-" or $bit1 == "+") {

 

$opt_prefix = $bit1;

 

$opt_price = substr($items[$prodoptpricekey],1);

 

}

 

else {

 

$opt_prefix = "";

 

$opt_price = $items[$prodoptpricekey];

 

}

 

}

 

unset($query);

 

$query = "INSERT INTO ".TABLE_PRODUCTS_ATTRIBUTES." (

 

products_id,

 

options_id,

 

options_values_id,

 

options_values_price,

 

price_prefix)

 

VALUES (

 

'$v_products_id',

 

'$products_options_id',

 

'$products_options_values_id',

 

'$opt_price',

 

'$opt_prefix')";

 

$rsl = tep_db_query($query);

 

}

 

} // if both attr vals passed

 

} //for attr

 

} //for lang

 

// GSW--END Attributes Section

 

 

 

if (isset($v_categories_id)){

 

//find out if this product is listed in the category given

 

$result_incategory = tep_db_query('SELECT

 

'.TABLE_PRODUCTS_TO_CATEGORIES.'.products_id,

 

'.TABLE_PRODUCTS_TO_CATEGORIES.'.categories_id

 

FROM

**************************************************************

Link to comment
Share on other sites

so i am guessing we will still need to add

 

v_products_options_1_(1-5) to the first part easypopulate.php around lines (335-352)

 

What other changes are nessassary? can you include your complete easypopulate.php file or PM me. I have time during the next several days to get this working.

Link to comment
Share on other sites

You do not need to add those items around lines 335... It works for me without that. I believe that is the default file layout...not real sure. That is why I was hesitant to post. The script, as you know, does several things, like upload files, give you default layouts, froogle, etc.... I didn't (yet) adapt the script to support those functions, only the actual part where easypopulate writes from your local input file to the database.

Link to comment
Share on other sites

I see. I guess the road block on my end is when I'm the admin

 

and click on "Download Complete tab-delimited .txt file to edit" it is missing the atribute fields in the download file.

 

Thats why I was thinking I need to that some extra code.

 

Perhaps what is missing is the FILE LAYOUT CODE

 

Around the file layout section (lines 960-1000) to see these fields in the downloaded .txt file?

Link to comment
Share on other sites

Probably correct.... I've never used that option. My intention, whenever I can make some time, is to review the 'other' functionality of the script and make sure I supported that, so hopefully the changes could be incorporated into the program. I just haven't been able to do it yet. But I suspect what you are suggesting is correct.

Link to comment
Share on other sites

I found the only way to do this was to export a file from easypopulate and then add the fields before an upload. We saved one of the files with all the data cleared and use it for the upload template. You will not see the results in your download file since the code for the file layout does not include it. You she see the results in your attributes table and in the product listings.

Ron

If we knew it all we wouldn't be here would we?

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...