Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

mySQL - Compare 2 DBs, Add Record from One That's Not in the Other


ibanezplayer85

Recommended Posts

Posted

I'm working for a book store, and we're using osCommerce for the website (obviously). The manager takes about 3-4 hrs a day adding books into his physical store database (he receives up to 2,000 new books weekly) and adding these books to the website, at the same time, would just be double the work.

 

My problem: We have two databases - one for the physical store and one for the web. The one in the physical store changes daily. I've already received help from someone else that updates the stock level accordingly, but now I need to find a way to add new records to the web database according to the store database.

 

My question: Is there a way to compare the two databases (each DB is really just one table with all of the product fields), and if there is a product (product id, price, description, etc) in the physical store DB that is NOT listed in the web database (in other words, it's new), then to add it to the web database?

 

An SQL command, or PHP perhaps? I'm desperate for some help on this. Thank you for any help in advance, I appreciate it so much.

 

P.S. The physical store DB runs on an SQL program. It's a very limited program but I am able to export the necessary fields in order to compare/update. I do this to synchronize the stock levels at the end of the month. Just thought I'd add that in case anyone was going to ask what type of DB the store had.

Posted

Hello again!

 

There are a couple of ways to do this. Here's an easy one that comes to mind:

 

insert into osc_products select backend_products.* from backend_products left join osc_products on backend_products.products_id=osc_products.products_id where osc_products.products_id is null

 

This compares all the records in the OSC products table and the backend products table. If there are products_id's in backend_products that do not exist in the osc_products, then they will be added to osc_products. You can change the following two clauses to match on ISBN numbers:

 

on backend_products.products_isbn=osc_products.products_isbn where osc_products.products_isbn is null

 

A second way is if you can do a .sql dump from the backend system. Then you do a simple text search and replace to modify all the insert statements to add the IGNORE keyword. You'd need to make sure you have unique indexes on your isbn fields.

 

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

 

"If you use the IGNORE keyword in an INSERT statement, errors that occur while executing the statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is still not inserted, but no error is issued."

 

This means duplicate ISBN numbers would not be inserted, but non-duplicate (new items) would be.

Contributions

 

Discount Coupon Codes

Donations

Posted

Hey, you've come to my rescue again! Amazing :blush:

 

I'm going to try the first method you've recommended to me. (I'm at work right now but I'm going to try it out first thing when I get home). Just one quick question: Using IGNORE sounds ideal... would I write it like this? (the link was a little confusing)

 

insert ignore into osc_products select backend_products.* from backend_products left join osc_products on backend_products.products_isbn=osc_products.products_isbn where osc_products.products_isbn is null

 

Thank you so much for your helpful response, I really appreciate it. I'll let you know if everything works later when I try it out :)

Posted

insert ignore into osc_products select backend_products.* from backend_products

 

It also requires that you have a unique index on isbn numbers AND that the products_ids do not overlap between the two tables. Or, you could write it like this (this query would allow for overlapping products_ids and match on isbn as a UNIQUE index):

 

insert ignore into osc_products select null, products_quantity, products_model, products_image, products_price, products_date_added, products_last_modified, products_date_available, products_weight, products_status , products_tax_class_id, manufacturers_id, products_ordered, products_type, products_cost from backend_products

 

Either one works, and I doubt that there's a big enough difference between the two, so whichever is easiest for you will work best.

Contributions

 

Discount Coupon Codes

Donations

Posted

I just realized there may be a problem. The fields products_name and products_description are located inside the products_description table instead of the products table, so I think I'd have to change my SQL statement to something else...

 

Also, in the physical store database, we don't have certain columns that are in the web database, such as the v_status, v_tax_class_title, and EOROER (the last one is for EasyPopulate). These fields always have the same values ("ACTIVE", "Taxable Goods", and "EOREOR" respectively), so is there a way to specify this in the SQL statement so that when any new product is added during the comparison, these fields aren't added as null?

 

The products_status field is located inside the products table, but tax_class_title is located in the tax_class table and I can't find the EOREOR anywhere... This suddenly got a lot more complicated :(

Posted

Ugh and I forgot about the categories, as well. They're given IDs and I'm not even sure how they are joined with the products... (products_id maybe?) because I don't see that in the products table anywhere.

Posted
I just realized there may be a problem. The fields products_name and products_description are located inside the products_description table instead of the products table, so I think I'd have to change my SQL statement to something else...

 

Yes. This is no big deal.

 

Also, in the physical store database, we don't have certain columns that are in the web database, such as the v_status, v_tax_class_title, and EOROER (the last one is for EasyPopulate). These fields always have the same values ("ACTIVE", "Taxable Goods", and "EOREOR" respectively), so is there a way to specify this in the SQL statement so that when any new product is added during the comparison, these fields aren't added as null?

 

Post results from:

 

SHOW CREATE TABLE products

SHOW CREATE TABLE products_description

SHOW CREATE TABLE backend_products

 

The products_status field is located inside the products table, but tax_class_title is located in the tax_class table and I can't find the EOREOR anywhere... This suddenly got a lot more complicated :(

 

It did, but it's only more complicated, certainly not impossible. You just need to figure out what the relationships are. I don't know EP, so I have no idea about the EOREOR field. Post a question on the support thread for EP to get some information on it.

 

To do this, you'll need to look at it from the OSCommerce side. What information is required for a product to be complete? I mean, you know you need price and title and ISBN, but what other contributions do you have the require custom data in order for a new product to be a complete product?

 

It might help to get out a piece of paper and map out how the tables are related to each other. This will help you know where you need to put new information and where you need to get existing information.

 

EP has already done this - and there's no reason to ignore a tool that already does what you want. You can run a query like so:

 

insert into export_products select backend_products.* from backend_products left join osc_products on backend_products.products_id=osc_products.products_id where osc_products.products_id is null

 

This query will create a new table called export_products that contains ALL the information from your backend system for new products only. You can then export this as a csv, which can then be run through EP, which is already set up to handle the relationships.

Contributions

 

Discount Coupon Codes

Donations

Posted
SHOW CREATE TABLE products

SHOW CREATE TABLE products_description

SHOW CREATE TABLE backend_products

 

CREATE TABLE `products` (

`products_id` int(11) NOT NULL auto_increment,

`products_quantity` int(4) NOT NULL default '0',

`products_author` varchar(45) default NULL,

`products_isbn13` varchar(13) default NULL,

`products_isbn10` varchar(10) default NULL,

`products_type` varchar(45) default NULL,

`products_model` varchar(12) default NULL,

`products_image` varchar(64) default NULL,

`products_price` decimal(15,4) NOT NULL default '0.0000',

`products_date_added` datetime NOT NULL default '0000-00-00 00:00:00',

`products_last_modified` datetime default NULL,

`products_date_available` datetime default NULL,

`products_weight` decimal(5,2) NOT NULL default '0.00',

`products_status` tinyint(1) NOT NULL default '0',

`products_tax_class_id` int(11) NOT NULL default '0',

`manufacturers_id` int(11) default NULL,

`products_ordered` int(11) NOT NULL default '0',

PRIMARY KEY (`products_id`),

KEY `idx_products_date_added` (`products_date_added`),

FULLTEXT KEY `products_isbn13` (`products_isbn13`)

)

 

CREATE TABLE `products_description` (

`products_id` int(11) NOT NULL auto_increment,

`language_id` int(11) NOT NULL default '1',

`products_name` varchar(64) NOT NULL default '',

`products_description` text,

`products_url` varchar(255) default NULL,

`products_viewed` int(5) default '0',

PRIMARY KEY (`products_id`,`language_id`),

KEY `products_name` (`products_name`)

)

 

CREATE TABLE `backend_products` (

`products_quantity` int(4) NOT NULL default '0',

`products_isbn13` varchar(13) NOT NULL default '',

`products_isbn10` varchar(10) NOT NULL default ''

)

 

(Right now I only have the quantity and ISBNs for the backend_products because I was only using those fields to update the stock (rather than uploading all of the other unnecessary fields) -- but I realize that I need all of the fields in the backend_products in order for this to work.)

 

To do this, you'll need to look at it from the OSCommerce side. What information is required for a product to be complete? I mean, you know you need price and title and ISBN, but what other contributions do you have the require custom data in order for a new product to be a complete product?

 

These are necessary to create a new product: status, date available, name, tax class, price, description, quantity, isbn13, isbn10, book type, model (usually leave blank), and image (added later).

 

These are custom added through EP: isbn13, isbn10, book type.

 

EP has already done this - and there's no reason to ignore a tool that already does what you want. You can run a query like so:

 

insert into export_products select backend_products.* from backend_products left join osc_products on backend_products.products_id=osc_products.products_id where osc_products.products_id is null

 

This query will create a new table called export_products that contains ALL the information from your backend system for new products only. You can then export this as a csv, which can then be run through EP, which is already set up to handle the relationships.

 

That's true... So I can export the physical store database, import those records to backend_products, then run the query you stated above, download export_products, make sure that there's an EOREOR at the end of everything (simple), then use EP to upload it back up and the database is up-to-date (in theory :)) Only thing is that my backend_products would need to be set up like my my osc_products so that they could compare properly, right? Otherwise it'll compare the product name from backend_products with the product id in osc_products.. that's the part I'm confused about.

 

