How to Setup a MySQL Database

How do you install PHP-MySQL programs on your website?

You've downloaded a new program to install on your website, but it requires a MySQL database be setup, in order to run. Now what?

That's what you're here to learn. And the fact is -- it's easy. All you need do is to know 4 little snippets of information, and finding those 4 is the subject of our quest.

Here's a description of those 4 mystery snippets:

1) The name of the MySQL server
2) A username authorized to access the database
3) A password for that username
4) The name of a MySQL database

In your downloaded program, you will find a Configure page where you are asked to enter these 4. And once you do that, you're ready to install the program using FTP. And then run it successfully.

Does Your Server support PHP?

It's essential if you're going to install and run a PHP-MySQL program. Here's a good way to find out....

First, we'll look at each of these snippets... and then you'll discover where you can go in order to get these for your program.

I. MySQL Server Name

(An aside: on 8 out of 10 webhosts, this name will be 'localhost'.)

This is the name that has been assigned by your webhost, to the server (program) that is MySQL. Your program simply needs to know the name of the MySQL server. When it has the name, it can locate everything it needs.

II. A username authorized to access the database.

This is a security measure. MySQL needs a username-password pair to tell it that it's OK to access the database. Without this security measure, anyone and everyone can get at your database -- not just to read what's there, but to change it, too. That would be a no-no. Hence, the username-password security.

III. A password for that username.

See above.

IV. The name of a MySQL database.

Some programs that you want to install may ask for the name of a database that you have already created. And some programs may create the database for you. Don't be put off by the term "create a database". It simply means to define a name. MySQL uses that name to reserve space for tables to go into the database.



OK? So now we know about the snippets. Where do we go to get those?

Working with the Control Panel of your Website

Let's start with the basics. You have a webhost. You must have, or you couldn't have a website. When you signed up for your website with your webhost, they would have sent you all the info you need to run your website. That's usually in an email they send you. In that email would be the FTP info you need, in order to access your website. (For more about FTP, click here.)

Also, in that email, will be a web address of a "Control Panel" for your website. Sometimes, it's called a Manager or an Administrator program. Along with that web address will be a userid and password that you need to enter when you get to that webpage. So this is the first step. Go to that web address and enter the userid and password you were provided.

Note: let's assume you are using a webhost that does NOT provide a Control Panel for your website. Personally, I wouldn't deal with a webhost who did not provide a control panel, but there are still some around who think (incorrectly, in my opinion) that it's more secure NOT to provide a control panel. All you can do is contact your webhost and ask them to provide these 4 snippets for you. That will work, but since it's a good idea to have access to a PHPMyAdmin program, that's not likely to be available on a system without a control panel.

Once you enter the Control Panel, you'll find links for setting up your Email and your File Manager and other things. But what you're looking for, at this point, is a link that says "Databases" or "MySQL" or something similar.

