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/>";
}
?>

PHP Tutorial: Connecting to a MySQLi Database

Leave a comment Standard

So, here’s the low down on how to setup a connection to a MySQL database using MySQLi and PHP. This will also make sure your login information is protected. First, you’ll need a database, PHP, and a place to host your files. Once you have those you’re ready to go. Don’t have a hosting provider? No problem, check out XAMPP.

1) You want your login for your database to be protected. That means you want the file to reside in a non-public part of your website. For instance, in most hosting places when you go to view all of your files you know to put them in the public_html folder or the www folder or the index folder. Your database login needs to be in the directory ABOVE your public folder. For most people this is called home, the name of your website, or even the root directory.

2) Create a file called dbh.php (or any other name that suits your fancy) in your home/root directory. That means if people navigate your website they’ll never be able to accidentally access this information because your home/root directory has different chmod permissions (711) then your public_html directory (750).

3) Now you need to write the code to make the actual connection to the database in dbh.php.

<?php
/**
* replace things with { } curly brackets with the appropriate information
* including the curly brackets! don't leave those in there...
* You'll use your database handler ($dbh) when you run your queries
**/

//connect to the server
$dbh = mysqli_connect("localhost", "{username}", "{password}", "{database}")
     or die ('cannot connect to database because ' . mysqli_connect_error());
?>

4) That’s it for the code to connect! Short and sweet. Now the question is how to get it in all your files.

5) Create a file in your public_html folder called index.php. You want this file to be publicly accessible to anyone. Put this inside of it:

<?php
/**
* include your database connection from a protected directory
* by include it from the directory above (../) where this file is
**/
include('../dbh.php');

//do all the rest of your usual php coding here
echo "connection successful!";

?>

If you don’t get an error message when you try to view this php script then you know your include was successful. Now there’s no chance someone can get the login and password for your database and you can access it from your php files like you normally would AND you don’t have to rewrite the connection statement at the top of EVERY php page you make.

Nifty huh?

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: Mass search and replace database field contents

Leave a comment Standard

So recently I needed to remove a bunch of URLs from my member’s content on my games because of spyware/malware notifications Chrome users were getting. It took me a bit to figure out how to do it so here’s a nice and nifty little script that will hopefully save you the same trouble.

<?php
/******
* Purpose: Mass search/replace all database fields
* Author: design1online.com, LLC
* License: GNU
******/

//connect to your database
$host = "localhost";
$username = "your_username_goes_here";
$pass = "your_pass_goes_here";
$database = "your_database_name_goes_here";

//the values you want to find/replace
$find = "what_you_want_to_find";
$replace = "what_you_want_to_replace_it_with";

//spit out the SQL without running it
$test_mode = true; //change this to false and the queries will be run automatically

//if you want to limit the replace to specific tables uncomment this line and enter them below
//$tables_list = array("table_name_1", "table_name_2", "table_name_3");

if ($tables_list)
    $limit_tables = " AND table_name IN ('" . explode("', '", $tables_list) . "')";

//loop through all the tables and all the columns and change $find to $replace
$loop = mysql_query("
    SELECT
        concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,',''/{$find}/'',''/{$replace}/'');') AS s
    FROM 
        information_schema.columns
    WHERE
        table_schema = '{$database}'
        {$limit_tables}")
or die ('cannot loop through database fields: ' . mysql_error());

while ($query = mysql_fetch_assoc($loop))
{
    if ($test_mode)
        echo "{$query['s']}<br/>";
    else
        mysql_query($query['s']);
}