Monthly Archives: April 2011

PHP Automatically Include Classes

Pulling In Content

Php has a nice small feature that allows us to automatically load classes that we use in our web application. This will check the designated location’s files and pull in any classes that we reference inside of the application.

function __autoload($class) {
    include $class . '.php';
}
 
$car = new carObject();
$house = new houseObject();
$boat = new boatObject();

Pull Classes From A Path

It is nice to keep all application classes in the same directory. Here is a quick fix to the above code to import everything from the classes folder on the server.

function __autoload($class) {
    include '/class/'.$class . '.php';
}
 
$car = new carObject();
$house = new houseObject();
$boat = new boatObject();

This is a small quick fix that works because the include function works with a simple string.

Password Protecting Your Directory With .htaccess & .htpasswd

Introduction

There are many ways to password protect a website and the simplest way to do this, even though not very secure, is by using an ht password.  This is a quick solution for password protecting a website that is currently under development. The idea behind this prevention is to cut off all directory access unless the user has entered in the proper username and password combination.

Note: When connecting to the server and using data services you need to make sure that the directory that the data service uses is not protected. Applications such as Flex don’t handle this form of protection very well.

Requirements

In order to get this to work you are going to need two files on the server. The first is an .htaccess file and the second is a .htpasswd file. The first file (.htaccess) is going to hold the location, information saying that it is a pass worded directory, and the location of the .htpasswd file.

1
2
3
4
AuthUserFile /full/path/to/.htpasswd
AuthType Basic
AuthName "root"
Require valid-user

What the Code Means

The first line of this file is the direct location to the htpasswd file from the htaccess file. The second line is the authentication type, simple is the most common type used, and requires that the browser is able to handle a 401 request which prompts for username/password insertion. The third line is just a name for the authentication that is taking place. You can just call this root. The final line is the one that is triggering the password requirement.

The .htpasswd File

The .htpasswd file should look a little different. It is going to just store the username and the password connection. Note that the password is being hashed and this requires that you generate the hash for it. If you would like to generate it yourself you can do so using php or apache, however, you can also just click on this link to find a website that will generate it for you. http://www.htaccesstools.com/htpasswd-generator/

1
caleb:28khj%js@as487dga

SQL: Using Unions in Queries

The union is a very nice feature to have when working with pulling multiple items from multiple tables. This article is going to go over how to use a union to join information under designated columns.

What is a Union

A union is a feature of sql that allows us to join information from multiple tables and store it into one singular table. There are a few requirements in order to do this properly and if the requirements are not followed you are going to get a few error messages and/or incorrect data returned.

Rules of the Union Operator
1. The union operator requires the same amount of columns.
2. All of the columns need to have the same name.
3. The union operator needs to be placed between queries.

Column Setup

Because you need to have the same amount of columns and the same names for each columns there is a high chance that rather than parsing the content you will just be displaying generic information such as type, title and content.
Note: There is no limit to the amount of queries you are able to union together but it would end up being a rather large query.

An example of a working union operator.

1
2
3
4
5
6
7
8
9
10
11
SELECT name, job, title, salary
FROM job_1_employees
WHERE job
LIKE ?
 
UNION
 
SELECT name, job, title, salary
FROM job_2_employees
WHERE job
LIKE ?

In this query we are pulling the information from both tables and merging the content into one large list of company and sub company employees. The search is done based on the like operator.

An example of a broken union operator

1
2
3
4
5
6
7
8
9
10
11
SELECT book_nid, author, title, YEAR
FROM book_listings
WHERE title
LIKE ?
 
UNION
 
SELECT magazine_nid, publisher, title, YEAR
FROM magazine_listings
WHERE title
LIKE ?

In order to get a query like this to work properly we are going to have to store the information into some renamed columns to get it to work. Whenever dealing with a work around it is best to create an extra column with a null value rather than changing up the entire query format.

How to Fix the Union Query

1
2
3
4
5
6
7
8
9
10
11
SELECT book_nid nid, author creator, title, YEAR, 'book' AS TYPE
FROM book_listings
WHERE title
LIKE ?
 
UNION
 
SELECT magazine_nid nid, publisher creator, title, YEAR, 'magazine' AS TYPE
FROM magazine_listings
WHERE title
LIKE ?

You will notice that we have changed some of the different columns and created another column that will store the type of field that is being returned. This is a good thing to have when you need to create a link to a specific kind of page or display a special icon.

Getting Secured Using Mysqli

