121 Access to MySQL Tutorial by chenmeixiu

VIEWS: 10 PAGES: 18

									121 WAM! for ISP's - Offer database maintenance capability to your customers                                                Page 1 of 18




121 WAM! Client
Overview
                               121 Access to MySQL Tutorial
Features
Screen Shots                   How to transfer data from Microsoft Access to the MySQL database provided
Client FAQ                     by your ISP
Tutorials
121 WAM!                       Microsoft Access is a great database for use on your personal computer. It has a great data entry
Server                         interface but, unfortunately, it is not robust enough for most web development projects. Don't worry. If
Payment Proxy                  you have a web hosting plan with a popular ISP, chances are that your plan includes a free MySQL
Server                         database. This article explains how you can upload your data from Microsoft Access to your MySQL
                               database space and thus enjoy the best of both worlds, data entry in Access and the scalability of
121 Store                      MySQL on the web server.
Builder
                               The article also comes with a small sample application, a database-driven reciprocal link directory for
                               your website. The application allows you to manage your outgoing links and is available in ASP for
                               Windows hosts and in PHP for those hosting their site on a Unix-based host such as Linux or FreeBSD.

                               The tutorial consists of the following parts:

                                   l   Getting started
                                   l   The sample web application
                                   l   Uploading your data
                                   l   Uploading your web application

                               Getting started

                               Required downloads

                                   l   121 WAM! Client
                                       During this article, we will use 121 WAM! Client. This handy utility is a special FTP client for
                                       databases. It allows you to drag and drop tables from one database into another over the
                                       Internet. 121 WAM! also allows you to perform basic database management tasks such as
                                       changing field types, previewing your data, and more. Additional information about 121 WAM! is




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                   5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                                Page 2 of 18



                                       available at www.121software.com/121wam. The 121 WAM! website also contains a free 30 day
                                       trial version. Note that you will need to download the 121 WAM! Client only. 121 WAM! Server is
                                       not required for the purpose of this tutorial.

                                   l   MyODBC database drivers
                                       In order to connect to MySQL databases, you will also need to download the MySQL ODBC
                                       database driver. Since we are going to use 121 WAM! Client in direct connection mode (without
                                       121 WAM! Server), the MySQL ODBC drivers need to be installed on your PC. You can download
                                       the drivers from www.mysql.com. Click on the 'download' link at the top of the MySQL website
                                       and download MyODBC version 3.51.

                                   l   Sample application
                                       Finally, you can download our sample link directory script here. The package includes all sample
                                       files as well as the Microsoft Access database file. Both an ASP and a PHP version of the script
                                       are available in the package.

                               What you need to know from your ISP

                               When you request your ISP to set up a MySQL database for you, they will provide these details:

                                   l   MySQL server address
                                   l   Database name
                                   l   User name
                                   l   Password

                               You will need to tell your ISP that you want to connect to the MySQL database directly from your PC. It
                               is important that you let the ISP know about this, because the database administrator needs to grant
                               you specific permission to connect to the database from outside of their local network.

                               If you are located behind a firewall (most corporate offices have firewalls), then you should also ask
                               your systems administrator to open port 3306 for you. This is the standard port used by MySQL for
                               database connections. If you use a direct modem or home DSL connection, this limitation will probably
                               not apply to you.

                               The sample application

                               A reciprocal links directory for your site




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                    5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                                  Page 3 of 18



                               For the purpose of demonstrating how to upload data from Access to MySQL, we have built a sample
                               application, a database-driven link directory. You can use this to manage reciprocal links that you
                               exchange with other sites.

                               The database only contains two tables: categories and links. The categories table contains topics such
                               as 'News', 'Business', 'Sport', etc. This allows you to break up your links directory into multiple pages
                               and limiting the number of links that appear on each. The links table contains the URL's and other
                               information about the sites that are listed in your directory. As you can see below, the links table
                               contains a field called categoryid. This is the foreign key that relates to the id field in the categories
                               table.

                               The screen shot below shows the database structure:




                               Editing data

                               Editing data is quite straightforward. Simply open the database file (linkdirectory.mdb) in Microsoft
                               Access. You can then see the two tables, as shown below.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                     5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                                 Page 4 of 18




                               Double-click on categories in order to edit data in that table. Initially, the table is already populated
                               with some sample data, as shown below. You can modify these categories or add your own. The table
                               contains two fields:


                                Field          Description
                                id             Record identifier. This can be any number. Each number must be unique, i.e. you
                                               cannot enter the same number twice.
                                categoryname This is the title of the category. You may create as many categories as you like.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                     5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                                   Page 5 of 18




                               To edit the links that appear in your directory, double-click on the links table. Again, the table is pre-
                               populated with sample data.


                                Field       Description
                                id          Record identifier. This can be any number. Each number must be unique, i.e. you cannot
                                            enter the same number twice.
                                categoryid This is the foreign key that relates to the id field in the categories table. For example, the
                                           'New York Times' link belongs to the news category because the value in the categoryid
                                           field is 1. This matches the news record in the categories table because the id of that
                                           record is 1.
                                title       The link title.
                                url         The link URL. This must always start with 'http://'.
                                description A brief description of the site. A maximum of 255 characters are allowed.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                      5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                                Page 6 of 18




                               Uploading your data

                               Opening your local database

                               To upload the database, we will use 121 WAM! Client. After you have downloaded and installed this
                               program, you will find a shortcut to it by clicking on the 'Start' button in the bottom left on your
                               screen. The shortcut is located under Programs | 121 WAM! Client.

                               Launch 121 WAM! Client and then select 'Direct Connection' from the 'Database Mode' button in the
                               toolbar. 121 WAM! Client has three connection modes:

                                   l   File connection - This allows you to upload files and folders to any standard FTP server.
                                   l   Data connection (121 WAM! Server Connection) - Connect to databases through 121 WAM!
                                       Server. This option can only be used if your ISP has 121 WAM! Server installed.
                                   l   Data connection (direct connection) - Connect to databases directly, using the database drivers
                                       installed on your ISP. This can be used to connect to any supported database without the need
                                       for 121 WAM! Server.

                               Because your ISP may not have 121 WAM! Server installed, we will be using direct connection mode.
                               To establish your connection, click on the 'Data Mode' button in the toolbar and select 'Direct




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                   5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                            Page 7 of 18



                               Connection'.




                               When 121 WAM! Client is in database mode, expand the 'Microsoft Access File' node and click on 'Add
                               New File', as shown below:




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                               5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                                Page 8 of 18



                               A dialog window will now appear, allowing you to select the location of the Access file you would like to
                               open.




                               After opening the file, the database is added to the tree below 'Microsoft Access Files'. You can expand
                               it to see the table structure as shown below.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                   5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                         Page 9 of 18




                               Opening the remote MySQL database

                               Open the MySQL Servers tree in the right pane of 121 WAM!. Then double-click on 'Add New MySQL
                               Server'.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                            5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                            Page 10 of 18




                               Enter the MySQL connection details that were given to you by your ISP and then click on 'OK'. The
                               'Using TCP/IP connection' checkbox must be ticked if you want to connect to a database over the
                               Internet.

                               Note: If 121 WAM! cannot detect the MySQL drivers, you will need to download and install them. Visit
                               www.mysql.com and download MyODBC version 3.51.




                               The connection now appears in the right pane below 'MySQL Servers'. In the screen shot below, the
                               connection is called 'localhost'. In your case, the name may be different.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                               Page 11 of 18




                               Transferring data from Access to MySQL

                               Now that the local and the remote databases have been opened, you can upload tables by dragging
                               them from the left pane onto the database in the right pane (called 'localhost' in the example below).
                               Alternatively, you can one or more tables in the left pane, select the destination database on the right
                               and then click on the 'Send ==>' button. Other options such as append and synchronisation are also
                               available. The data transfer process is very simple and intuitive.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                   5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                              Page 12 of 18




                               The graphic below shows 121 WAM! after the tables have been transferred.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                  5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                                   Page 13 of 18




                               Uploading your web application

                               Configuring the sample web application

                               Our sample application, a reciprocal links directory script, consists of a single file called links.asp or
                               links.php. You only need one of these files. It is most likely that you have a Unix/Linux web hosting




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                        5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                              Page 14 of 18



                               plan. In this case you will need the PHP version (links.php). On the other hand, if your ISP uses
                               Windows servers, you will need to use the ASP version (links.asp). You can delete the unused file.

                               Open the relevant file (either links.asp or links.php) using a text editor such as Notepad. The database
                               connection information is contained at the top of the file. The information for the ASP and PHP versions
                               are slightly different, examples are shown below:

                               Database connection information in the ASP file:

                                connectionString = "Driver={mySQL ODBC 3.51 Driver};" & _
                                                    "Server=<MySQL server address>;" & _
                                                    "Port=3306;" & _
                                                    "Option=131072;" & _
                                                    "Stmt=;" & _
                                                    "Database=<database name>;" & _
                                                    "Uid=<user name>;" & _
                                                    "Pwd=<password>"


                               Database connection information in the PHP file:

                                $dbhost   = "<MySQL server address>";
                                $dbname   = "<database name>";
                                $dbuser   = "<user name>";
                                $dbpw =   "<password>";


                               As you can see, tags have been inserted as placeholders. You will need to replace these with the
                               database connection information provided by your ISP.


                                Tag                          Replace with:
                                <MySQL server address>       Replace with the domain name (without http://) or the IP address of
                                                             your MySQL server, e.g. 'www.acme.com' or '165.32.46.172'.
                                <database name>              Replace with the database name as assigned to you by your ISP, e.g.
                                                             'acme'.
                                <user name>                  Replace with the database user name as assigned by your ISP, e.g.
                                                             'acme_admin'.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                  5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                            Page 15 of 18



                                <password>                   Replace with your database password.


                               After replacing the placeholder tags, your connection string may look as follows:

                               ASP version:

                                connectionString = "Driver={mySQL ODBC 3.51 Driver};" & _
                                                    "Server=165.32.46.172;" & _
                                                    "Port=3306;" & _
                                                    "Option=131072;" & _
                                                    "Stmt=;" & _
                                                    "Database=acme;" & _
                                                    "Uid=acme_admin;" & _
                                                    "Pwd=XXXXX"


                               PHP version:

                                $dbhost   = "165.32.46.172";
                                $dbname   = "acme";
                                $dbuser   = "acme_admin";
                                $dbpw =   "XXXXX";


                               After making these changes, save the file on your hard disk.

                               Uploading your web application

                               In file mode, 121 WAM! Client acts as a normal FTP client that can be used to connect to any standard
                               FTP server. This is how we will upload our web application.

                               Click on the file mode button in the 121 WAM! Client toolbar to switch to file mode.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                                Page 16 of 18




                               A dialogue window will now open. Click on 'Add New' and enter your FTP details. Then click on
                               'Connect' to connect to your FTP server. Note that you will need to enter the FTP details only the first
                               time. Thereafter, the details will be saved and you can select it from the profile list on the left.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                    5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                              Page 17 of 18




                               After 121 WAM! has successfully connected to your FTP server, it shows the folders and files contained
                               on the server in the right pane. Open the folder that connects your web application in the left pane, as
                               shown below. Then select the files you wish to upload, click on the folder you want to put them in and
                               click on send. The files will now be uploaded.




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                   5/24/2003
121 WAM! for ISP's - Offer database maintenance capability to your customers                                             Page 18 of 18




                               After the file has been uploaded, you should be able to access it through a web browser, e.g. point
                               your browser to http://www.acme.com/links.php (replace 'acme.com' with your domain name).




http://www.121software.com/121wam/tutorial-access-mysql.asp                                                                  5/24/2003

								
To top