Note: if you simply cannot find such a link, it may mean that your webhost either does not provide MySQL databases (in which case, you're out of luck) or you need to upgrade your hosting service (read: pay more) in order to have a MySQL database. Once you've upgraded your service, you'll find a database link in the control panel here.

Setting Up Your Database

Once you click on the Database link, you'll come to a Database Setup screen. (Or you may find choices for Setup or Admin. You want to choose Setup, at this point. (Or you may have landed directly in the Setup page...) Here, you'll find a way to define a username and password. So do that. Setup a username and a password to use with your databases.

You'll also likely find here a place where you can create a database. So just choose a name and that'll create the database. (Note: on some webhosts, they've pre-defined a database name for you. So just make a note of that database name.)

If you have a choice of defining a database name AND setting up a username-password, it's a good idea to create the database FIRST. (Although it's no problem, if you do things in reverse order.) After creating the database, then define the username-password pair. When you setup a username-password, the control panel will ask you to authorize how that username can work on the database you've created (or been assigned.)

If you hadn't created the database FIRST, you'll have to go back into the username definition afterwards, in order to do the authorizations. It's just more efficient to create the database first.

Note: In most cases, you'll just give the username full authorization. In fact, I've never run into a situation where I've given the username less than full authorizations. But just know that "authorization" means that you can allow the username only the ability to read the database, but not write to it. Or modify data, but not enter any. Like that. But in most cases, you can just forget about the different choices and just authorize everything.

So there you have 3 of the 4 snippets: a username-password and the database name. What about the server name?

If it isn't shown you clearly at the top of the MySQL setup page, looking something like "mysql.servername.net" then it's a safe assumption that you can use "localhost" as the server name.

(Or you can go to the Admin page on your database webserver - usually called PHPMyAdmin, and the MySQL server name will be displayed there.)

Now, you can enter these 4 snippets in the appropriate place in the Configure page of your program. And then you're ready to FTP the program to your website.

Configuring Your PHP Program

Usually, you will find a file named something like config.php You will edit that file. In there, you'll find something like this:

$sqlserver = "localhost";
$sqluserid = "userid";
$sqlpassword = "password";
$sqldatabase = "database name";

And now you know those 4 snippets. Just modify those PHP statements above by replacing the info between the quotes with the right data. Let's say you've found that the servername is localhost, you've define a username as george, a password as fred and a database name as mydata. You would then end up with this:

$sqlserver = "localhost";
$sqluserid = "george";
$sqlpassword = "fred";
$sqldatabase = "mydata";

And you'll always find instructions in the program how to enter a web address (URL) where the programs starts.

That's it. You're done!

Find Out if MySQL is Installed on your Webhost

Here's a way to tell if the database is properly setup on your website BEFORE you install any PHP-MySQL programs. Just copy/paste this code (using a simple text editor like Notepad), call it testmysql.php

Actually, there is one more thing that it's wise to know about, although by no means essential. You can view the contents of your database by using a program called PHPMyAdmin, which is installed on the majority of webhosts who offer MySQL.

Click here for info about PHPMyAdmin.

Testing if PHP Works on Your Website

Just copy/paste the following code into a text file, call it testif.php and FTP it to your webserver at www.mydomain.com Then run it by going to www.mydomain.com/testif.php

If PHP is properly installed on your webserver, you will see a screen (mostly in blue) that begins with PHP Version X.X.X And followed by a lengthy screen full of data about the PHP installation on your server. That's what you want. That tells you PHP will work nicely. You're done.

However, if you should see a screen that simply says: phpinfo(); OR the screen is mostly blank, then you have NOT got PHP installed on your webserver and your PHP programs will NOT work. You're outta luck.

Here's the code:

<html>

<head>
<title>PHP Test</title>
</head>

<body>
<p><b>A Test of the PHP system</b></p>
<?php
phpinfo();
?>
</body>
</html>

Testing if MySQL Works on Your Website

Just copy/paste the following code into a text file, call it testmysql.php and FTP it to your webserver at www.mydomain.com Then run it by going to www.mydomain.com/testmysql.php

Make sure you change the database server section to reflect the 4 snippets you've setup in your control panel. If the result is a line that reads, "Looks good! Success!" then you are practically guaranteed that any program using the 4 snippets will work successfully. Here's the code:

<?php

$sqlserver = "localhost";
$sqluserid = "userid";
$sqlpassword = "password";
$sqldatabase = "database";

if( !@mysql_connect($sqlserver, $sqluserid, $sqlpassword) ) {
echo("Unable to connect to the database at this time.<br><br>");
exit();
}

if( !@mysql_select_db($sqldatabase) ) {
echo("Unable to locate the database at this time.<br><br>");
exit();
}


$sql = "SHOW TABLES from $sqldatabase";
$resultset = @mysql_query($sql);
if( !$resultset ) {
echo("Unable to run a query at this time.<br><br>");
exit();
}

$row = mysql_fetch_array($resultset);

echo("Looks good! Success!");


?>