Website Basics 6 – MySQL

Many people on the internet aren't familiar with how to make a decent website on the internet. In an effort to remedy that, this episode kicks off the first of a five part series explaining the different aspects of building a good looking, database driven website from scratch.

In the final part 6 of this series, we will look at the connecting our website to our MySQL database.

Here's a list of the other tutorials in the series:

• Part 1 - HTML
• Part 2 - CSS
• Part 3 - Javascript
• Part 4 - Servers
• Part 5 - PHP

What is a database?

A database is an organized means of storing data and information. There are several different database software programs out there for creating database, but when it comes to websites, MySQL software is most widely used. In the MySQL software, databases are the top level of organization. You can have dozens of different databases depending on your computers storage capacity. Databases classify data into very broad subjects, such as "Financial Database" and "Office Database". Within each database is a table. Tables are used to classify the data into common groups. For instance, in the "Office Database", you could have a table for "Coworkers" and a table for "Shifts". In each table are columns. Columns divide up the data into much more specific groups. Taking the "Coworkers" table from the "Office Database", you can have columns for "ID", "First Name", "Last Name", "Phone Number", and "Address". Within each column is a row or record of data. This is where the actual data goes.

OFFICE DATABASE

Creating A MySQL Database

MySQL is a popular database for websites. Back in our episode of setting up a WAMP server, MySQL, along with PHP, was automatically installed on our server. There are two main ways of creating databases in MySQL. The first is through using PHPMyAdmin. This is the most common interface for web hosting providers. If you were to purchase hosting space from someone, PHPMyAdmin would most likely be your database interface. The second method is through the MySQL command line. Although the command line may seem intimidating, it's actually a much easier and faster method of setting up a database.

Setting up a database through the command line means knowing about database queries. A query is an action request for a database. Some examples of database actions are CREATE, SELECT, and INSERT. Click the query link above for a large list of common database queries. When you open up a the command line interface, it will prompt you for a password. By default there is no password, so hitting enter will bypass this prompt. But for security reasons, we should go ahead and create a password. The default account for the database is called "root", so this is the account that we will need to make a password for. To set a password, you can use this SET query:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('abc123');

It's important to note how each query ends in a semi-colin (;). The capitalization of the letters (the case) is not important. In this instance, it just shows the difference between queries and custom information. Now that we have secured our database with a password, we can view what databases already exist. By default, the MySQL installation comes with a few sample database. To view them, you can use the SHOW query:

SHOW DATABASES;

This returns a list of existing databases. So now let's add our own database to the list. Creating a new database is simple. Just use the CREATE DATABASE query followed by a name that you have chosen for your database. Here's an example:

CREATE DATABASE my_data;

Before we can add stuff to our new database, we have to actually select it. So to do that, we will use the USE query:

USE my_data;

This selects our database and any queries from here on will be executed within our database. So the next step is to create a table within our database. Your database can have several different tables, but for now we will just create one. Using the CREATE query again, we can create a table with a name of our choice:

CREATE TABLE users;

But to make things simpler, we can use CREATE TABLE query and go ahead and attach our column query onto it. When creating columns, you have to include character specifications for each column so that the database knows which characters are and are not allowed. There's four core types to choose from: numbers signified by the type INT (for integer), text and numbers signified by the type VARCHAR, text signified by the type TEXT, and date formats signified by the type DATE. For TEXT and VARCHAR types, you can also specify the number of characters allowed for each column. For instance, most first names are generally less than 20 characters long (the name "gigafide" is 8 characters long). So you can set the character limit for those types by adding it in parenthesis, such as VARCHAR(20). Keep in mind that if the characters are longer than that number, only 20 characters will be stored in the database.

CREATE TABLE users(id int, username varchar(20), password varchar(100));

For records, it's good practice to have a column that stores a unique value that can be used to differentiate between all of the different records. This is why most tables have an ID column. This column of unique values is called a Primary Key. So that we don't have to constantly enter a unique value manually when adding a new record, we can set it to automatically increment with each new addition. To do this, add the AUTO_INCREMENT query to the id column. Also, we need to make this column required so that it is never accidently left blank. So we can add the NOT NULL query to it as well. To wrap it all up, after all the columns are listed, add a PRIMARY KEY query and set it to the column that you want to use as the primary key. In this case, it's the id column. Here is what the final CREATE TABLE query will look like:

