Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Stock Synchronization Between Two Databases


ibanezplayer85

Recommended Posts

Hello everyone :)

 

I'm making an online book store for a physical book store, and in the very near future I know that I'll have a serious problem updating the stock according to the in-store stock levels.

 

The problem is, I need to update the stock once a month, and the store can sell 500-1500 books in that period of time within the physical store. So, I need to figure out a way to efficiently compare and update the online database stock with the in-store stock level, as well as add new books that aren't in the old database but are in the new one.

 

Does anyone have any ideas on how to go about doing this? I'm not looking for an easy way... just ANY way that would be better than looking at their sale reports and manually updating the stock one by one. There's gotta be a better way...

 

BTW, I would simply over-write the old database once a month with the new one, but I'd lose all of the product images, descriptions, and other fields... Not to mention I had to spend about 2 hrs fixing the spelling mistakes the owner made in all of the book categories, etc., so I couldn't do that every month...

Link to comment
Share on other sites

Hello everyone :)

 

I'm making an online book store for a physical book store, and in the very near future I know that I'll have a serious problem updating the stock according to the in-store stock levels.

 

The problem is, I need to update the stock once a month, and the store can sell 500-1500 books in that period of time within the physical store. So, I need to figure out a way to efficiently compare and update the online database stock with the in-store stock level, as well as add new books that aren't in the old database but are in the new one.

 

Does anyone have any ideas on how to go about doing this? I'm not looking for an easy way... just ANY way that would be better than looking at their sale reports and manually updating the stock one by one. There's gotta be a better way...

 

BTW, I would simply over-write the old database once a month with the new one, but I'd lose all of the product images, descriptions, and other fields... Not to mention I had to spend about 2 hrs fixing the spelling mistakes the owner made in all of the book categories, etc., so I couldn't do that every month...

 

 

What is the backend system like? If you can get dumps from that system, either SQL or flat file, then you can make sure OSC is up-to-date.

 

It may not be a small job though. If you have a unique ID for products that exists in both systems (ISBN numbers?), then you have a way to match each and every product in the backend to products in OSC. Then you can pull the inventory numbers/pricing/whatever from the backend and UPDATE the OSC database. This wouldn't require editing anything about the products other than their stock amounts - so no spelling problems!

 

If you're not familiar with doing this sort of thing, then you might want to find a small consulting firm in your area to do this. You might be able to find someone to set this up for a reasonable price. For an experienced person, this can be a small job, but expect MANY problems if you're not experienced with databases. Issues can range from character set problems to escaping problems to problems you never even realized could occur. But then again, it might be a piece of cake. It all depends on the two systems and what they're stuctured like.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

Thanks for responding :)

 

The actual store uses a register program that has an SQL backend. I can export the database to a CSV file (I can also generate reports of books sold). There are unique IDs, yes, which are the ISBN numbers -- so the files can be matched in both databases. So how would I "pull" the ISBN and stock, compare the levels in both databases, and then "UPDATE" accordingly? I'd love to have someone do this for a fee but there are absolutely no web developers in my area (perhaps someone online, instead? and how much would be a fair price?)

Link to comment
Share on other sites

Does anyone have any ideas on how to go about doing this? I'm not looking for an easy way... just ANY way that would be better than looking at their sale reports and manually updating the stock one by one. There's gotta be a better way...

 

It all depends on the complexity of the synchronisation.

 

For example if you wanted to keep both stock levels in sync that you would need to do it more than once a month or else you risk selling all the books in the store but people purchasing the same books online for which you would have no stock.

 

The simplist way to do it although there is room for error is to add a counter to the product table and increment this each time you sell a book online and offline. Then create a csv export for the data then reset all of the counters to 0.

 

You will need to do this on both systems and also export the stock level of the books in the store. you could then from that data calculate an on hand stock level which would then be updated in both systems.

 

Not an easy tasks all in all :)

Link to comment
Share on other sites

Right, in reality, to be as accurate as possible, it would have to be done more than once a month. But, since we can only do it once a month, it will never be perfect. Plus, we're assuming that that the majority of people will be buying the books in the physical store as opposed to the online store. So updating the stock once a month should be sufficient for now.

 

Someone mentioned to me in another board that I could somehow use SQL statements for querying & updating the database, and a scripting language such as PHP to handle the comparison... would this be the way to go? I'm pretty new to SQL and PHP unfortunately, and I know this is a complex issue, so I'm not looking for any fix-it-quick ideas... Although, this problem does need to be solved under a specific deadline (and hopefully before the holiday), so I might have to hire someone to do the coding...

 

But before I do that, any ideas for the SQL updating and comparing? I'm actually kind of surprised there isn't an already-made script available somewhere... I figured this would be a heavily-requested feature among online store operators...

Link to comment
Share on other sites