Once again, thank you for taking the time to help me, I really cannot thank you enough :D

Posted
That's true... So I can export the physical store database, import those records to backend_products, then run the query you stated above, download export_products, make sure that there's an EOREOR at the end of everything (simple), then use EP to upload it back up and the database is up-to-date (in theory :)) Only thing is that my backend_products would need to be set up like my my osc_products so that they could compare properly, right? Otherwise it'll compare the product name from backend_products with the product id in osc_products.. that's the part I'm confused about.

 

Once again, thank you for taking the time to help me, I really cannot thank you enough :D

 

Short answer: yes.

 

Longer: I believe from our previous discussion ISBN10 and ISBN13 uniquely identify a product (and they are both required). These are the fields you use to compare - nothing else matters. The only thing you need in backend_products is

 

isbn10

isbn13

 

(if they really do uniquely identify all products).

 

The query

 

INSERT into export_products

SELECT backend_products.*

FROM backend_products

LEFT JOIN osc_products

ON backend_products.products_isbn10=osc_products.products_isbn13 and backend_products.products_isbn10=osc_products.products_isbn13

WHERE osc_products.products_isbn10 is null and osc_products.products_isbn13 is null

 

Will create records in the export_products table with just the isbn10 and isbn13 from the backend_products table that do not match the osc_products table. backend_products does NOT have to match the schema for osc_products for this query to work.

 

