Share the Love! Free Programming eBooks

Leave a comment Standard

GoalKicker.com is giving away these free programming books and I thought I would share the love for anyone who hasn’t seen them yet. There are more available from their website but these are the ones I was most interested in. Enjoy!

Screen Shot 2018-01-31 at 4.26.46 PM

Screen Shot 2018-01-31 at 4.26.54 PM

Screen Shot 2018-01-31 at 4.27.01 PM

Screen Shot 2018-01-31 at 4.27.11 PM

Screen Shot 2018-01-31 at 4.27.18 PM

Screen Shot 2018-01-31 at 4.27.24 PM

Screen Shot 2018-01-31 at 4.27.37 PM

Screen Shot 2018-01-31 at 4.27.44 PM

Advertisements

MySQL Tutorial: Archiving and Loading Archived Table Data

Comments 2 Standard

Let’s say you have a really large table and you want to archive the data to a new table so that your main table is smaller again. This is useful with things like chat logs or game logs which grow large really quickly but you don’t want to lose the data or you want to keep a history of it. Making an archive table from the main table allows you to truncate the main table for faster queries.

Create the Archive Table

If you want to save the contents of the table into another archive table you’ll need to create a copy of the original table with all the same indexes and schema:

CREATE TABLE your_new_archive_table LIKE the_table_you_want_to_archive;

Dump the Data into the Archive Table

Now you have an archive table that matches your original table let’s dump the data into it:

INSERT your_new_archive_table * FROM the_table_you_want_to_archive;

Dump the Data into a SQL file

If you prefer to save your data to a file on the server rather than in another table you can archive it this way instead. Make sure your file has saved and has data in it before you truncate your original table.

SELECT * FROM the_table_you_want_to_archive INTO OUTFILE '/save_path/your_new_archive_table .sql';

Test the Archive Table

Make sure your table has data in it by looking at your new archive table:

SELECT * FROM your_new_archive_table LIMIT 100;

Test the Archive File

Go to the command line and check to make sure you file was created and has data in it by typing the following:

cat /save_path/your_new_archive_table.sql

Empty the Original Table

Make sure your archive table or archive file has data in it before you empty your original table.

TRUNCATE TABLE the_table_you_want_to_archive;

Load an Archive Table Back Into the Original Table

If you decide you want to load your archived data back into the original table you can do the following.

INSERT the_table_you_want_to_archive * FROM your_new_archive_table;

Load an Archive File Back Into the Original Table

Once you have an archive file you can load it back into your database by doing the following:

LOAD DATA INFILE '/save_path/your_new_archive_table.sql' INTO TABLE the_table_you_want_to_archive;

PHP Tutorial: Looping Through & Displaying A MySQLi Result Set

Comment 1 Standard

Recently I’ve seen a lot of people using while loops to retrieve a single record. You only need to loop through your result set when you expect to get more than one row of data returned. If you use a loop even when you only expect one value back from your query then you’re wasting your server’s resources because the PHP interpreter has to do more work to process a loop than it does to return a single result. This assumes you have already connected to the database and have a table to work with.

Let’s say we have a table called members with the following information:

-----------------------------------------------------
| ID  | NAME       | EMAIL              | LOGGED_ON |
-----------------------------------------------------
| 1   | Sam Jones  | sjones@gmail.com   |  False    |
-----------------------------------------------------
| 2   | Dee Walter | deeman@gmail.com   |  True     |
-----------------------------------------------------
| 3   | John Doe   | jdoe@gmail.com     |  False    |
-----------------------------------------------------
| 4   | Lisa Wells | lwells@gmail.com   |  True     |
-----------------------------------------------------

Now our ID field is an auto_increment and unique. That means no two members will have the same ID number. It’s guaranteed that looking up a member by their ID number will return 1 record or no results. 1 record if a member with that ID number is found and no records if a member with that ID doesn’t exist. Anytime you expect to get 1 or fewer records returned there’s no reason to use a loop, instead find the row directly:

<?php

//run the query
$result = mysqli_query($dbh, "SELECT * FROM members WHERE id='1'")
    or die (mysqli_error($dbh));

//fetch the results
$row = mysqli_fetch_array($result);

//display the results
echo $row['id'] . " " . $row['name'] . " " . $row['email'] . " "  . $row['logged_on'];

?>

Since we know there is only one member with the ID equal to 1 we can run our query, fetch the results and then display the data returned from the result.

Now let’s say we want to display ALL of our members. In this situation we know our members table will have zero or more records at any time, especially as new members are added in the future. In this case we expect our result will always, with the exception of when we add our first member, have one or more member in it. Here it’s appropriate to use a loop to display the data.

<?php

//run the query
$loop = mysqli_query($dbh, "SELECT * FROM members")
   or die (mysqli_error($dbh));

while ($row = mysqli_fetch_array($loop))
{
     echo $row['id'] . " " . $row['name'] . " " . $row['email'] . " "  . $row['logged_on'] . "<br/>";
}
?>

ERROR FIX: json_encode returns null even if there is a value being passed to it

Comments 7 Standard

Loosing Data With json_encode?

So this took me several days of pulling out my hair so I’m hoping to save you some hair loss. I had a query pulling data from a MySQL database and then I was encoding the data and sending it back as a json_encode to an ajax file. The strangest thing was happening to my query — it would load a set number of records and then it just stopped, my json_encode would always return a null value even if there was data being passed to it.

Here’s The Solution

Apparently json_encode will only work properly if the data being passed to it is encoded as UTF-8. One of the posts I was pulling back had a Microsoft curly apostrophe in it (darn you user-entered data!).  So the quick solution is to use the utf8_encode() function around your data before you pass it to json_encode. Otherwise it’s time to go through all of your data and make sure it’s encoded UTF-8 first!

PHP Tutorial: Searching and Pagination

Comments 50 Standard

In the work that I’ve done for clients and just in browsing the web I’ve seen a fair amount of strange pagination scripts out there. If you’re looking for a simple pagination solution then you’ve found the right tutorial. This tutorial assumes you know PHP basics, SQL basics, and you can display results from a MySQL query result set.

