coolhandluke Posted October 15, 2003 Share Posted October 15, 2003 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!! Quote Link to comment Share on other sites More sharing options...
radders Posted October 15, 2003 Share Posted October 15, 2003 From what I have read in previous posts it doesn't handle attributes. Quote Link to comment Share on other sites More sharing options...
coolhandluke Posted October 15, 2003 Author Share Posted October 15, 2003 ??? Why would it not? Why would anyone use it if you can only load part of your product data? Is someone using Easypopulate for their file uploads? If so, how are you addressing this problem? Quote Link to comment Share on other sites More sharing options...
Sinister-Racing Posted October 15, 2003 Share Posted October 15, 2003 I have also posted many time regarding this same issue. I dont know why nobody has wondered about this before. That time I checked (Neo) was trying to work on this issue. Quote Link to comment Share on other sites More sharing options...
coolhandluke Posted October 15, 2003 Author Share Posted October 15, 2003 Well, tomorrow I'll be hacking away trying to put this logic in... Perhaps it'll end up being my contribution if I can figure it out. Quote Link to comment Share on other sites More sharing options...
Guest Posted October 22, 2003 Share Posted October 22, 2003 I am having this same problem! so when someone comes up with a fix could you please post again or send me an email ([email protected]) ....I am willing to donate $ to this cause and I am sure others would too...Thank for all you guys do!!!!!! Quote Link to comment Share on other sites More sharing options...
coolhandluke Posted October 22, 2003 Author Share Posted October 22, 2003 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. Quote Link to comment Share on other sites More sharing options...
Guest Posted October 24, 2003 Share Posted October 24, 2003 :lol: Yeah!!! I can't wait ... please send it and I understand it is at my own risk...but I have back up and I would love to see it.... April Quote Link to comment Share on other sites More sharing options...
trillback Posted October 27, 2003 Share Posted October 27, 2003 Please let me know if you have added this to your Easy Populate. I am also needing to add about 10K items, but each item has diffrent attributes. Your changes will be very helpful :) Quote Link to comment Share on other sites More sharing options...
loxly Posted October 29, 2003 Share Posted October 29, 2003 I would love to see if this works, I have had people clammoring for it since the beginning of time.... Quote [no external urls in signatures please, kthanks] Link to comment Share on other sites More sharing options...
coolhandluke Posted October 29, 2003 Author Share Posted October 29, 2003 I'm entrenched at the moment trying to meet some deadlines with this stuff... When I get a chance (hopefully in the next week or two) I will review how to post and see about getting something out there. Quote Link to comment Share on other sites More sharing options...
trillback Posted October 29, 2003 Share Posted October 29, 2003 Can you just post your source code Quote Link to comment Share on other sites More sharing options...
-=neo=- Posted October 29, 2003 Share Posted October 29, 2003 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=- Quote The problem with winning the rat race is at the end you're still a rat. Link to comment Share on other sites More sharing options...
Guest Posted October 30, 2003 Share Posted October 30, 2003 You guys are great...Thank you both :D Quote Link to comment Share on other sites More sharing options...
coolhandluke Posted October 30, 2003 Author Share Posted October 30, 2003 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 ************************************************************** Quote Link to comment Share on other sites More sharing options...
trillback Posted October 30, 2003 Share Posted October 30, 2003 Thanks! I'll give it a try and see what comes up. Quote Link to comment Share on other sites More sharing options...
trillback Posted October 30, 2003 Share Posted October 30, 2003 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. Quote Link to comment Share on other sites More sharing options...
reasonable Posted October 30, 2003 Share Posted October 30, 2003 I would also greatly appreciate a copy of what yo have done or at least the instructions for doing it. Being a non-programmer this all might as well be in plutonian as english. Thanks, Ron [email protected] Quote Ron If we knew it all we wouldn't be here would we? Link to comment Share on other sites More sharing options...
coolhandluke Posted October 30, 2003 Author Share Posted October 30, 2003 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. Quote Link to comment Share on other sites More sharing options...
trillback Posted October 31, 2003 Share Posted October 31, 2003 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? Quote Link to comment Share on other sites More sharing options...
reasonable Posted October 31, 2003 Share Posted October 31, 2003 OKay so I can see the lines that were mentioned. What would we need to put in there in order to have EP output those fields? Would make it much easier than rebuilding the input file by hand each time. Ron https://www.heartsfireleathers.com Quote Ron If we knew it all we wouldn't be here would we? Link to comment Share on other sites More sharing options...
coolhandluke Posted October 31, 2003 Author Share Posted October 31, 2003 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. Quote Link to comment Share on other sites More sharing options...
trillback Posted November 3, 2003 Share Posted November 3, 2003 I added your code and created my own TSV file, but when I added new products I don't see any changes. Has anyone got this to work? Any chance i can see the full source of your easypopulate.php file. Quote Link to comment Share on other sites More sharing options...
reasonable Posted November 3, 2003 Share Posted November 3, 2003 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. Quote Ron If we knew it all we wouldn't be here would we? Link to comment Share on other sites More sharing options...
trillback Posted November 5, 2003 Share Posted November 5, 2003 can you send me your TSV file and easypopulate.php file. I did just what you said but my setup doesn't seem to work :-( Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.