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.

[bnbwalt]

Files with the .sql extension are just text files that contain SQL query language.  In this case, my guess is that the files contain the SQL commands to create tables and maybe even populate them with some data.  Not sure about the latter.

They can be imported into MySQL and even into Access.  You may have to read the documentation on Access to find out how to import them.

Not sure about your question about a sql folder.  Not sure what you mean by that.

Walt

B & B Photography

[Helen_1972]

My question is: how to place Access data tabels in a folder name xxxx.sql, so it can be utilized in a website. Thanks.

[bnbwalt]

As far as I know and remember from my years with Access, the tables used in an Access database are contained in a single file with the suffix of xxxx.mdb.

Maybe Access 2007 is different but I do not know since the last version of Access I used for web or any other development was in Office 2003 and that created all the tables in a single file called xxxx.mdb.

From what I see on the web, Access 2007 still creates a single file for the Access database.  I guess I just do not understand what and why your are asking about a xxxx.sql folder.  Is that where you want to put the mdb file?  Are you using an Access project to link to a database implemented in SQLServer?

If you could give me a reference to what you are doing, I might be able to assist.  I have 15+ years in database, database application, and web application development.

I am sorry I am not immediately understanding what you are asking.

Walt

B & B Photography

[Helen_1972]

Hi Walt,

Thank you so much for spending the time trying to answer me.

you are right, it is a .sql file, not a folder. However, once it is imported into MySQL server, it spits out 4 tables. Please see the following tutorial steps:

"4. in phpMyAdmin's top navigation bar, click the Import button.

Doing so takes you to a page that lets you type in an SQL query or load a test file that has SQL commands in it. You'll do the latter-load a text file that contains all the SQL necessary to create the tables and data for the database.

5. Click the Browse button in XAMPP (application software for SQL server), In the File Upload window that appears, navigate to and select the file cosmofarmer.sql in the php_dynamic folder you downloaded with the tutorial files.

6. Click the Go button.

The MySQL server slurps down the SQL file, and executes the instructions found within it. The results? Four new talbes are created (see the list of talbles that just appeared on the left side of the phpMyAdmin window) and a bunch of data is added to them. "

The book I am reading explains everything but how the cosmofarmer.sql file was made.

[bnbwalt]

No problem with the time.  I learn a lot by trying to help others 🙂

As I said in an earlier post, the sql file is just a text file.  In this case, someone either sat down a typed in the required sql queries into a text file called cosmofarmer.sql or, if the database already existed, they exported the tables to the file called cosmofarmer.sql.

If you open the file in notepad, you will see the sql queries that create the tables.  They will lool something like

CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

Since I do not know what was trying to be accomplished by the database, the example here is probably much simplier than what will be in the file you have.

If you are asking me what exactly is in the file cosmofarmer.sql, I have no idea 🙂

I hope this helps and if not, keep asking questions.  I do not mind trying to help.

By the way, what is the book you are reading?

Walt

B & B Photography

[Helen_1972]

That is interesting to know. i was just wondering how a file can be converted into mutiple data tables as the tutorial website did. 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 still have no idea how the cosmofarmer.sql was created, but I've started reading Access 2007 tutorials.

I want to know how the cosmofarmer database and the cosmofarmer.sql was created so I can setup my real website the same way.

Am I on the right track?

thanks again,

Helen

[David_Powers]

Helen_1972 wrote:

Am I on the right track?

No. Dreamweaver's PHP server behaviors don't support Access. You must use MySQL.

The .sql file is usually created by exporting data from MySQL using phpMyAdmin or a commercial product such as Navicat.

[Helen_1972]

Thanks, David.

i wasn't familiar with either MySQL or Access, but i got the impression that Access is the way to go for massive data entry and MySQL is not. Also, Access data can be easily transferred to MySQL. If that is wrong, I will stop wasting time in learning Access. ?

[David_Powers]

Helen_1972 wrote:

i got the impression that Access is the way to go for massive data entry and MySQL is not. Also, Access data can be easily transferred to MySQL.

No, you've got it the wrong way round. Access is part of Microsoft Office, and is designed for small office databases. MySQL is an enterprise level relational database management system.(RDBMS), capable of storing millions of records. It's used by YouTube, Wikipedia, NASA, Flickr, and Facebook.

