Miva Merchant 5.5 Dream Features

[Brandon MUS]

Re: Miva Merchant 5.5 Dream Features

I think we're both talking about the same thing, kayakbabe, but just to be clear:

For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

But that would appear to be a mute point since RayYates idea for a "last_updated" field would extend beyond the database row, so it would need to be a manually updated timestamp.

Just to reiterate, if Miva were to add the last_updated column, I would really like a date_created column as well. Different stores have different needs, but I think being able to do a "New Arrivals" page would be welcomed by everyone.

[RayYates]

Re: Miva Merchant 5.5 Dream Features

But that would appear to be a mute point since RayYates idea for a "last_updated" field would extend beyond the database row, so it would need to be a manually updated timestamp...

Don't mis-understand, we are talking about the same thing, a timestamp column. I want each record (not table) automaticly time stamped when updated and/or created., i.e. no manual update required.

You've both listed reasons for their inclusion, I hadn't though of.

Rick, do you want to weigh-in on this?

[kayakbabe]

Re: Miva Merchant 5.5 Dream Features

Brandon,
Since mysql 4.1.2
You CAN HAVE 2 (or 50) timestamp fields. But only one timestamp field is allowed to have autoupdate characteristics.
So for created and modified timestamps, one of them has to be handled by an insert statement.
Even in mysql 5 the same applies, unless you use triggers.

YOU CAN"T HAVE BOTH being automated at the same time.

Your choices for the auto update are either to assign a current time stamp as the default value OR to have it updated to the current timestamp everytime the record is updated OR both. You can't spread this behavior into different time stamp columns. Only 1 timestamp column is allows to have any kind of update behavior at all.

TIMESTAMP DEFAULT CURRENT_TIMESTAMP will automatically get the current time when it is created at INSERT but on subsequent record update there will be no changes.

TIMESTAMP ON UPDATE CURRENT_TIMESTAMP the field upon insert will equal 0 and then when an update occurs, it will get a time.

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP gets the current timestamp when the record is inserted AND also every time it's updated.

For my non miva stores. the created date is usually an automated mysql field with a default currenttime. so it won't change even if the record is updated. Then the modified_date is updated from within the cart code upon any kind of update such as Ray mentioned.

HOWEVER since to me the modified date is more important, and it can't be done from within miva easily. I choose to use the on update current_timestamp defined field.

I believe it would be better if miva chooses to add timedate stamps.. that they use the created_date field as the automated timestamp default currenttime and then manually update a modified_date field from within the store cart system. that way it can be tied to many actions such as attribute updates too (as ray suggested). And if a store owner wanted to change the modified date they could. Creation dates should be frozen. A creation can logically only occur once for a given entity. However modifications occur all the time.

again, I choose the timedate stamp that fit my needs best at the time which was having a autoupdating modified_date field. I don't think it would be best for the cart overall, but it was best for me and what I needed out of it.

[William Davis]

Re: Miva Merchant 5.5 Dream Features

I personally, if I can only have 1 time field, would prefer the last updated. I could use that info to figure out who of my employees screwed something up if I had to (not to punish, but to figure out if our procedures are wrong of if the employee needs to be in a different role, or needs more education); Even more though... I would use a date modified to have a product listing that showed recently updated products, to notify customers that some product has changed, to well... I could use it so many more ways than just a date created field.

However in mysql it is not true that you can have only 1 timedate stamp field in a record. You just can't have 2 fields that auto update to the current time upon update. So you can have your date created field and you can also have your last modified time field. You CAN have both. The date created needs to be created with the data of the record as is it first inserted. But the modified_date can be an auto updated by sql. On creation those timestamps will probably match, (but might not if your db server is not on the same box as your website).

By the way, I have modified my tables to have modified time stamps. I give xml feeds to some of my customers so they can resell our products and not have to stock them. It's important that they know when some product is updated and when it's been updated. I give them short feeds of only updated products (importing the whole mass of products is a lot, so they get short feeds everyday of just the changed products).

I believe the key is to use a field name which miva wouldn't likely use. Therefore I use a prefix to my extra field names. I wouldn't name this modified_date because that's a generic enough term that miva would likely use. I use something like kayak_modified_date. I'm pretty sure miva wouldn't use that. Yes, I might have a conflict if miva ever decided to add an automated time date stamp field. But if they do, it's a pretty obvoius and easy thing to fix. I've been doing this in every version of miva even before the compiled days.

I would really like this to be built in, and not have to mess with adding fields to the database structure. modified date is pretty standard in a lot of carts. We should have it in ours.

I should mention Bill's Tool Kit has a real neat auto update solution to this problem (feel free to chime in Bill), we've been using it for quite some time now and it works great.

[Brandon MUS]

Re: Miva Merchant 5.5 Dream Features

Brandon,
Since mysql 4.1.2
You CAN HAVE 2 (or 50) timestamp fields

For the record I never said you couldn't have two timestamp fields (I simply said you just can't use them both how Ray was hinting -- a simple schema change with no other logic needed). But yes, you are absolutely correct.

"Using" the one auto-update field on insert vs. update becomes a design decision. Personally I think using it on update would be smarter since creation is much more controlled so there is less chance of forgetting to set that date.

[Rick Wilson]

Re: Miva Merchant 5.5 Dream Features

Rick, do you want to weigh-in on this?

Sort of. We like watching the debate (especially when it is debate and civil which so far it is) because it helps us learn about the thinking process of our merchants, devs, partners, etc...

I see the logic behind what you're looking for and some of this info may already be available, for example in the new PA-DSS/PCI-DSS logs, and we just have to figure out how to make it easily accessible and usable.

At this time I can't comment on how and how much of this we'll implement, but we're listening and find it good feedback. It'll certainly go in the next round of Dev discussions internally.