CREATE TABLE users(id int NOT NULL AUTO_INCREMENT, username varchar(20), password varchar(100), PRIMARY KEY (id));

Now we have our database, table, and columns. All that's left to do is add data to the columns. For inserting data, we will be using the INSERT INTO query. Then we just add the table name that we want, the columns that we will be inserting values into, and then the values themselves.

INSERT INTO users(username,password) values('gigafide','abc123');

Finally, to view what you've just created, you can use the SELECT query followed by an asterisk (*), which says to select all values.

SELECT * FROM users;

Encryption

When dealing with passwords, it's a good precaution to encrypt password values before they are inserted into the database. There are tons of different available encryptions out there, but the most common one when dealing with MySQL is called Md5. Encrypting a value using the Md5 encryption method is really simple. All you have to do is enclose the value that you want to encrypt with parenthesis and add md5 to the beginning of it. Below is an example where the password value has been encrypted.

INSERT INTO users(username,password) values('gigafide',md5('abc123'));

Be aware that if you want to compare any values to the encrypted database value, the compared value will need to be encrypted as well or else it will not match up with what is in the database.

Connecting a webpage to your database

If you want to use the data in our database with your webpage, you will need to create a script that connects it to your database. The most common language used for interfacing a website with a database is PHP. This is because PHP has a lot of built in functions that makes it easy to get information from databases. For example, to log into a database, you can use a function called mysql_connect and it takes the parameters of your server location, you server username, and your server password. If you are running a WAMP server, your server location will be localhost. Otherwise, it will probably be an IP address or URL of some sort.

mysql_connect("localhost","root","abc123");

To test connectivity, you will need to set this function equal to a PHP variable and then add it to an IF statement to see if there are any errors upon connection. If there are, you can have it return any errors using the mysql_error() function. Otherwise, you can select the database you want to use. This can be done using the mysql_select_db function using your database name and the mysql_connect variable. The full final connection script should look something like this.

<?php
	$db_con = mysql_connect("localhost","root","abc123");
	if (!$db_con)
	  {
		die('Could not connect: ' . mysql_error());
	  }
	  mysql_select_db("my_data", $db_con);
?>

This code can be added directly to the webpage, or it can be added to a page of it's own. As an example, you can save this script to a page called config.php and then link to this page from any other webpage using the include PHP script:

<?php
     include('config.php');
?>

Verifying User Credentials With A Database

One primary use of a database is to store user information and then use that information to verify a user when they log in to your website. In the previous PHP tutorial, we created a form that asked for log in credentials and then passed those credentials to a page that verified them and then logged the user into the website if the credentials were correct. We can use this same method to validate the user using the database, but we have to change a few things first. Here is the original verification code:

<?php
$mypass = "abc123";
$passcheck = $_POST["password"];
if ($passcheck == $mypass){
echo "Welcome, " . $_POST["username"] . "! You are now logged in.
";
$_SESSION['user']= $_POST["username"];
$_SESSION['pass']= $passcheck;
}
else
echo "Sorry, wrong password.
";

?>

The variables store the valid password and the password sent from the form. Since the valid password is now in the database, we can remove the the $mypass variable and replace it with a $usercheck variable that stores the username sent from the form.

$usercheck = $_POST["username"];

Next we need to create a database query that checks to see if the submitted username is actually in the database. We can use the PHP mysql_query function along with the SELECT * FROM query. Then we set the select parameters WHERE the username is equal to the usercheck variable from the form. Then you can set that entire query equal to a variable.

$db_query = mysql_query("SELECT * FROM users WHERE username = '" . $usercheck  . "'");

After that, before checking the password, you need to actually run the query and see if it returns any results. So you can use an IF statement to see if it returns a row. If it does, then you can run the password verification script. If not, then you can echo a result saying that the password is wrong.

if (mysql_num_rows($db_query) == 1){
     ....PASSWORD SCRIPT....
}
else
     echo "Sorry, wrong username. <br/>";