Introduction

The Mysqli class is a great help to working towards proper security when sending and pulling information from the database. It can stop injection attacks in their tracks and will allow you to specify data types allowing you to not let anything bad slip into the system.

In this article I will be going through the step by step approach to creating a query using prepared statements through MySQL. We are going to follow best practices and use the object oriented style to achieve a connection and gather our results.

Connecting to the database

The first thing that we will be doing is connecting to the database. Traditionally this should be done using an outside class to prevent an access to the connection information. The connection string will attempt to connect to the database and open up a persistent connection.

1
$this->connection = new mysqli('localhost','username','password','database');

Creating the prepared statement

The next thing that is required is for us to store the prepared statement. This should be attempted inside of an if statement. This way we can handle any errors appropriately.

1
2
3
4
5
6
7
8
9
10
$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
 
}else{
	die(trigger_error($this->connection->error));
}

Wild cards

You probably noticed that the sql code has something new inside of it. This is the wild card for the sql statement and a ‘?’ is used to display it. This will allow us to bind variables in place of these questions marks and specify their data type.

Bind parameters

Bind parameters is the next step that takes place when there are wild cards inside of the sql statement. These allow us to force a data type into the format of our choosing. There are 4 data types that we can use.

‘I’ Integer
‘s’ String
‘d’ Decimal
‘b’ Blob

1
2
3
4
5
6
7
8
9
10
11
$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
	//Now bind the data to the types.
	$stmt->bind_param('sii', $bookName, $bookYear, $limit);
}else{
	die(trigger_error($this->connection->error));
}

The above function that we just used will take a minimum of two arguments, the first to define the variable types and everything after that is a variable. Notice that all of the type declarations are in the same string. A common error for this piece of code is that you have an invalid amount of arguments being passed. Another error commonly given is that you cannot have data placed directly in the string. You need to reference variables.

1
2
//This is not allowed:
$stmt->bind_param('sii',"the php book", 4, 20);

Execution

Once we have properly set up our bindings we can execute the prepared statement and try to get a result from the database. As you can see in the code below; this can fail and an error message will be stored in case it does. This is why we are going to have it placed inside of an if statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
	//Now bind the data to the types.
	$stmt->bind_param('sii', $bookName, $bookYear, $limit);
	//We will now execute the statement. This will throw an error if unsuccessful.
	if($stmt->execute())
	{
 
	}else{
		die(trigger_error($stmt->error));
	}
}else{
	die(trigger_error($this->connection->error));
}

Number of Rows

The next step in our quest for world domination is to store the results inside of the statement variable. This is going to let us make a call for the number of rows so we can handle the case of no rows being returned.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
	//Now bind the data to the types.
	$stmt->bind_param('sii', $bookName, $bookYear, $limit);
	//We will now execute the statement. This will throw an error if unsuccessful.
	if($stmt->execute())
	{
		//check the number of rows.
		$stmt->store_result();
		if($stmt->num_rows >= 1)
		{
 
		}else{
			echo 'Nothing was returned from the database.';
		}
	}else{
		die(trigger_error($stmt->error));
	}
}else{
	die(trigger_error($this->connection->error));
}
<pre>
<h2>Binding Results to Variables</h2>
The last thing that is required is binding the results and looping through the results. As you can see we are actually creating variables with this function and storing the information into them. The variables created are gathered from the select part of the statement. This means that we need to define the variables in the same order as the sql statement.
<pre lang="PHP" line="1">
$sql = "SELECT id, name, year FROM tbl_books WHERE name = ? AND year = ? LIMIT ?";
 
//store the prepared statement into the $stmt variable.
//note that the $stmt is a common naming for prepared statements.
if($stmt = $this->connection->prepare($sql))
{
	//Now bind the data to the types.
	$stmt->bind_param('sii', $bookName, $bookYear, $limit);
	//We will now execute the statement. This will throw an error if unsuccessful.
	if($stmt->execute())
	{
		//check the number of rows.
		$stmt->store_result();
		if($stmt->num_rows >= 1)
		{
			//bind the results
			$stmt->bind_result($id, $name, $year);
			//loop through the results.
			while($stmt->fetch())
			{
				echo "$name: $year<br>";
			}
		}else{
			echo 'Nothing was returned from the database.';
		}
	}else{
		die(trigger_error($stmt->error));
	}
}else{
	die(trigger_error($this->connection->error));
}