1. Build The Search Page

In order for this to work you need to create a simple search page. In this example we’ll use a simple member’s table. The member’s table has three fields: member id (key, auto_increment), username (varchar 15) and email address (varchar 25). So our create table SQL looks like this:

CREATE TABLE members (
 id INT NOT NULL AUTO_INCREMENT,
 username VARCHAR(15) NOT NULL,
 email VARCHAR(25) NOT NULL,
 PRIMARY KEY (id)
);

Now that we have our table we can build our PHP search page. We want to be able to search all three fields of our member’s table so we’ll need three text input fields. We wrap a <form> around those input tags so we can use a submit input type to submit our form to the server:

<?php
require_once('database.php'); //connect to your database in this file

//display our search form
echo "<h2>Search Members</h2>
<form method='post'>
    <p>ID: <input type='text' name='id' /></p>
    <p>Username: <input type='text' name='username' /></p>
    <p>Email: <input type='text' name='email' /></p>
    <p align='center'>
        <input type='submit' name='submit' value='Search' />
    </p>
</form>";

/**
* We'll display our search results down here in the next step!
**/
?>

2. Build The Search Query

Now that we have our search form we need to create a query that will generate the results we’re looking for. Inside of our $_POST submit if statement we want to build a query based on which search values have been filled in. Our updated PHP file will look like this:

<?php
require_once('database.php'); //connect to your database in this file

//display our search form
echo "<h2>Search Members</h2>
<form action='{$_SERVER['PHP_SELF']}' method='post'>
    <p>ID: <input type='text' name='id' /></p>
    <p>Username: <input type='text' name='username' /></p>
    <p>Email: <input type='text' name='email' /></p>
    <p align='center'>
        <input type='submit' name='submit' value='Search' />
    </p>
</form>";

/**
* Display Search Results Below Here
**/
//build our query based on what they entered in the form
$sql = "SELECT * FROM members WHERE 1=1";

if (isset($_POST['id']))
   $sql .= " AND id='" . mysql_real_escape_string($_POST['id']) . "'";

if (isset($_POST['username']))
   $sql .= " AND username='" . mysql_real_escape_string($_POST['username']) . "'";

if (isset($_POST['email']))
   $sql .= " AND id='" . mysql_real_escape_string($_POST['email']) . "'";

$loop = mysql_query($sql)
   or die ('cannot run the query because: ' . mysql_error());

while ($record = mysql_fetch_assoc($loop))
   echo "<br/>{$record['id']}) " . stripslashes($record['username']) . " - {$record['email']}";

//this gets the total number of records returned by our query
$total_records = mysql_num_rows(mysql_query($sql));

echo "<center>" . number_format($total_records) . " search results found</center>";
?>

3. The Pagination Function

In the previous step we created the form to pull up all the results but if our member’s table has thousands of records in it our browser can timeout and the loading times can be really slow trying to display a large list of members. To remedy that we add pagination so only a few records appear on the page at a time. Our function will show the page numbers so we can easily add them to multiple parts of the search results (ie before and after the results):

/****
* Purpose: paginate a result set
* Precondition: current page, total records, extra variables to pass in the page string
* Postcondition: pagination is displayed
****/
function pagination($current_page_number, $total_records_found, $query_string = null)
{
    $page = 1;

    echo "Page: ";

    for ($total_pages = ($total_records_found/NUMBER_PER_PAGE); $total_pages > 0; $total_pages--)
    {
        if ($page != $current_page_number)
            echo "<a href=\"?page=$page" . (($query_string) ? "&$query_string" : "") . "\">";

        echo "$page ";

        if ($page != $current_page_number)
            echo "</a>";

        $page++;
    }
}

5. The Completed Script

Now that we have a function to display our page numbers we need to update our search to send the correct information to our function. We also need to update our query to only display a limited number of records. Since we’re going to use a variable in both our query and our function we’re going to define it as a global – that way both the function and the query can see it and we don’t have to pass it to our function or declare it twice. Mix that all together and here’s what you get:

<?php
require_once('database.php'); //connect to your database in this file
define("NUMBER_PER_PAGE", 5); //number of records per page of the search results

/****
* Purpose: paginate a result set
* Precondition: current page, total records, extra variables to pass in the page string
* Postcondition: pagination is displayed
****/
function pagination($current_page_number, $total_records_found, $query_string = null)
{
    $page = 1;

    echo "Page: ";

    for ($total_pages = ($total_records_found/NUMBER_PER_PAGE); $total_pages > 0; $total_pages--)
    {
        if ($page != $current_page_number)
            echo "<a href=\"?page=$page" . (($query_string) ? "&$query_string" : "") . "\">";

        echo "$page ";

        if ($page != $current_page_number)
            echo "</a>";

        $page++;
    }
}

echo "<h2>Search Members</h2>
<form action='{$_SERVER['PHP_SELF']}' method='post'>
    <p>ID: <input type='text' name='id' /></p>
    <p>Username: <input type='text' name='username' /></p>
    <p>Email: <input type='text' name='email' /></p>
    <p align='center'>
        <input type='submit' name='submit' value='Search' />
    </p>
</form>";

/**
* Display Search Results Below Here
**/

//load the current paginated page number
$page = ($_GET['page']) ? $_GET['page'] : 1;
$start = ($page-1) * NUMBER_PER_PAGE;

/**
* if we used the search form use those variables, otherwise look for
* variables passed in the URL because someone clicked on a page number
**/
$id = isset($_POST['id']) ? $_POST['id'] : isset($_GET['id']) ? $_GET['id'] : null;
$username = isset($_POST['username']) ? $_POST['username'] : isset($_GET['username']) ? $_GET['username'] : null;
$email = isset($_POST['email']) ? $_POST['email'] : isset($_GET['email']) ? $_GET['email'] : null;

$sql = "SELECT * FROM members WHERE 1=1";

if ($id)
    $sql .= " AND id='" . mysql_real_escape_string($id) . "'";

