How to create tables for using in SQL/php website

[Helen_1972]

Sorry for asking basic question because I 'm knew.

i was building a dw site for listing multiple products. i was following DW, the Missing Manual book tutorials. The tutorial offers downloadable data tabels in a folder with .sql extension. I just cannot find anywhere an instruction as how the .sql folder is created.

Second question: if it can be created in Access 2007, please tell me how.

thank you sooo much.

[Helen_1972]

Hi David,

Thank you again!

That is an excellent article. I need to read again, maybe a few more times. Feeling great. Thanks!

[David_Powers · Accepted solution]

Try reading the following article for guidance. http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html. Life becomes a lot easier when you understand the basic principles of normalization.

[Guest]

Helen_1972 wrote:

when i  create a table, can i specify a field to be a look-up one, i.e., lookup a value from a related table field like you can do in Access tables? if the answer is yes, i hope you don't mind to give a little further instruction. if the answer is no, what is the alternative way to directly relate a new record to another table, e.g., relate a product to it's category?

Thank you very much. I am speeding up my learning, so I do not ask too many basic quesitons.

MySQL JOIN. Here's a basic tutorial

http://www.tizag.com/mysqlTutorial/mysqljoins.php

[Helen_1972]

thanks again, David. I will definately check your book out.

that gives me a better idea bout Access and MySQL.

[bregent]

>Now  I understand, if i'm correct, the cosmofarmer.sql is

>only a query  /instruction for getting data into those tables

>from an existing  database.

I don't think you provided an example of what is in the sql file, but I (and Walt) assume that it is a DDL (Data Definition Languate) statement. DDL is the language that DBMS's use to create objects.

>I want to know how the cosmofarmer database

>and the cosmofarmer.sql was created so I can

>setup my real website the same way.

If you are set on using MS Access, then you can either create the tables/objects manually, or use the SQL you have to create them. Note that if the DDL is written specifically for MySQL then you may have to alter it to get it to run in MS Access.

MS Access is not really a great DBMS to use for the web. Why are you considering that rather than MySQL?  PHP/MS Access is not a server model that DW supports so you will need to code everything by hand if you go that route.

[Helen_1972]

Thank you Bregent.

"MS Access is not really a great DBMS to use for the web. Why are you considering that rather than MySQL?  PHP/MS Access is not a server model that DW supports so you will need to code everything by hand if you go that route."

It sounds like I really should stop using Access and go directly to MySQL. I was just wondering it will be a tedius work ahead of me since i have a list of over >4000 products.

[bnbwalt]

For a general idea of how databases are created, yes you are on the right track.

The user interface in Access will give you a good start at how a table is created and the properties of a database table.  Access will also allow you to export the results of your actions as a sql file so you can see what it would look like.

If you build your tables in Access you can export them as a sql file containing the data definition language IDDL) the other person mentioned.  All DDL is is text much like the example that I had in an earlier post that can be processed by most SQL based databases.  Each database product has some unique features but most handle basic DDL and SQL query language statements.

The individual who said that Access is not the database to use for the web is exactly correct but for leaning basics about databases, Access is a good start.  If you are going to be using a hosting service for your web site, they will probably not support Access and will use, most likely, MySQL.  If you are going to be developing your site on your local system, I suggest that after you feel comfortable with the basics of a database with Access, you download MySQL, its free, and the MySQL administrator and set up your database for the web site using that.

Also, even if you are using Dreamweaver, I highly recommend that you become familiar with PHP as that will probably be the programming language supported by the hosting service.  Again, its free and you can set it up on your local system and test both the MySQL and PHP code from within Dreamweaver.

One nice thing about using Dreamweaver is the SPRY framework that provides some prebuilt server code for interfacing to the database and displaying the results and allowing for users to enter data or modify data.

If you go to http://www.adobe.com/devnet/dreamweaver/articles/first_dynamic_site_pt1.html you will find a fairly good tutorial from Adobe on setting up a PHP/MySQL dynamic site using Dreamweaver.  I highly recommend that you work through that as it walks you through the process.

Let me know what else I can do to assist.

Walt

B & B Photography

[Helen_1972]

Hi Walt,

thanks again!

I will definately visit the DW tutorials.

Only about Access data exporting to .sql I am not sure. I did not find in Access 2007 that function and no where on the web says I can do that.

[bregent]

I'm not sure about Access 2007, but 2003 does NOT have any native tools to export data into a sql statement. There are however, add-ons like this that can do it: http://www.sofotex.com/Export-Table-to-SQL-for-Access-download_L72425.html

Others also exist.

[bnbwalt]

You can export the data into files by creating a query and then export the results in multiple formats include delimited text.  What you can't do, and I just verified it and apologize for misleading you, is export the DDL from Access.  My memory is getting old.

You can, however, export your database definition directy to MySQL by the following

Access Export

If you are a Microsoft Access user but do not have access to Microsoft DTS or SQLyog, you may want to use the export capability of Microsoft Access. Access can export its tables to a variety of formats, including ODBC. This allows you to export an Access table to MySQL by way of the Connector/ODBC ODBC driver provided by MySQL AB.

To export an Access table to MySQL, right-click on the table in question and choose the 'Export' option. After several steps your data will be exported to MySQL. The column-type choices made by Access may need to be modified, and you should be aware that Access will not export index information with the data, meaning that you will need to implement indexes on your tables after exporting them.

This information can be found at http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html

So I will go back to my view and say if you are initially learning to use databases (create, query, enter data, use indexes, etc), start with Access as it is easy for the first time user and there are migration paths to MySQL.

You will still need to also learn about PHP and MySQL once you are serious about developing your dynamic web site.

Walt

B & B Photography