Although data from Access can be migrated to MySQL, I would not recommend going that route. If you're not already at home with Access, I think you'll waste your time learning a system that differs from the one you ultimately intend to use.

Walt has already pointed you in the direction of a tutorial series I wrote for Adobe. You might also be interested in taking a look at the book I wrote about using PHP with Dreamweaver CS5: "Adobe Dreamweaver CS5 with PHP: Training from the Source". It covers all the basics of designing a MySQL database and interacting with it through PHP. You can find details on my website at http://foundationphp.com/phpcs5/.

[bnbwalt]

David's statements are correct about the design and purpose of Access - its part of Office and is not suitable for large scale production databases.

My point has been that since you are new to database design and usage, Access has some nice tools for the beginner and allows for easy data entry.  MySQL, using MySQLAdmin, does allow for data entry but I find it clumsy for the beginner.  I do all my current work directly in MySQLAdmin, but I come from 15+ years database, application (C++, Pascal, C#, Java) development, and information system architecture.

Where I used to work, we used Oracle, MySQL and SQLServer.  It was determined, wrongly in my view, that only DB Administrators could directly use those advanced systems.  Luckily, I was classified as one.  Prior to that, I had many experiences with people who jumped in at the deep end (using one of the 3 tools mentioned) and was required to go in and resolve their issues - by the way, most had attended classes on those products but without prior background.

We found that if someone started in Access to put their ideas down, it was a lot easier to work with them on transitioning to one of the big three.  By the way, David will probably respond to this, but you also use Excel to create spreadsheets that represent your tables, fill in the data and export it to a database, yes even to MySQL, and the tables with the data will be created.

I you are not interested in becoming a full-time developer and database guru, I suggest that you start small and work up.  If you are the type that like to jump into the deep end, then go directly to MySQL.  The learning curve might be slightly steeper for you but you will also eventually get there. 

You actually face three learning curves - database, PHP and Dreamweaver.  This will not be a trivial effort on your part.  Read books, review tutorials and seek the advice of experts like David and you will make it.  The route you choose is the route that should be the most comfortable to you and your learning and development style.

Good luck

Walt

B & B Photography

PS : I also strongly recommend David's tutorials, examples and site.  He has a lot of great information.

[Helen_1972]

Thanks, Walt.

Yes, i am learning the three all together. I thought it would be easy to use Access as well. On the other hand, some of my data is already in Excel. I am most interested in getting a tool that is most efficient. I know little about MySQL, and am afraid it is not so easy to input data as MS Access. I might be wrong.

[David_Powers]

bnbwalt wrote:

We found that if someone started in Access to put their ideas down, it was a lot easier to work with them on transitioning to one of the big three.  By the way, David will probably respond to this,

You rang, sir? 😉

Everyone's experience will be different, but I started out with Access, and actually found it very difficult. I read books about it, and my first book on database design used Access exclusively. I just found myself going round in circles.

It wasn't until I got a copy of "MySQL" by Paul DuBois that database design began to fall into place. I also used "Database Design for Mere Mortals" by Michael Hernandez - a bit verbose, but generally solid.

However, I should point out that I started using computers with MS-DOS, and my first websites were hand-coded in Notepad. So, I was less intimidated by working at the command line than many beginners would be today. For a long time, I did all my work with MySQL at the command prompt; but I think most people should have little difficulty using MySQL with phpMyAdmin. It doesn't look pretty, but it's fairly easy to use once you know what you're trying to do.

Give Access a whirl by all means if you have it installed. Its graphical interface might make it easier to learn the basics of working with a database. However, if you don't have Access, I don't think it's worth paying the extra to install a tool that you won't be making long term use of.

BTW, thanks for your nice comments about my material.

[Helen_1972]

Thanks, David and Walt.

I have decided to drop Access and go with MySQL.

I have read a few articles and Tutorials on MySQL databaes. while I am practicing, i have an important question that i am having a little trouble finding an answer:

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.

[bnbwalt]

You just ran into one of the reasons why I suggested using Access as your beginning and data entry tool.

Good luck

Walt

[Helen_1972]

yes. Walt.

I'm getting quite frustrated here.

I have not found a very systematic tutorial among many on the internet like those for MS programs. I read hours to get Innodb working so I can add a foreign key, which is supposed to be similar as a look-up field in Access. I just couldn't get a straight forward answer as how to make InnoDB work in my SQL. Also, I have no idea if I can manage SQL data the way as I can do in excel spreadsheet. I was thinking using Access for data entry and then move to SQL. However, I read somewhere it says, data from Access won't migrate won't be easy and some index function will be lost...

[David_Powers]

Helen_1972 wrote:

I'm getting quite frustrated here.

I have not found a very systematic tutorial among many on the internet like those for MS programs. I read hours to get Innodb working so I can add a foreign key, which is supposed to be similar as a look-up field in Access.

I think part of your problem is that you're confused by the non-standard terminology used by Access. You need to understand the difference between foreign keys and foreign key constraints. A foreign key is simply a column that contains a reference to the primary key of a record in a related table. A foreign key constraint is a SQL rule that tells the database what to  do when a record in the parent table is deleted or updated.

The default storage engine in MySQL 5.1 and earlier is MyISAM, which supports foreign keys, but not foreign key constraints. The InnoDB storage engine, which becomes the default in MySQL 5.5, supports both foreign keys and foreign key constraints. Although foreign key constraints are extremely useful, they're not absolutely essential, because you can achieve the same effect through your application logic in PHP (or any other server-side technology). Before rushing into the use of foreign key constraints and InnoDB, you need to check whether it's supported by your hosting company. InnoDB tables tend to be larger, and they're more difficult to administer on shared hosting, so many hosting companies turn off InnoDB or offer it only on premium support. The advantage of MyISAM lies in smaller files and faster response (in most cases).

I can understand your frustration. When I first started working with databases, I found myself going round and round in circles. The problem with open source projects like MySQL and PHP is that there's a lot of conflicting and out-of-date information floating around on the internet. Microsoft sells software, so it has a vested interest in making sure tutorials are available. With PHP/MySQL, try to find an up-to-date and respected book - it doesn't have to be one of mine - and get familiar with all the terminology and basic features. Once you have that knowledge under your belt, you'll find it a lot easier to get help in public forums, because you'll be talking the same language as those trying to help you.

My "Adobe Dreamweaver CS5 with  PHP: Training from the Source" provides all the basic information  you need in one place. The book shows how to use Dreamweaver's built-in  server behaviors for interacting with MySQL, but then teaches you how  to use the Zend Framework, which is much more up to date and offers a  wide range of functionality not present in Dreamweaver. If you want to  stick with the Dreamweaver server behaviors, my "The Essential Guide  to Dreamweaver CS4 with CSS, Ajax, and PHP" is still valid, because the server behaviors in CS4 and CS5 are identical.

Alternatively,  if you want to gain deep knowledge of MySQL, I strongly recommend  getting a copy of "MySQL, Fourth Edition" by Paul DuBois. It's more than 1,000 pages, but it's extremely well written.  Virtually everything you need to know is in the first 200-300 pages. The  final 300 pages or so are an invaluable reference of SQL commands,  functions, and operators. I bought the first edition of his book, and  it's so good, I have bought each subsequent edition. I  constantly turn to it for answers.

Good luck.

[Helen_1972]

That is a whole lot of good information again, David. Thank you!

Yes, I was wondering the same about InnoDB. It is not search friendly either. There are reasons for MyISAM being so popular.

I've not got any of the books you recommended because when I want a book I go to the shelves at a local Barnesandnoble. I think i will order one as my Bible.

I feel guilty to ask for more, but comments to this will be much appreciated:

My biggest concern in my project is listing thousands of products which include tens of categories and levels of sub categories. I still don't have a clear idea about how to structure my tables.

I understand that I need to have a better understanding of Database and find out the appropriate software to build it up.

[bnbwalt]

You are running into the learning curve problem.  Databases are not a simple subject.  Access has been a great tool for beginners trying to establish what seems to be simple databases.

Can you create your data in Excel and export to MySQL?  Yes, with some minor caveats.  The fact that you are using lookup fields says that what you should do initially is create a model of your proposed database.  That means that you should list your tables and their fields.  For each field, identify the type of data.  What is called a primary key needs to be identified for each table.  The primary key has no information about the data.  It is use only for identification (SQL wise) purposes.

If there is a table that is dependent on another table, then a reference needs to be defined.  There are two major types - one to many and many to many.  One to many states that between two table, there are many values in the referenced table for one value in the main table.  For example, addresses for customers could use a separate table that only contains 50 entries for state as the state entry can reference many customer addresses.  (its a bad example, but simple).

This is a bad example for many to many but illustrates the concept.  Two tables - one containing first names and one containing last names.  As you can imanage, a first name of John could be related to many last names and a last name of Smith could be related to many first names.

Once you have that model (diagram) established, then the next step is to actually build the tables or Excel worksheets and enter data.  For the many to many relationship, a separate table that resolves the many to many is required.  That table only contains the primary keys of the two related tables.

The problem with exporting the Excel data to a database is the issue of the many to many table and primary keys.  You want the database to generate them.

If this is an important, meaning your income is dependent on it, I would recommend that you hire a professional web developer in your area to do the work.  If its a casual project and not time constrained and you like to learn new stuff, keep on going and asking questions.

Walt

B & B Photography

[Helen_1972]

Thanks, Walt.

I have gained some knowledge about the types of relational tables from recent reading and it is great that you pointed out the aspects of migrating from Excel to SQL.

What I am trying to work out is a product list for research labs. Here is a snapshot of how the categories look like.

categoryID    categoryName     motherCategoryID

1                  reagents                         null

2                  proteins                             1

3                  DNA engineered Proteins    2

4                  enzymes                           2

5                  antibodies                         1

6                 primary antibodies               5

7                 labeled primary antibodies    6

8                 unlabeled primary antibodies 6

9                 second antibodies                5

10               buffers                              1

11               pH buffers                         10

12               non-pH buffers                   10

......

You can tell under the main category 'Reagents' there are several sub categories and each of those include another level of sub categories and it goes on more levels.

It is easy work to create a product label, but I am pausing at how to make this category table, so I can properly relate the products.productID to categories.categoryID.

I feel I should adopt the categoryIDs of those lowest level categories. For example, for product phosphate buffer, I should link it to #11 pH buffer rather than #10 buffers.

More about the category table: Originally I thought I need a category table and a sub-category table, but that doesn't work, because some categories have one lower level sub-categories and some have several lower levels. Making different data sets for different categories will for sure solve this problem, but that makes the database much complicated. So I figured I may create a categories table like the one I just typed.

I don't yet know if this is a totally dumb idea.

At least this illustrates what I am trying to do. How would you database categories like this?

[Helen_1972]

What I am describing is a tree structure. However, it is NOT the same as a product tree in an assembly factory. In an assembly factory the tree branches are sub-assemblies, which are physical parts and can be products. In my list, the sub-categories are only names. In my tree, only the fruits are physical products.

I'm thinking I can build this database in several ways, but this two table method might be the simplest method and hope it is a or the correct method.

a. put all the fruits in one basket and give each fruit two numbers, an ID and the immediate branch ID where they were picked from.

b. put all the branches and the trunk in another. each branch are also given two numbers, an ID and the ID of the mother branch that the attach to.

In theory, I should be able to list products in whatever level category/ subcategory I like to and that is the goal of the project.

It would be great if I can get a DB expert's comment.

[bnbwalt]

I work at Boeing and they consider me a "database expert" and I hold several patents on products that are database for the 787.  I also received my membership in the Boeing technical fellowship for data management.  I not sure what qualifies as a database expert for you 🙂  I would like to know because I am always on the lookout to learn more from those that know more and I know they are out there.

B & B Photography is my building for retirement business.

Walt

[bnbwalt]

What you are really describing is what is known as a taxonomy.  Taxonomies can be represented as trees (parent child relationships) but also support more complex relationship like synomyns that end up binding several branches together.

[bnbwalt]

Here is how I would model the category / product objects.

It may look complex but what you are dealing with is essentially a "bill of materials" model with assemblies and sub-assemblies.  This structure also allows for easier expansion if required.  I could not export the output of the database modeling tool that I have as JPG, so there is some interpretation that needs to be done.  This applies to all.  From the Reagent table, Proteins relate only to the Proteins table, Antibodies relates to the Antibodies table, etc

Walt

[Helen_1972]

This is Awesome!

thank you. Combining with the Link David has provided, it is starting to make very much sense.

You certainly qualify as an database expert.

My husband and I are working on this project together. after receiving messages from you and David, he got more and more excited with the learning. He is very into photography (www.longlastingphotos.com) . We are both amazed by those beautiful photography works of yours.