if ($username)
    $sql .= " AND username='" . mysql_real_escape_string($username) . "'";

if ($email)
    $sql .= " AND email='" . mysql_real_escape_string($email) . "'";

//this return the total number of records returned by our query
$total_records = mysql_num_rows(mysql_query($sql));

//now we limit our query to the number of results we want per page
$sql .= " LIMIT $start, " . NUMBER_PER_PAGE;

/**
* Next we display our pagination at the top of our search results
* and we include the search words filled into our form so we can pass
* this information to the page numbers. That way as they click from page
* to page the query will pull up the correct results
**/
pagination($page, $total_records, "id=$id&username=$username&email=$email");

$loop = mysql_query($sql)
    or die ('cannot run the query because: ' . mysql_error());

while ($record = mysql_fetch_assoc($loop))
    echo "<br/>{$record['id']}) " . stripslashes($record['username']) . " - {$record['email']}";

echo "<center>" . number_format($total_records) . " search results found</center>";

pagination($page, $total_records, "id=$id&username=$username&email=$email");
?>

Since the function makes things look a little messy I cleaned up my downloadable version so it includes the pagination function via a functions.php file. It also includes the SQL for creating the members table with some data in it. Happy searching!

Try the Working Version or Download The Source Code

MySQL Tutorial: A Beginners Guide

Comment 1 Standard

So, what is a database? If you think you’ve never used a database before you’re wrong. Are you online right now reading this post?? Guess what, you’re reading information from a database. You can be almost 90% certain most any big website you visit they use a database for their online content.

A database is designed to store/fetch/update large amounts of content as quickly as possible. MySQL is a database software you can download for free. In order to interact with MySQL you run queries  using SQL, or structured query language. If you’re looking for a real world comparison — think of SQL as a way to ask MySQL questions about the data it’s storing or a set of instructions you can give it to make it perform different tasks for you. You can find out more about MySQL by visiting the website.

Terminology

A lot of people get confused with some of these terms so I’m going to go over them before I start officially using them.

  • Database – this can refer to two things. People often use it to refer to the database software (MySQL, Oracle) but it also refers to a repository, or large collection of data that consists of several tables. When I use the term database I am referring to the large collection of data, not to the actual software. If I want to refer to the software, I’ll call it by it’s name: MySQL. Think of a database as a workbook in Excel, where it contains multiple spreadsheets yet they’re all a part of the same file.
  • Table – a database consists of many tables. Tables help you gather similar data together, form relationships, and setup constraints/limits to the data you insert into it. In this lesson we’ll be creating and working with several tables that make up all of our map data. Think of tables as a single spreadsheet in excel.
  • Row – a row is a horizontal line of data in a table.
  • Column – a column is a vertical line of data in a table.
  • Keys – in SQL things are identified by keys. Think of a key as a way to distinguish one record from another. In the United States everyone has a unique social security number, that way the government can distinguish one John Smith from another. Sometimes keys can be made up of multiple things. For instance, if you knew one John Smith lived in Florida and the other lived in New Jersey you’d be able to tell the two apart. Keys can work the same way. Falling back on my earlier excel references, think of a Key as one of the column or row headers on the spreadsheet file (A-Z at across the top and down the side).

Creating A Database

For the sake of learning I’m going to walk you through some basic SQL before I introduce combine PHP into the mix. The first thing we need to do is make a database for Pits of Doom. To help you distinguish between when I’m writing SQL code versus PHP code I’m going to precede all my SQL statements with mysql> like you’d see the mysql command prompt screen.

mysql> CREATE DATABASE pitsofdoom

A lot less painful looking thing you thought it would be huh? Most people have a general rule with SQL where they capitalize SQL command words and put their own text in lowercase letters. I believe in some database software the capitalization is required, but it’s not required for MySQL. Choose whichever way you prefer.

Creating A Table

This is nearly as easy as creating a database with a few exceptions. When we create a table we have to give it all the row values we’ll be using and a type for each of those values. For this example let’s think about how we could apply store information about a dungeon map in a game:

Each map has 1 name and multiple levels — we’ll need to know what’s the deepest level you can go in each map.

Each map has lots of data for each level — it’s probably a good idea to separate this data from the map name and level data because there’s so much of it. Also, since we have so much map data we don’t want to repeat things like the map name over and over again (we don’t really need to know the map name more then once)

So, the maps in our game should have at least two tables. One table with the map name and level, the other table with all the data for that particular map. If we draw out a rough sketch of our tables they’d look like this:

Table Name: Map

Fields:

ID: a unique number for this map (number)

Name: the name of this map (string)

MaxDepth: the maximum depth level for this map (number)

Table Name: MapData

Fields:

Id: a unique number for this map data information (number)

MapId: the id of the map this data belongs to (number)

X: the x coordinate for this map value (number)

Y: the y coordinate for this map value (number)

Z: the depth of this place on the map (number)

Value: the value of the field in the map ie X, T, W, E (single character)

Now that we have our tables laid out we’ll convert them into SQL.

mysql> CREATE TABLE map (       id integer NOT NULL AUTO_INCREMENT,       name varchar(100) NOT NULL,       maxdepth integer NOT NULL,       UNIQUE KEY(id))

In this first table we’ve created our map information. We’ve set it up so the ID number must be unique, and so that it’ll automatically increment for us as we add records (so we don’t have to do that ourselves!). You’ll notice anything we wanted to put a number into has a type of INTEGER. Anything we wanted to put text into has the type of VARCHAR and then we specified the maximum number of characters for that field. So in this case the name of our map can’t be more then 100 character’s long.

A visual representation of the table we made would look something like this:

ID Name MaxDepth

These three fields combined make up our map table. Now let’s make our map data table:

mysql> CREATE TABLE mapdata (       id integer NOT NULL AUTO_INCREMENT,       mapid integer NOT NULL,       x integer NOT NULL,       y integer NOT NULL,       z integer NOT NULL,       value varchar(1),       UNIQUE KEY(id))

