Adding data via SQL (New user)

[devink]

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

[wizardsandwars]

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"

[devink]

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?

[wizardsandwars]

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.

[devink]

In 2.2, there is a PDF doc of the database and relationships.

[ran]

In 2.2, there is a PDF doc of the database and relationships.

<{POST_SNAPBACK}>

[ran]

<{POST_SNAPBACK}>

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?

[kahml]

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