[kayakbabe]

Re: Miva Merchant 5.5 Dream Features

just for a few more ideas of how this can be utilized... I also created a new products category. it contains the 100 most recently created and or modified products. This let's me notify customers that something new with an 'old' product has possibly made it interesting to them again. I didn't want just a list from newest to oldest. I didn't want just the newest products in the store (that would be easy with the product ID in the database... I wanted the most recently modified/updated/and created products in the store and I wanted only x number (100) of those products to exist in the category. Plus I wanted it to page and be sortable with other modules like sebenzas category templates. I wanted the older products to be weeded out. AND I wanted this all automated with no human having to keep track of it.

Ray Yates did the terrific code to allow all that. And he made it so it can be auto updated on the fly so I don't have to manually get involved. It also doesn't update based on page views of that category (which would be nutty for a busy store), it can do that.. but I have it running from a cron job that hits a 'hidden' page on the store to tell it to update. It's one less thing to worry about or manage. Ray did a terrific job! Thanks Ray!

I've been thinking of changing this from the x number of most recently added/modified products to the products add/modified in the last 30 days. Our store is pretty active and customers could probably benefit from that.

[kayakbabe]

Re: Miva Merchant 5.5 Dream Features

Just to be clear. I too think the products need both date_created and date_modified fields.

[William Davis]

Re: Miva Merchant 5.5 Dream Features

Just to be clear. I too think the products need both date_created and date_modified fields.

I concur -most feeds (...those of value anyway) ask for both (though not required, but ideal), and thus that is the way it should be. I think your previous suggestion (post) is a better solution than what we are using with Tool Kit workaround (update based on page view, I believe).

[wcw]

Re: Miva Merchant 5.5 Dream Features

The update is not based on page view with tool kit. The date updates when you make an update in admin. Just viewing a product page does not mean the page has been modified. I think you are mistaking this sentence with something that it is not " If you include the below code on the page, when the page is next visited, the token will update those that are empty with the current unix time."

Lots of people implement the tool kit last update feature after all their products are already in the store. Others don't want to bother to add that time when doing a mass flat file import of new products since it isn't really necessary. Hence that field is blank with those old products or those you did not want to take the effort to assign a time to. The tool kit has a token that inserts the time of the first visit of that page by anybody, including search engines. Most stores' pages are hit by some search engine every day so the day you implement this system all those blank products will have a date. Any new products added by flat file will have the date they were imported since a search engine will usually visit them that same day. Then the only time that date will change is when you edit/modify the product in admin.

[RayYates]

Re: Miva Merchant 5.5 Dream Features

..."Using" the one auto-update field on insert vs. update becomes a design decision. Personally I think using it on update would be smarter since creation is much more controlled so there is less chance of forgetting to set that date.

I completely agree. When records are inserted, the Merchant API can handle a creation timestamp field. I haven't look through the LSK to confirm this but I would assume the number of functions that INSERT product and category, records would be rather small. Other than those, only the table creation routines would have to altered.

Record updates can come from lots of sourses including third party modules, external Mivascript functions, MYSQL queries, ODBC connections, and even Storemorph pages using Toolbelt. Let the updated timestamp be handled by MySQL and it will always be current no matter how the record was updated.

As for MivaSQL, I have to say, none of my clients use it. In those cases, toolkit could fill the void quite nicely. Again the only problem with that solution is that it can't create dates from thin air on records that already exist, (but I'm sure Bills working on a fix for that <grin>)

My focus here is to get the Maximum Bang for the Minimum Effort, which (I hope) increases the likely hood that it actually happens. This is why I keep repeating those two tables. (Perhaps attributes too if your having a really good day but it would fall under the 'nice to do' heading.)

One other request, please resist the urge to use custom fields for this purpose. I spend a LOT of time writing custom exports and custom reports. I just want to load the tables and get the dates without jumping through extra hoops.

Thanks

[William Davis]

Re: Miva Merchant 5.5 Dream Features

The update is not based on page view with tool kit. The date updates when you make an update in admin. Just viewing a product page does not mean the page has been modified. I think you are mistaking this sentence with something that it is not " If you include the below code on the page, when the page is next visited, the token will update those that are empty with the current unix time."

Lots of people implement the tool kit last update feature after all their products are already in the store. Others don't want to bother to add that time when doing a mass flat file import of new products since it isn't really necessary. Hence that field is blank with those old products or those you did not want to take the effort to assign a time to. The tool kit has a token that inserts the time of the first visit of that page by anybody, including search engines. Most stores' pages are hit by some search engine every day so the day you implement this system all those blank products will have a date. Any new products added by flat file will have the date they were imported since a search engine will usually visit them that same day. Then the only time that date will change is when you edit/modify the product in admin.

Thanks -I had forgotten how that actually worked.

[Datagg]

Re: Miva Merchant 5.5 Dream Features

We are in the process of having some work done on our site by the awesome Design team of Miva. Come to find out, that the attribute system with variants isn't designed to work with 3 levels deep. This does limit us quite hard on our Bikini line of products.

With that being said, my future request would be to allow a prevision to accept and maintain a variant level that would work with products 3 levels deep.

[Rick Wilson]

Re: Miva Merchant 5.5 Dream Features

Dan,

There must be some confusion. The current variant system can handle an unlimited depth of attributes, only the kits are limited in any way.

[Datagg]

Re: Miva Merchant 5.5 Dream Features

Dan,

There must be some confusion. The current variant system can handle an unlimited depth of attributes, only the kits are limited in any way.

Hmm... Matt had mentioned to us along the way, as we activated inventory products from the old to the new, that a 3 level setup wont work. We still need to flip the switch from vikings setup, to wombats setup, but were advised to deactivate the ones with 3 levels deep as they wont work dynamically thru the various attributes correctly.