Visually, this would look something like this:

ID MapID X Y Z Value

All of these fields combined make up the mapdata table. However, our tables need a lot more information then just the columns for the data we’re going to add. Let’s see what it would look like visually if we added some information for a map.

ID Name MaxDepth
1 Round Hill 5

The first map in our game starts out with the ID number 1. If you remember, we setup our map table to auto-increment the ID field. That means it will always add 1 to the value of the ID number. The max depth of the first entry in our table is 5 because there are five levels in our map. Now let’s see what our map data table would look like with some of the first level’s information inserted into it (W = wall, E = empty, X = start, T = treasure):

ID MapID X Y Z Value
1 1 0 0 1 W
2 1 0 1 1 W
3 1 0 2 1 W
4 1 0 4 1 W
5 1 0 5 1 W
6 1 0 6 1 W
7 1 0 0 7 W
8 1 0 8 1 W
9 1 0 9 1 W
10 1 0 10 1 W
11 1 0 11 1 W
12 1 0 12 1 W
13 1 0 13 1 W
14 1 0 14 1 W
15 1 1 15 1 W
16 1 1 0 1 W
17 1 1 1 1 X
18 1 1 2 1 X
19 1 1 3 1 E
20 1 1 4 1 E
21 1 1 5 1 E
22 1 1 6 1 E
23 1 1 7 1 W
24 1 1 8 1 T
25 1 1 9 1 E
26 1 1 10 1 W
27 1 1 11 1 E
28 1 1 12 1 E
29 1 1 13 1 E
30 1 1 14 1 W

Our second table now holds all of the information for our fictional game map. This brings us to our next topic, how in the heck do you add this information to the database using MySQL?

Inserting Values Into A Table

First, let’s try to insert the map information for Round Hill into our map table:

mysql > INSERT INTO map (name, maxdepth) VALUES ('Round Hill', '5')

The insert statement in SQL is comprised of the table you want to insert into, the fields you’re inserting data into, and then the data. The sequence of the data should match the order of the column names you put in. So if we reversed our values to ‘5’, ‘Round Hill’ we’d end up with an error when we tried to insert the text value Round Hill into a number value.

Now let’s insert some of our map information into the mapdata table.

mysql > INSERT INTO mapdata (mapid, x, y, z, value) VALUES ('1', '0', '0', '1', 'W'),         ('1', '0', '1', '1', 'W'), ('1', '0', '2', '1', 'W')

In this case, I’ve actually inserted three rows using one insert statement. Each entry is surrounded by parenthesis. When you insert something, you can use either of these formats, whichever works best for you.

Selecting Values From A Table

Once you have data in a table of your database you’re ready to start working with it. Let’s pretend we a table called members with the following data in it:

ID Name Username Password Email Show_Email
1 Jade Krafsig JadenDreamer ilovehorses jade@design1online.com Y
2 John Doe Jdoe imadope jdoe@gmail.com N
3 Jane Doe Jdoe howdy jane.doe@gmail.com N

We have three members in our database. Let’s pretend John Doe is trying to login using his email address and password. Before we can let him access any member’s only area we need to verify that this username and password are correct, then figure out what his show email status is.

mysql > SELECT id, name, show_email FROM members WHERE email='jdoe@gmail.com' AND password='imadope'

The select statement is broken into several parts, only a few of the basics which I’m going to show you right now. After the word SELECT, you list the name of the columns you want to view. After the word FROM you list the name of the table. The WHERE section is pretty self explanatory. If we ran this query we would get back the following table:

ID Name Show_Email
2 John Doe N

One thing to notice right off the bat, when we select something we always get back a TABLE. That means we can get more then one row of data from a select statement. For instance, take this query:

mysql > SELECT * FROM members WHERE username='Jdoe'
ID Name Username Password Email Show_Email
2 John Doe Jdoe imadope jdoe@gmail.com N
3 Jane Doe Jdoe howdy jane.doe@gmail.com N

In this example, we used an asterisk to tell MySQL we want all the column fields in the table where members have a username that equals Jdoe. Now you have multiple records returned. Take a look at some of the queries below, can you figure out what table would be generated if you ran them?

mysql > SELECT id FROM members WHERE show_email='N'

mysql > SELECT * FROM members WHERE email='jane.doe@gmail.com' OR email='jdoe@gmail.com'

mysql > SELECT * FROM members WHERE 1=1

mysql > SELECT * FROM members

mysql > SELECT username, password FROM members WHERE username != password

mysql > SELECT * FROM members WHERE id >= 2

Think you’ve figured them out? Good! Let’s go over them. The first one finds all members who have the show_email field set to N. The second one selects all columns where the email address is one value OR where the email address is another value. The third query will select everything in the entire database, as will the query after it. The next query shows all the usernames and passwords where the username field and the password field don’t have the same values in them, and the last query finds all members with an ID greater than or equal to the number 2.

Updating Values In A Table

Data doesn’t usually stay static, especially data for a multiplayer online game. People change passwords, write messages, go on quests, and more. In order for the game to reflect that we’ll need to update the data as people play. That’s were update statements come in. Let’s use the same members table we had before. Pretend that John Doe has logged into his account and wants to change his email address:

mysql > UPDATE members SET email='john.doe@gmail.com'

If you realized immediately that this will cause a problem you’ve got the right mindset. If you didn’t notice an error right away look again. Computers are really dumb, they do exactly what you tell them to. If you ran this query, you would change everyone’s password in the game to John Doe’s new email address. What a disaster!

This is where keys become important. In order to identify which record in our database belongs to John and only change data for information that belongs to John we can use the key for our member’s table: ID.

mysql > UPDATE members SET email='john.doe@gmail.com' WHERE id='2'

When we run this query only the email address for John is affected. Perfect!

Update statements can be as easy and as complicated as you want. We could update every other row by figuring out if the id number was odd or even, we could change passwords according to the show_email field, and more. However you have to be careful with update statements. What happens if we did this:

