Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Adding data via SQL (New user)


devink

Recommended Posts

Hi,

 

can anyone tell me if the database lends itself to being updated using SQL. Specifically, I'm interested in generating SQL statements in Access and submitting them using a MySQL admin tool. I already have a product database, categorised, and would like to upload it to an OSCommerce database.

 

I have not installed OSCommerce yet, hence the question.

 

TIA

Many Thanks,

 

Steve

Link to comment
Share on other sites

I'm a database programmer, and I quite frequently execute SQL through myphpadmin.

 

When I uploaded my products from another database, I just constructed a query that would return my products in the same structure at the tables in the OSC and them uploaded that file. However, there are a few tables involved, so be prepared to spend some time getting it right.

 

You have to be carefull using that MSAccess SQL, though. It's not ANSI standard, and there will definatly be some syntax variations with MySQL.

 

For instance the "SELECT INTO" construct in MySQL is "INSERT INTO <table> VALUES SELECT"

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

Thanks for the info. I plan to write a text file from Access, filling in the gaps with data, rather than copy and paste an SQL statement. I work mainly with DB2/400 which is nearer the ANSI standard, so I'm familiar with some of the differences (But not all!).

 

Is there any documentation of the db relationships anywhere?

Many Thanks,

 

Steve

Link to comment
Share on other sites

I don't know of any ERDs, but if you can take a look at the tables in myphpadmin, and easily enough make sense of it. It's pretty much all snowflaked out, with the product ID being the only key you need to remember.

 

The table you will need to be concerned with are products, products_description, products_attributes, products_to_categories, products_manufacturers.

 

A pivot table in excel would probably work best, I agree.

 

 

Might not be a bad idea for one of us to create an ERD for OSC.

-------------------------------------------------------------------------------------------------------------------------

NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites

  • 2 years later...

I had a look on the PDF you mention. It's a graphical representation of tables and their relationships, but that's less than an ERD.

I doubt wheter somebody not involved in the DB design is able to create a correct ERD, because of simple lack of information. The main problem I've found so far in Open Source projects is the fact, that you can see the code, and are able to change it. This means that you can see how it is made, but not WHY it is made as it is.

From my point of view documentation like an ERD should deliver information that cannot be found by looking the code or, in this case, the DB content only.

 

What I'd like to see are not only the simple relationships (can be found by examining the DB with phpmysql or something similar), but the defined cardinality as well. Last but not least one should know which tables are mandatory and which are not.

 

This leads to another question:

What about referential integrity? Who's responsible for, the DB engine or the application?

 

Besides this it's of some interest to me to know, wheter there's a program able to check the entire DB for correctness? Does anybody know, wheter such an animal already exists?

Link to comment
Share on other sites

The folks at microOLAP seem to have some interesting DBA-related products that work with MySQL.

 

30-day trial downloads are free and the documentation is available in PDF format.

 

I haven't had the time to get my hands dirty with any of them, but I do have the 3rd week of November penciled in to learn about them.

 

Larry

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...