Then you have a list in export_products of the new products. If you have enough control over the export from the backend system, you can export a csv/excel file/whatever based off of the ISBN's in list you get above. Then use EP and import. Done!

 

Of course I imagine it'll not be that easy... ;)

Contributions

 

Discount Coupon Codes

Donations

Posted
Short answer: yes.

 

Longer: I believe from our previous discussion ISBN10 and ISBN13 uniquely identify a product (and they are both required). These are the fields you use to compare - nothing else matters. The only thing you need in backend_products is

 

isbn10

isbn13

 

(if they really do uniquely identify all products).

 

Yep, they're both required to uniquely identify the product (good memory!) But if I only insert the ISBN's into the backend_products table, where will it get the other necessary product info from when adding a new book to the DB? In other words, I have a copy of the physical store DB in a CSV file which contains old products and new ones that the web DB has not seen yet. If I only insert the ISBN numbers into backend_products, none of the new book info will be there (besides the ISBNs), and when the comparison takes place and it finds an ISBN that's not in the web database, it won't have any other product info to add (like the product name, price, etc.) Am I making any sense? Where do I store that information then? (I thought I would have to put all of the product info inside backend_products)

 

The query

 

INSERT into export_products

SELECT backend_products.*

FROM backend_products

LEFT JOIN osc_products

ON backend_products.products_isbn10=osc_products.products_isbn13 and backend_products.products_isbn10=osc_products.products_isbn13

WHERE osc_products.products_isbn10 is null and osc_products.products_isbn13 is null

 