mysql > UPDATE members SET password='heya', email='john.doe@gmail.com' WHERE username='jdoe'

Now we run into the problem of changing multiple records again. In our members table both John and Jane Doe use the same username. If we ran this query, we’d reset both of their passwords to heya and both their email addresses to john.doe@gmail.com.

Take a look at some of these other update queries. Can you figure out what they’d do?

mysql > UPDATE members SET password='test' WHERE id <= 1

mysql > UPDATE members SET username='Jade2', password='newpass' WHERE email='jade@design1online.com' AND username='John'

mysql > UPDATE members SET showing_emails='Y'

The first query sets all member’s passwords to test if their ID number is less than 1. The second query sets both the username and password for all members whose email matches AND have the username John. The third and final query fails, there is no column called showing_emails. If it instead said show_email it would set everyone’s show_email value to Y.

Deleting Values From A Table

Taking things out of your database is sometimes almost as important as putting things in. I run pretty small servers with a lot of active people who play. In order to make queries faster I delete old data. There are other reasons to delete data as well and I’m sure you can think of a few.

So you know, there is no way to delete a certain field in one file. Deletion in mysql is an all or nothing, it either removes the whole row or it doesn’t.

mysql > DELETE FROM members WHERE id='3'

This removes Jane Doe from the members database. Again, you can end up with the same problems deleting as you did updating. Pay careful attention to any delete statement so you don’t delete everything in your database!

MySQL & PHP Working Together

If you’ve never worked with PHP before then I suggest you read my learning php tutorial series. In order to access our database information from mysql we’re going to use php to open a connection to the database and send our queries. I usually make this a file called dbconnect.php and include it at the top of every page I need database access to. Your file will vary based on the username, password, and name of the database you’re using so you’ll need to change this file to work for you:

<?php

/************
* File: dbconnect.php
* Purpose: open database connection
*************/

//open our database connection with the correct username & password
mysql_connect(“localhost”, “USERNAME_HERE”, “PASSWORD_HERE”)
or die
(“could not connect to the database because: ” . mysql_error());

//change to the database we want to use
mysql_select_db
(“DATABASE_NAME_HERE”)
or die (‘could not switch to using database because: ” . mysql_error());
?>

The first function mysql_connect starts the connection to the database. If you’re using a database that’s local to where you host your website then the words localhost will never change. The or die statement at the end of the function is a great way to help you debug your mysql/php code. If something goes wrong while mysql is trying to connect it’ll automatically give you a fairly descriptive error message about the problem.

The second function mysql_select_db switches to the database you’ll be using. Often times you have multiple databases on the same server. For instance, I have two servers. On each server I have anywhere from 2-15 databases. When I run a PHP script, it needs to know which of those databases I’m using before it can try to access any of my tables.

Now you’ve seen two of the great functions PHP has built in to make it easy working with MySQL. Since we can connect to our database let’s start performing operations on it.

<?php
include(‘dbconnect.php’);

//we start off by setting the result of our query to a variable named $result
$result = mysql_query(“SELECT id, name FROM members WHERE id=’1′”)
or die (‘cannot select the member id because: ‘ . mysql_error());

//now we need particular information from that result put into our $row variable
$row = mysql_fetch_assoc($result);

//now we can access the values in our $row variable using the name of the field we want
echo “Hello ” . $row[‘name’] . “! Your member ID number is: ” . $row[‘id’];

?>

This prints, Hello Jade! Your member ID number is: 1

I’m not sure about you but I think that’s totally awesome. But in this example we need to know someone’s ID number before we display information about them. What happens if we want to change that based on a number someone’s entered into a text field? Take a look at this next example:

<?php
include(‘dbconnect.php’);