In order to check the submitted password variable with the password from the database, we need to assign the row that was found to an array variable using the mysql_fetch_array function.

$record = mysql_fetch_array($db_query);

This will allow us to select any column from that record. So the column that we want is the password column. To access it, you just type the $record variable followed by the column in question.

$record['password']

With that in mind, and remembering to encrypt the submitted password, our new password check if statement should look like this:

if (md5($passcheck) == $record['password']){

The final script in it's entirety looks like this:

<?php
if (mysql_num_rows($db_query) == 1){
     $record = mysql_fetch_array($db_query);
     if (md5($passcheck) == $record['password']){
	echo "Welcome, " . $usercheck . "! You are now logged in. <br/>";
	$_SESSION['user']= $usercheck;
	$_SESSION['pass']= $passcheck;
     }
      else
	echo "Sorry, wrong password. <br/>";
}
else
     echo "Sorry, wrong username. <br/>";
?>

Adding Form Data To A Database

Retrieving information from a database is only half of the database functionality. With PHP, you can also add values and records to a database. A common reason to do this is to store data from a form, such as adding a user to a website. So, you will need to start out with a typical PHP form, that points to a page that can add the data to the database. We'll call this page "add_user.php". Here's a typical form:

<form action='add_user.php' method='post'>
Name:<input type='text' name='addusername'/>
Password:<input type='password' name='addpassword'/>
<input type='submit' value='Add'/>
</form>

Notice that the password type is set to password. This will make it so that the form field shows only dots instead of revealing the actual password. This form stores the form fields as addusername and addpassword variables and then passes those variables to the add_user.php page that will then insert the values into the database. For the add_user page, you want to make sure you start the session and that you include the config.php page first so that we connect to the database:

<?php session_start(); 
include('config.php');
?>

Then you can add your page theme or template. For the content, you want to run an INSERT query using the variables sent from the form. Before adding the password, however, make sure that it is first encrypted using md5. Then assing this query to a variable, and run it through an IF statement to execute it. If it fails, then display the error. If it succeeds then you can echo "User Added".

<?php
     $db_query="INSERT INTO users (username, password) VALUES ('$_POST[addusername]',md5('$_POST[addpassword]'))";
     if (!mysql_query($db_query)){
          die('Error: ' . mysql_error());
     }
     echo "User Added";
?>

Inserting Database Data Into HTML Tables

A good way to show data from a database on a webpage is by displaying it in an HTML table. The first step in this process is to create a SQL query variable that selects the table or data that you want to display.

<?php
     $db_query= mysql_query("SELECT * FROM users");
?>	

The next step is to use PHP to echo an HTML table with one row and two columns: One for User ID and one for Username.

<?php
     $db_query= mysql_query("SELECT * FROM users");
     echo "<center><h1> User List:</h1><table border='1'>
     <tr>
     <td><b>User ID</b></td>
     <td><b>Username</b></td>
     </tr>
     </table>
     </center>";
?>

We want to add more rows, but we want the rows to be equal to the amount of data records returned from the query. The most effective way to do this is to put the row HTML within a while loop so that it loops a new row for each record until there are no more records. Then display the results in the columns using record array.

<?php
     $db_query= mysql_query("SELECT * FROM users");
     echo "<center><h1> User List:</h1><table border='1'>
     <tr>
     <td><b>User ID</b></td>
     <td><b>Username</b></td>
     </tr>";
     while($record = mysql_fetch_array($db_query)){
          echo "<tr>";
          echo"<td>" . $record['id'] . "</td>";
          echo"<td>" . $record['username'] . "</td>";
          echo"</tr>";
     }
     echo"</table></center>";
?>

Pay close attention to the echo statements and how you have to make sure the PHP code is not enclosed within an echo statement. Each line of the new row within the while loop as it's own echo statement.

Final Code

Here is the final code for the pages used in this tutorial. You can combine these with pages from other tutorials that are not included here.

config.php

<?php
     $db_con = mysql_connect("localhost","root","abc123");
     if (!$db_con){
          die('Could not connect: ' . mysql_error());
     }
     mysql_select_db("my_data", $db_con);
?>

add_user.php

<?php session_start(); 
include('config.php');
?>
<html>
<head>
<title>My Data</title>
		<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>

<?php include("header.php"); ?>
<p class="welcome" id="greeting">
<?php
  $db_query="INSERT INTO users (username, password) VALUES ('$_POST[addusername]',md5('$_POST[addpassword]'))";

if (!mysql_query($db_query))
  {
  die('Error: ' . mysql_error());
  }
echo "User Added";
?>
</p>
<a href="index.php">Click here</a> to return to the main page.
<?php include("footer.php"); ?>

	</body>
</html>

welcome.php

<?php session_start(); 
include('config.php');
?>
<html>
<head>
<title>My Data</title>
		<link rel="stylesheet" type="text/css" href="style.css" />
</head>
<body>

<?php include("header.php"); ?>
<p class="welcome" id="greeting">
<?php
	$usercheck = $_POST["username"];
	$passcheck = $_POST["password"];
	$db_query = mysql_query("SELECT * FROM users WHERE username = '" . $usercheck  . "'");
	if (mysql_num_rows($db_query) == 1){
		$record = mysql_fetch_array($db_query);
		if (md5($passcheck) == $record['password']){
			echo "Welcome, " . $usercheck . "! You are now logged in. <br/>";
			$_SESSION['user']= $usercheck;
			$_SESSION['pass']= $passcheck;
			}
		else
			echo "Sorry, wrong password. <br/>";
	}
	else
		echo "Sorry, wrong username. <br/>";

?>
<a href="index.php">Click here</a> to return to the main page.

<?php include("footer.php"); ?>

	</body>
</html>

Index.php

<?php session_start(); 
include('config.php');
?>
<html>
	<head>
		<title>My Data</title>
		<link rel="stylesheet" type="text/css" href="style.css" />
		<script>
			function clicked(){
			var x;
			var y;
			x = document.getElementById('nameCheck').value;
			y = document.getElementById('password').value;
			if(x !="" && y !=""){
				return true;
				}
				else if(y ==""){
					document.getElementById('errorPass').innerHTML='<font color="red">(required) Password:</font>';
					return false;
					}
				else if(x ==""){
					document.getElementById('errorMsg').innerHTML='<font color="red">(required) Name:</font>';
					return false;
				}
				
				
			}
		</script>
	</head>
	<body>		
<?php include("header.php"); ?>
					<?php
					if (isset($_SESSION['user'])){
					$db_query= mysql_query("SELECT * FROM users");				
						echo "<center><h1> User List:</h1><table border='1'>
							<tr>
							<td><b>User ID</b></td>
							<td><b>Username</b></td>
							</tr>";
							
						while($record = mysql_fetch_array($db_query)){
							echo "<tr>";
							echo"<td>" . $record['id'] . "</td>";
							echo"<td>" . $record['username'] . "</td>";
							echo"</tr>";
							}
						echo"</table>
						</center>";
							}
					else 
						echo "<p class ='welcome' id='greeting'> Please Login:</p>
								<form action='welcome.php' method='post' onSubmit='return clicked();'>
									<b id='errorMsg'>Name:</b>
									<input type='text' id='nameCheck' name='username'/>
									<b id='errorPass'>Password:</b> <input type='password' id='password' name='password'/>
									<input type='submit' value='Click Me'onClick='clicked()'/>
								</form>";
					
					?>
					<?php
					if (isset($_SESSION['user']) && $_SESSION['user']=="gigafide")
					echo "<p class ='welcome' id='greeting'> Add User:</p>
								<form action='add_user.php' method='post' onSubmit='return clicked();'>
									<b id='errorMsg'>Name:</b>
									<input type='text' id='nameCheck' name='addusername'/>
									<b id='errorPass'>Password:</b> <input type='password' id='password' name='addpassword'/>
									<input type='submit' value='Add'/>
								</form>";
								?>
				<p class="content" >This page is a work in progress that will eventually show places for data input, as well as data recall. Check back here for updates and more information! Thanks for visiting!</p>				
<?php include("footer.php"); ?>
	</body>
</html>

Help support my channel: http://www.patreon.com/tinkernut Follow Tinkernut! Google + Facebook Twitter