Will create records in the export_products table with just the isbn10 and isbn13 from the backend_products table that do not match the osc_products table. backend_products does NOT have to match the schema for osc_products for this query to work.

 

Then you have a list in export_products of the new products. If you have enough control over the export from the backend system, you can export a csv/excel file/whatever based off of the ISBN's in list you get above. Then use EP and import. Done!

 

Of course I imagine it'll not be that easy... ;)

 

Ohhhh, ok... I see what you mean now about the new products (I think). The thing is, I can only export the WHOLE database from the backend store, so I thought I would have to replace the backend_products table with the updated backend database (including all of the product fields) for every update.

 

I could use export_products to identify the new products, open the backend CSV file that contains all of the products, search the ISBNs and copy the book info from there, but that could be up to 2,000 new products a month which would take too long for me to manually copy & paste into the EP file to upload...

Posted
bump

 

did u forget about me? lol :-"

 

 

Yes. ;) But the answer you were expecting in you last post is the correct one. You're on the right track. If you can't selectively export records from the backend database, then you are correct that you will want to export all information you have. It's still a simple procedure:

 

Do a full export from the backend with all the information you need and import into backend_products. Then perform the query:

 

INSERT into export_products

SELECT backend_products.*

FROM backend_products

LEFT JOIN osc_products

ON backend_products.products_isbn10=osc_products.products_isbn13 and backend_products.products_isbn10=osc_products.products_isbn13

WHERE osc_products.products_isbn10 is null and osc_products.products_isbn13 is null

 

Replace SELECT backend_products.* with

 

SELECT status, date available, name, tax class, price, description, quantity, isbn13, isbn10, book type, model , etc...

 

Add to this SELECT the fields that EP expects to have in the CSV. The query will create a table called export_products with all the information you have in the SELECT statement, but only for products without matching ISBN numbers in osc_products (the OSCommerce products table). Then using phpMyAdmin, you can get it to make a nice CSV for you, which you can then run through EP.

 

If you're comfortable coding, you might find it easier to modify EP to handle this without the intermediary steps. I can't tell you how much work it would be since I haven't messed around with it myself.

Contributions

 

Discount Coupon Codes

Donations

Posted
Yes. ;) But the answer you were expecting in you last post is the correct one. You're on the right track. If you can't selectively export records from the backend database, then you are correct that you will want to export all information you have. It's still a simple procedure:

 

Do a full export from the backend with all the information you need and import into backend_products. Then perform the query:

 

INSERT into export_products

SELECT backend_products.*

FROM backend_products

LEFT JOIN osc_products

ON backend_products.products_isbn10=osc_products.products_isbn13 and backend_products.products_isbn10=osc_products.products_isbn13

WHERE osc_products.products_isbn10 is null and osc_products.products_isbn13 is null

 

Replace SELECT backend_products.* with

 

SELECT status, date available, name, tax class, price, description, quantity, isbn13, isbn10, book type, model , etc...

 

Add to this SELECT the fields that EP expects to have in the CSV. The query will create a table called export_products with all the information you have in the SELECT statement, but only for products without matching ISBN numbers in osc_products (the OSCommerce products table). Then using phpMyAdmin, you can get it to make a nice CSV for you, which you can then run through EP.

 

If you're comfortable coding, you might find it easier to modify EP to handle this without the intermediary steps. I can't tell you how much work it would be since I haven't messed around with it myself.

 

 

Awesome B) I just wanted to let you know that it worked out. The first time I did it I had to add about 111,000 new products from the backend database, but now that all of the products are in there the hard part's over. Updating on a per month basis will be very easy (probably like 500-5,000 per update). I also created a query that will automatically update the owner's misspellings of booktypes and categories from export_products table (I'd do backend_products but this way is less queries -- only fix the new products going into OSC).

 

The only thing is I realized this after I uploaded 111,000 new products and now I have a bunch of categories that need to be re-named :( I think I can somehow change this in phpmyadmin by editing the categories table, though...

 

Lesson learned though. Next month should be very smooth thanks to you! :thumbsup:

Archived

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

×
×
  • Create New...