if ($_POST[‘id’]) //they’ve entered an ID number into the box
{

$id = $_POST[‘id’]; //set this variable to what they entered

$result = mysql_query(“SELECT id, name FROM members WHERE id=’$id'”)
or die (‘cannot select the member id because: ‘ . mysql_error());

//now we need particular information from that result put into our $row variable
$row = mysql_fetch_assoc($result);

//now we can access the values in our $row variable using the name of the field we want
echo “Hello ” . $row[‘name’] . “! Your member ID number is: ” . $row[‘id’];

}
?>

<form action=”#” method=”post”>
Enter an ID number: <input type=”text” name=”id” value=”<?php echo $_POST[‘id’]; ?>” />
<center><input type=”submit” name=”submit” value=”Click Me!” /></center>
</form>

In this example our page now pulls the correct information about whichever member’s ID number you enter. Now this works fine if someone uses a number and its less then 4, but what happens if they enter something else? Can you fix this so it only displays information about the member if the number is valid? Here’s two hints, you can use the function isNumeric($number) to find out if what someone entered is a number, then you should can check to see if the value returned by $row[‘field_name’] has data in it.

Let’s display a list of all of our members. How do we do that? Take a look at this code:

<?php
include(‘dbconnect.php’);

//I always change this variable to loop so it’s easier to read
$loop = mysql_query(“SELECT id, name FROM members”)
or die (‘cannot select the member id because: ‘ . mysql_error());

//now we want all the information that $row finds, not just the top value
while ($row = mysql_fetch_assoc($loop))

{
echo “Member ID: ” . $row[‘id’] . ” – ” . $row[‘name’] . “<br/>”;

}
?>

This goes through and echo’s the ID number and name of every member in our database as long as $row has a value. That means as soon as we no longer have any results returned from our table the loop will stop automatically.

There’s one more thing you need to know before we go on to making an SQL class, and that’s how to update a row.

<?php
include(‘dbconnect.php’);

if ($_POST[‘password’])
{

//notice how we don’t set this equal to a variable now
mysql_query
(“UPDATE members SET password='” . $_POST[‘password’] . “‘ WHERE id=’1′”)
or die (‘cannot select the member id because: ‘ . mysql_error());

echo “You updated the password for Jade!”;

}
?>

<form action=”#” method=”post”>
Enter an ID number: <input type=”text” name=”password” />
<center><input type=”submit” name=”submit” value=”Update Jade’s Password!” /></center>
</form>

In this example we have only one major difference. With the mysql update or delete statement, you don’t need a variable in front of it. Either the update works, or fails. Either a delete works or fails. There’s nothing to store for later use.

Whooo!! Now we’re ready to combine the two skill sets for our game. Instead of writing tons of queries all over the place we’re going to create a SQL class to help us out. It’ll do a ton of the work for us so we can focus on the bigger picture.

Creating A MySQL Class

So, here goes nothing:

<?php
/**************
* File: mysqlobj.php
* Purpose: database class
**************/

class database{    
//variables for this class
    var $database;
    var $host;
    var $username;
    var $password;
    var $classerror;
    var $connected;

    /**************
    * Purpose: default constructor, is called every time we create an object of this class
    * Precondition: host, username & password for the database, database we're using
    **************/
    function database($host, $username, $password, $database)
    {
        if (!$username)
            return errorMsg("You must enter a username");

        if ($username != "root" && !$password)
            return errorMsg("You must enter a password");

        if (!$database)
            return errorMsg("You must enter a database");

        if (!$host)
            $this->host = "localhost";
        else
            $this->host = $host;

        $this->username = $username;
        $this->password = $password;
        $this->database = $database;
        $this->classerror = "Database Error: ";

        //automatically connect to the database
        $this->connect();
    }

    /**************
    * Purpose: connect to the database
    * Precondition: none
    * Postcondition: connected to the database
    **************/
    function connect()
    {
        mysql_connect($this->host, $this->username, $this->password)
        or die ($this->classerror . mysql_error());

        mysql_select_db($this->database)
        or die ($this->classerror . mysql_error());

        $this->connected = true;
    }

    /**************
    * Purpose: end connection to the database
    * Precondition: none
    * Postcondition: close database connection
    **************/
    function disconnect()
    {
        mysql_close();
        $this->connected = false;
    }

    /**************
    * Purpose: check for connected to database
    * Precondition: none
    * Postcondition: connected to the database
    **************/
    function checkconnection()
    {
        if (!$this->connected)
            $this->connect();
    }

    /**************
    * Purpose: query the database
    * Precondition: query to run
    * Postcondition: returns query data
    **************/
    function query($sql)
    {
        if (!$sql)
            return errorMsg("You must enter a query");

        $this->checkconnection();

        $result = mysql_query($sql)
             or die ($this->classerror . mysql_error());

        return $result;
    }

    /**************
    * Purpose: selection query
    * Precondition: fields, table, where
    * Postcondition: returns query data
    **************/
    function select($fields, $table, $where)
    {
        if (!$fields)
            return errorMsg("You must enter a field");

        if (!$table)
            return errorMsg("You must enter a table");

        $this->checkconnection();

        $result = mysql_query("SELECT $fields FROM $table $where")
        or die ($this->classerror . mysql_error());

        $row = mysql_fetch_assoc($result);
        return $row;
    }

    /**************
    * Purpose: update query
    * Precondition: table, fields, where
    * Postcondition: field has been updated
    **************/
    function update($table, $fields, $where)
    {
        if (!$fields)
            return errorMsg("You must enter a field");

        if (!$table)
            return errorMsg("You must enter a table");

        $this->checkconnection();

        mysql_query("UPDATE $table SET $fields $where")
        or die ($this->classerror . mysql_error());
    }

    /**************
    * Purpose: delete query
    * Precondition: table, where
    * Postcondition: row in table has been deleted
    **************/
    function delete($table, $where)
    {
        if (!$table)
            return errorMsg("You must enter a table");

        if (!$where)
            return errorMsg("You must enter a where condition");

        $this->checkconnection();

        mysql_query("DELETE FROM $table $where")
             or die ($this->classerror . mysql_error());
    }

    /**************
    * Purpose: insert query
    * Precondition: table, values
    * Postcondition: row in table has been deleted
    **************/
    function insert($table, $fields, $values)
    {
        if (!$table)
            return errorMsg("You must enter a table");

        if (!$values)
            return errorMsg("You must enter values in the table");

        $this->checkconnection();

        mysql_query("INSERT INTO $table ($fields) VALUES ($values)")
             or die ($this->classerror . mysql_error());

        //id of the row just inserted
        return mysql_insert_id();
    }

    /**************
    * Purpose: find objects in the database then load them into an array
    * Precondition: field, table, and object
    * Postcondition: returns query data
    **************/
    function loadArray($field, $table, $where, $object)
    {
        $loop = mysql_query("SELECT $field FROM $table $where")
                or die ('cannot load object data from table $table: ' . mysql_error());

        $customarray = array();

        while ($row = mysql_fetch_array($loop))
            array_push($customarray, new $object($row[$field]));

        return $customarray;
    }

    /**************
    * Purpose: delete everything in a table
    * Precondition: table
    * Postcondition: all fields in table have been deleted
    **************/
    function truncate($table)
    {
        if (!$table)
            return errorMsg("You must enter a table");

        $this->checkconnection();

        mysql_query("TRUNCATE $table")
        or die ($this->classerror . mysql_error());
    }
} //end of class

/**************
* Purpose: show a formatted error message
**************/
function errorMsg($message)
{
	echo "<center>Error: $message.</center><br/>";
}

/**************
* Purpose: show a formatted success message
**************/
function successMsg($message)
{
	echo "<center>Success! $message.</center><br/>";
}
?>

I know everything’s not color coded but this is such a big class it’ll take me forever. So the short of it is we can use this class to do everything we need to do with our database. Take a look at this test file I made to see if the class was working. Try creating a table named attributes with an ID number and a name. Enter a few values into the table, then run this script:

<?php

include("mysqlobj.php");

//testing connection
echo "<b>Testing connection:</b> ";
$database = new database("localhost", "username_here", "password_here", "databasename_here");
echo "success!<br/>";

//testing query function
echo "<b>Testing query function:</b> ";
$loop = $database->query("SELECT * FROM attributes");
while ($row = mysql_fetch_assoc($loop))
{
	print_r($row);
	echo "<br/>";
}

echo "<br/><br/><b>Testing select function:</b> ";

//testing select function
$result = $database->select("name", "attributes", "WHERE id='3'");
echo $result['name'];

echo "<br/><br/><b>Testing update function:</b> ";
$database->update("attributes", "name='test field'", "WHERE id='12'");
$result = $database->select("name", "attributes", "WHERE id='12'");
echo $result['name'];

echo "<br/><br/><b>Changing Name Back:</b> ";
$database->update("attributes", "name='flexibility'", "WHERE id='12'");
$result = $database->select("name", "attributes", "WHERE id='12'");
echo $result['name'];

echo "<br/><br/><b>Testing Insert:</b> ";
$id = $database->insert("attributes", "name", "name='test'");
echo $id;

echo "<br/><br/><b>Testing Delete:</b> ";
$database->delete("attributes", "WHERE id='$id'");echo "success!";

echo "<br/><br/><b>Testing disconnect:</b> ";
$database->disconnect();echo "success!";

echo "<br/><br/><b>Testing Insert:</b> ";
$id = $database->insert("attributes", "name", "name='test2'");echo $id;

echo "<br/><br/><b>Testing Insert:</b> ";
$id = $database->insert("attributes", "name", "name='test3'");echo $id;

//testing query function
echo "<br/><br/><b>Listing all values in the database:</b> ";
$loop = $database->query("SELECT * FROM attributes");
while ($row = mysql_fetch_assoc($loop))
{
	print_r($row);
	echo "<br/>";
}

echo "<br/><br/><b>Testing Truncation:</b> ";
$id = $database->truncate("attributes");
echo $id;

//testing query function
echo "<br/><br/><b>Listing all values in the database:</b> ";
$loop = $database->query("SELECT * FROM attributes");
while ($row = mysql_fetch_assoc($loop))
{
	print_r($row);
	echo "<br/>";
}

echo "<br/><br/><b>Disconnecting:</b> ";
$database->disconnect();
echo "success!";
?>

What happens when you view this page? Can you see how it’s effecting things in the database? Great!! Now we’re ready to convert everything over to a database in our game.

Summary

There is a lot to digest in this tutorial. Don’t worry if it all doesn’t make sense — try making a database and playing around with it, add things, delete things and update things using SQL or the database class I provided above. Once you feel confident there try assigning database values into variables, make a small form to update values in the database.

Source Files:

Database Tables: database.sql (map file data included)

Mysql Database Class: mysqlobj.php

PHP Tutorial: Free OOP Hangman Game (database required)

Leave a comment Standard

In this tutorial we’ll be modifying the original PHP hangman game so instead of reading from a text file it reads from the database. We’ll also be adding a new word difficulty option. This tutorial assumes you’ve already read the version that doesn’t require a database so I won’t cover the game mechanics, only the changes.

Getting Started

So first thing’s first, we have to create a table in our database to hold all of our hangman words. Our table will consist of two columns, a word column and a difficulty column.

CREATE TABLE IF NOT EXISTS `hangman_words` (
  `word` varchar(50) NOT NULL,
  `difficulty` enum('Easy','Medium','Hard') NOT NULL DEFAULT 'Easy',
  UNIQUE KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `hangman_words` (`word`, `difficulty`) VALUES
('dog', 'Easy'),
('cat', 'Easy'),
('fish', 'Easy'),
('horse', 'Easy'),
('donkey', 'Easy'),
('cow', 'Easy'),
('mule', 'Easy'),
('lizard', 'Easy'),
('leather belt', 'Medium'),
('blue suit', 'Medium'),
('crochet shawl', 'Medium'),
('denim jeans', 'Medium'),
('black tuxedo', 'Medium'),
('seamstress', 'Medium'),
('sibilant', 'Hard'),
('obtest', 'Hard'),
('cicatrix', 'Hard'),
('pother', 'Hard'),
('sudorific', 'Hard'),
('mensch', 'Hard'),
('besot', 'Hard'),
('fulcrum', 'Hard');

Connecting To The Database

Now that we have our database table we need to write a script that connects us to our database. Anytime we want to access something from our database we need to include this connection string. So, let’s create a folder called inc, short for includes, and then store this as a PHP file inside of the folder. That way any time we need to connect to our database we can simply do include(‘inc/database.php’);

<?php
$host = "localhost";
$username = "your_username";
$password = "your_password";
$database = "your_database";

mysql_connect($host, $username, $password)
    or die ('cannot connect to the database because: ' . mysql_error());

mysql_select_db($database)
    or die ('cannot select database because: ' . mysql_error());
?>

Updating The Hangman Class

Now it’s time to update the game so it pulls a word from the database instead of pulling a word from our old wordlist.txt file. First we need to add a new difficulty variable to our class variables:

var $difficulty = 'Easy'; //lets set it to Easy by default

Next we update the loadWords function so it pulls from the database:

    /**
    * Purpose: load words from the the database at the given difficulty level
    * Preconditions: a difficulty level has been set already
    * Postconditions: the word list has been loaded
    **/
    function loadWords()
    {
        $loop = mysql_query("SELECT word FROM hangman_words WHERE difficulty='$this->difficulty' ORDER BY RAND() LIMIT 1")
            or die ('cannot load hangman words for this difficulty');

        while ($data = mysql_fetch_assoc($loop))
            array_push($this->wordList, trim($data['word']));
    }

Next we’re going to add a new function that allows the user to change the difficulty level and starts a new game.

    /**
    * Purpose: allow the player to change the game difficulty
    * Preconditions: the difficulty setting
    * Postconditions: game difficulty is changed and a new game is started
    **/
    function changeDifficulty($option)
    {        
        //change the difficulty level depending on what they selected
        switch ($option)
        {
            case 1: $this->difficulty = "Easy"; break;
            case 2: $this->difficulty = "Medium"; break;
            case 3: $this->difficulty = "Hard"; break;
            default:
                $this->difficulty = "Easy"; //always default to easy
        }

        //empty the current word list and repopulate it with new words
        $this->wordList = Array();
        $this->loadWords();

        //make sure we start a new game once they change the difficulty
        $this->newGame();
    }

Now we need to update the displayGame function to show a drop down option for changing the game difficulty. In addition we’re modifying the drop down box so the current difficulty will always be selected by default in the drop down box.

    /**
    * Purpose: display the game interface
    * Preconditions: none
    * Postconditions: start a game or keep playing the current game
    **/
    function displayGame()
    {
        //while the game isn't over
        if (!$this->isOver())
        {
            echo "<div id=\"picture\">" . $this->picture() . "</div>
                  <div id=\"guess_word\">" . $this->solvedWord() . "</div>
                  <div id=\"select_letter\">
                    Enter A Letter:
                        <input type=\"text\" name=\"letter\" value=\"\" size=\"2\" maxlength=\"1\" />
                        <input type=\"submit\" name=\"submit\" value=\"Guess\" />
                  </div>";

                  if (!empty($this->letters))
                    echo "<div id=\"guessed_letters\">Letters Guessed: " . implode($this->letters, ", ") . "</div>";

            //display the difficulty drop down box
            echo "<div id=\"change_difficulty\">
                    Difficulty:
                        <select name=\"difficulty\"/>
                            <option value=\"1\">Easy</option>
                            <option value=\"2\""; if ($this->difficulty == "Medium") echo " selected=\"selected\""; echo ">Medium</option>
                            <option value=\"3\""; if ($this->difficulty == "Hard") echo " selected=\"selected\""; echo ">Hard</option>
                        </select>
                        <input type=\"submit\" name=\"change\" value=\"Change\" />
                  </div>";
        }
        else
        {
            //they've won the game
            if ($this->won)
                echo successMsg("Congratulations! You've won the game.<br/>
                                Your final score was: $this->score");
            else if ($this->health < 0)
            {
                echo errorMsg("Game Over! Good try.<br/>
                                Your final score was: $this->score");

                echo "<div id=\"picture\">" . $this->picture() . "</div>";
            }

            echo "<div id=\"start_game\"><input type=\"submit\" name=\"newgame\" value=\"New Game\" /></div>";
        }
    }

Now that we have different difficulty levels I think it’s only fitting that we award more points for harder words. Let’s change our guessLetter() function so it looks like this:

    /**
    * Purpose: guess a letter in this word
    * Preconditions: a game has started
    * Postconditions: the game data is updated
    **/
    function guessLetter($letter)
    {            

        if ($this->isOver())
            return;

        if (!is_string($letter) || strlen($letter) != 1 || !$this->isLetter($letter))
            return errorMsg("Oops! Please enter a letter.");

        //check if they've already guessed the letter
        if (in_array($letter, $this->letters))
            return errorMsg("Oops! You've already guessed this letter.");

        //only allow lowercase letters
        $letter = strtolower($letter);

        //if the word contains this letter
        if (!(strpos($this->wordList[$this->wordIndex], $letter) === false))
        {

            //factor in our new difficulty levels
            switch ($this->difficulty)
            {
                case "Easy": $multiplier = 1; break;
                case "Medium": $multiplier = 2; break;
                case "Hard": $multiplier = 3; break;
                default:
                    $multiplier = 1; break;
            }

            //increase their score based on how many guesses they've used so far
            if ($this->health > (100/ceil($this->guesses/5)))
                $this->setScore((5 * $multiplier));
            else if ($this->health > (100/ceil($this->guesses/4)))
                $this->setScore((4 * $multiplier));
            else if ($this->health > (100/ceil($this->guesses/3)))
                $this->setScore((3 * $multiplier));
            else if ($this->health > (100/ceil($this->guesses/2)))
                $this->setScore((2 * $multiplier));
            else
                $this->setScore((1 * $multiplier));

            //add the letter to the letters array
            array_push($this->letters, $letter);

            //if they've found all the letters in this word
            if (implode(array_intersect($this->wordLetters, $this->letters), "") == 
                str_replace($this->punctuation, "", strtolower($this->wordList[$this->wordIndex])))
                $this->won = true;
            else
                return successMsg("Good guess, that's correct!");
        }
        else //word doesn't contain the letter
        {
            //reduce their health
            $this->setHealth(ceil(100/$this->guesses) * -1);

            //add the letter to the letters array
            array_push($this->letters, $letter);

            if ($this->isOver())
                return;
            else
                return errorMsg("There are no letter $letter's in this word.");
        }
    }

The last modification we need to make to the game class is to update the playGame function so it correctly responds to the player changing the difficulty.

    /**
    * Purpose: set or retrieve maximum guesses before game over, or change the game difficulty
    * Preconditions: posted form data
    * Postconditions: game responds to the selections
    **/
    function playGame($_POST)
    {
        //player is changing the game difficulty
        if ($_POST['change']) 
            $this->changeDifficulty($_POST['difficulty']);

        //player pressed the button to start a new game
        if ($_POST['newgame'] || empty($this->wordList))
            $this->newGame();

        //player is trying to guess a letter
        if (!$this->isOver() && $_POST['letter'])
            echo $this->guessLetter($_POST['letter']);

        //display the game
        $this->displayGame();
    }

Updating The Index File

We’re almost done with the new game functionality! Now we need to update our index file so it connects to the database. When our hangman class is running it will need access to the database to load our word list so we’ll make sure to include the database connection file before we start the game object. Open index.php and add our database connection file right after the class includes.

<?php
/***
* File: index.php
* Author: design1online.com, LLC
* Created: 6.06.2011
* License: Public GNU
***/

//include the required files
require_once('oop/class.game.php');
require_once('oop/class.hangman.php');

//connect to the database
require_once('inc/database.php');

Working Version and Source Code

Not as hard as you thought it would be huh? In this tutorial we built on the code we had from our original OOP version of this game to add a difficulty level and updated it to use words from a database instead of from a text file.

Try the working version or Download the source code for this tutorial