You definitely can do this using PHP, but it can also be done manually. (And that doesn't necessarily mean having to type in every stock amount individually ;) ) Here's an outline of what you can do:

 

At the beginning of every month

1. create a sql backup of your current OSC database. Your host may provide a method for you to set up a cron job to do this automatically.

 

At the end of every month

1. dump data from the backend system. You'd only need ISBN and stock numbers and whatever else you might want to update.

2. create a MySQL table and import your dump from the backend database. Call this table backend_products.

3. create a MySQL table containing the products table from the dump you made at the beginning of the month. Call this table beginning_products.

 

You know that the current stock level minus the stock level at the beginning of the month is the number of books you have sold through the OSC store. So, you want beginning_products.products_stock - products.stock. Then, if you subtract that number from the stock level in backend_products.products_stock, you now have the current stock level. Then just overwrite that number in the OSC database.

 

So your query would be something like

 

UPDATE products SET products.products_stock = ( backend_products.products_stock - ( beginning_products.products_stock - products.products_stock ) );

 

You'd have to come up with another method of tracking stock in the backend if you can't import the new stock numbers. If you can't then another option is to choose one as the master. You could enter all OSC sales into the backend database so that it at all times has the accurate inventory. Then periodically overwrite the stock data in OSC to update it.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

Wow, thanks kgt for your very helpful response. I'm trying out your method right now. So, I would have to do this then:

 

Backup the OSC database, create "beginning_products" which contains the ISBN and stock from the OSC store, create "backend_products" which contains the the ISBN and stock from the actual store, and then run the SQL query you provided.

 

So I'm running a test right now... I created those two tables with the necessary ISBN and quantity fields, imported the data using phpmyadmin, and then ran this query:

 

UPDATE products SET products.products_quantity = ( backend_products.products_quantity - ( beginning_products.products_quantity - products.products_quantity ) );

 

But for some reason it's telling me:

 

#1109 - Unknown table 'backend_products' in field list

 

But I have 'backend_products' as a table in the database... I'm looking at it right now, spelled correctly, contains the same fields and everything... I don't get it :huh:

Link to comment
Share on other sites

It's telling you unknown tables because they are in the table list. Also, you need to make sure you relate each field in each table to the other on the products model (if that's where you store ISBN)

 

UPDATE products, beginning_products, backend_products SET products.products_quantity = ( backend_products.products_quantity - ( beginning_products.products_quantity - products.products_quantity ) ) WHERE products.products_model=beginning_products.products_model AND products.products_model = backend_products.products_model

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

Awesome... just did a test and it worked. Changed the stock in the backend table to 1 where it used to be 2, and it updated on the OSC store to 1 after the running the query. Just a quick question, if I wanted to change the stock based on two fields, ISBN13 and ISBN10, would that be possible? Would I do something like this? (or would I need to put another WHERE in there?)

 

UPDATE products, beginning_products, backend_products SET products.products_quantity = ( backend_products.products_quantity - ( beginning_products.products_quantity - products.products_quantity ) ) WHERE products.products_isbn13 = beginning_products.products_isbn13 AND products.products_isbn13 = backend_products.products_isbn13 AND products.products_isbn10 = beginning_products.products_isbn10 AND products.products_isbn10 = backend_products.products_isbn10

 

Also... question about importing the data (I'm terribly sorry if I'm being a pain, you've been such a great help). When I made the ISBN10 and ISBN13 fields in the backend_products and beginning_products tables, I copied the info from the main products table, which had VARCHAR(13), [and VARCHAR(10) respectively], Null to NULL and Default to NULL. Now, when I imported my data, I couldn't see any of the ISBN13 or ISBN10 fields because they were all "Null" (yet when I imported them, they were filled with their respective numbers). I tried taking out the NULLs in the field properties and imported the data again, but they came in blank... The quantity comes in fine, however, just not the ISBN #s. To do the test, I just manually entered the ISBN # of one book and then ran the query to see if the stock updated, so that part did work.

 

Thanks again for all the help, you don't know how much I appreciate it ;) (And I promise after this I will some more books on mySQL -- I'm actually in the process of reading one now.)

Link to comment
Share on other sites

I don't know the difference between ISBN10 and ISBN13. Are both required to uniquely identify a book? If so, then yes, you would need the additional two conditions in the WHERE clause. If ISBN 13 can uniquely identify items, then you don't need the additional conditions.

 

When you imported the data, you got warnings. When MySQL does not understand the value you are passing in, it will try to set the field to the defautl value. If the default is set to NULL, values that are not understood by the engine will become NULL. When the default value is set to '' (the empty string), then values that are not understood become blank fields. When MySQL does this silent conversion, it issues warnings (not errors).

 

I'm not 100% clear on how phpMyADmin handles warnings from the MySQL engine. I don't know if it automagically displays them or what. You should be able to type SHOW WARNINGS in the SQL tab window after you import the data to get a list of what warnings occurred. Also, remember that the fields from your backend dump may be required to be contained within quotes. The problem is probably that MySQL does not like how your data looks, so you'll need to figure out what it doesn't like.

Contributions

 

Discount Coupon Codes

Donations

Link to comment
Share on other sites

Yeah, I need to identify by both ISBN10 and ISBN13 so I'll do it that way then. I can't figure out what the warnings are. It usually tells me automatically, directly after running a query if I have warnings, but when I imported the data, I just get this (with no warnings...)

 

Inserted rows: 6491 (Query took 0.0121 sec)
SQL query: 
LOAD DATA LOCAL INFILE '/tmp/phpZlPCHV' REPLACE INTO TABLE `backend_products`
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

 

I tried importing as CSV, TXT, even tried changing the field names and the ISBN10/13 still show up blank. After I imported the data, I tried doing SHOW WARNINGS but that in itself gives me an error:

 

#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNINGS' at line 1

 

I'm so close to getting to this work, too! The stock update actually works, if only it accepted my two columns of data... :unsure: Is there any other way to import a CSV file into my backend_products and beginning_products tables?

Link to comment
Share on other sites

Just wanted to add the conclusion to this topic in case anyone else was watching...

 

After looking at my data, kgt informed me via e-mail that I had to change the import settings in phpMyAdmin so that it would accept the ISBN fields. So I emptied the table, and tried importing again, this time specifying that the fields are terminated by a comma rather than a semi-colon, and also specifying that the fields are enclosed by quotations OPTIONALLY. After that, the import worked like a charm, and I was able to update the stock using the above query.

 

Thanks again for all the help! :thumbsup:

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...