It is now time to run your code and gather the results using the fetch function. If you only have to return one row from the database then you can just use the fetch function outside of the while loop and it will allow you to pull the results and use the variables in the same way you would in the while loop.

Searching With %?% In a Prepared Statement

A while back when working on a project I learned that when using the wild card: ‘%’ in SQL with a prepared statement you are going to get an error message. This is because the prepared statement class has an issue with a wild card surrounding wild cards. ‘%?%’.

Because of this a simple sql query such as this is going to have issues pulling data from the database.

1
$sql = "SELECT * FROM tbl_comments WHERE comment LIKE %?% LIMIT 20";

Rather than attempt to get the above code to work you are going to need to take a separate approach and store the wild cards in with the search variable. This only takes one extra line of code and your search will be back up and running in no time at all.

1
2
$comment = '%'.$comment.'%';
$sql = "SELECT * FROM tbl_comments WHERE comment LIKE ? LIMIT 20";

Create Your Own PHP/AMF Data Service For Flex

This is a quick and short tutorial on how to create your own data services for flex to read. Also, note that in this tutorial we will be focussing on what a PHP Developer would be doing and not what a Flex Developer would do, we are not going to go over setting up data services or Flex, we are just going to be writing out classes.

Classes

The class is a rather simple thing to write out when focussing on creating an object that flex will be able to easily read. The trick to creating proper services is knowing what flex can do with your functions and your class.

The first thing to know is that flex is not going to be passing any values into the class and the class is not going to behave in a similar fashion to flex. This means that everything you do needs to operate on a function level.

The Construct

The construct is going to allow you to store information into the classes variables such as anything else and thus it is a good place to initialize and store a connection.

There isn’t a standard class declaration line within Flex and thus you cannot simply pass arguments through the parameters of the class.

The construct should look like this:

1
2
3
4
5
6
public function __construct()
{
    session_start();
    $this->connection = new mysqli('localhost','root','password','databasename');
 
}

Function Error Returns

Remember that the front end development team is going to need to know how to handle every situation of data that they receive and they will need to handle any scenario. Because of this you are going to need to return a status and message within the object in case anything goes wrong. A common standard is to simply store a boolean of if it worked as the first value returned. If the boolean is a false value then we know that the second object in the array is going to be an error message or an error code letting the front end know what went wrong.

Function Arguments

When setting up your arguments in the parameters of a function you are going to need to break away from pre setting the value of the argument with:

1
public function getMeta($metaName, $metaType = "video"){}

Values that are pre set in PHP do not show up in flex as an optional parameter.

Returning the Object

When using PHP/AMF as a data service it is good likely that you will need to return some rather complex objects to the front end team. The best way to pass data and allow them to use it is to store the information within an multi-dimensional array.

This means that when you use something like a prepared statement, you are going to need to handle the data and not just throw it back in a PHP array. If you throw back an unhandled mysqli array the front end team may kill you, especially if you have joins within your connection. This will force them to recreate an object from a singular one dimensional array.

Example of a custom Data Service.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
public function pullVideoFromUser($userNid)
    {
        //Query the database and pull video information
        $sql = "SELECT t.name tour_name, t.tour_nid video_nid, tm.created tour_created, tm.modified tour_modified, tm.viewcount tour_viewcount
            FROM tbl_user_video ut
            INNER JOIN tbl_video t ON ut.video_nid = t.video_nid
            INNER JOIN tbl_video_meta tm ON tm.video_nid = tu.video_nid
            WHERE u.user_nid = ?";
 
        $videoReturn = array();
        if($stmt = $this->connect->prepare($sql))
        {
            $stmt->bind_param('i', $userNid);
            $stmt->execute();
            $stmt->store_results();
            if($stmt->num_rows >= 1)
            {
                $stmt->bind_results($key, $name, $nid, $created, $modified, $viewcount);
                while($stmt->fetch())
                {
                    //create an object holding the appropriate information
                    $videoReturn[] = array('status' => 1,'key' =>$key, 'name' => $name, 'nid' => $nid, 'created' => $created, 'modified' => $modified, 'viewcount' => $viewcount);
                }
            }else{
                //Store a status of false and return an error message within the object.
		$videoReturn[] = array('status' => 0, 'message' => 'There was an issue returning the video.')
	    }
        }else{
            //Store a status of false and return an error message within the object.
	    $videoReturn[] = array('status' => 0, 'message' => $this->connection->error);
	}
        //return the object
        return $videoReturn;
 
    }