PHP and MySQL in Dreamweaver MX

This tutorial will walk you through the following:

  •  Installing MySQL
  •  Set up of phpMyAdmin
  •  Creating your database
  •  Connecting to database
  • phpMyAdmin

    MySQL comes with a perfectly good command line tool for interacting with the database, but a far more popular and user friendly option is to use the phpMyAdmin program. 
    It's a set of PHP scripts that form a front end into your database.  Grab the latest version from www.phpmyadmin.net and extract the contents of the archive into a directory that is accessible via your web server.  This would either be a directory within the document root of your server (defaults of C:\inetpub\wwwroot for IIS or C:\Program Files\Apache Group\Apache\htdocs for Apache), or a virtual folder within your web server. 

    At this point you can fire up your web browser and point it at the phpMyAdmin folder - on my machine this is http://localhost/phpmyadmin/

    If your web server and MySQL server are up and running you should see a page showing you a select box of your databases on the left and a welcome message on the right, displaying the version of MySQL that you are running, where it is running and who you are running it as.  We're using root@localhost - the MySQL admin or superuser.  The version that we are using is 2.3.3pl1 - at the beginning of this month (Feb 2003) 2.4.0-rc1 was released which has a slightly different interface.  Where applicable I will try and alert you to these differences.

    If you scroll down on the right hand side you'll see 2 messages in red - the first telling you to set the $cfg['PmaAbsoluteUri'] directive in the configuration, the second that you are running as the root user with no password - a nice big security hole.

    The first problem we can solve by opening the config.inc.php file in the phpMyAdmin folder.  This is the configuration file for phpMyAdmin and any setting that you need to change for phpMyAdmin you will do in this file.  The Documentation.html that is distributed along with phpMyAdmin contains details of all of the settings and what they do.  The one that we are worried about is $cfg['PmaAbsoluteUri'] - this needs to be set to the full path of the phpMyAdmin installation, including the trailing slash.  I changed mine to http://localhost/phpmyadmin/

    Since the configuration file is a PHP page, you can edit the page in Dreamweavers' Code View.

    On the main phpMyAdmin page there is a link to Users (Privileges in 2.4) - click through to the user management page and we can sort out the problem with the root password.

    The first part of the page displays the current users in your database, as well as what rights they have in the databases. 

    The user that we will want to change is the root user.  You'll notice that the root user appears twice - once for Host localhost and once for Host % - the % symbol is the wildcard.  The reason for having the username appear twice is that you may want to have different privileges for a user that is connecting from the local machine than one that is connecting remotely.  We'll give both root usernames a password.  Click on the Edit link next to the first root username.

    The portion of the form that we are interested in is the Password (in 2.4 it's right at the bottom).  By default it is set to not change the password, so select the Password radio button and type your password into each of the boxes provided.  Click on Go and the page will present you with confirmation:

    Now click Back to go back to the main User page and do the same for the second root username. Once you have done that click Back to get back to the main user page and click on the link Reload MySQL. 

    The reason for doing this is that the MySQL privileges table is read into memory when the server loads, so to make any changes to usernames and passwords take effect, you need to reload the server, thereby forcing MySQL to re-read the privileges table.  In phpMyAdmin 2.4 the privileges are automatically reloaded, so clicking on the Privileges tab to go back to the main Privileges page will immediately give you the error message below.

    If you now refresh the page, you'll get the message:

    Access denied for user: 'root@127.0.0.1' (Using password: NO)

    In order to access the page now that we have set a root password, we need to tell phpMyAdmin what the root password is.  Open up the config.inc.php page if you don't still have it open and scroll down to the line that reads $cfg['Servers'][$i]['auth_type']

    You have 2 options here - changing the auth_type from 'config' to 'http' will cause phpMyAdmin to prompt you for a username and password each time you open the page:

    Your second option is to simply type in your root password

    Save the file and refresh the page again.  Depending on the setting you chose you will either be prompted for a username and password or have the page displayed.  You are now ready to start working with phpMyAdmin.

    Allan Kent

    Allan KentAllan comes from Cape Town, South Africa. He has been implicated in writing for several WROX, glasshaus, Wiley and Apress publications, generally in the 'cool stuff that PHP can do' sections.

    You can catch up with him at his website http://www.mediafrenzy.co.za.

    See All Postings From Allan Kent >>

    Comments

    DBTools is better!!!

    February 25, 2003 by Mané da Ilha
    I think that DBTools manager is better then phpmyadmin. Please, look at this www.dbtools.com.

    Sorry!!!

    February 25, 2003 by Mané da Ilha
    Sorry, the correctly URL is www.dbtools.com.br/EN.

    RE: Sorry!!!

    February 25, 2003 by Tim Green
    Except that DBTools is a Win32 Executable, rather than a web-based administration system. The advantage of phpMyAdmin is that you can ensure that your MySQL databases are administrable from anywhere, regardless of platform.

    DBTools has no such strength.

    It worked for me :o)

    March 2, 2005 by Harry Nibble

    Thanks for this tutorial.

    I had a few questions like "what does that mean" or "now what" but that was to be expected because I was not setting this up on a local machine.   As the tutorial was aimed at a local machine, there is little surprise there.   Also  for some strange reason I could not use phpMyAdmin and had to use Webmin as already installed.

    The part that really helped me was getting DMX to connect.   The followup tutorial was even much more helpful.

    Thanks for a brilliant tutorial 

    See all 7 Comments

    You must me logged in to write a comment.