By BoLOBOSE payday loan

Statistics – Simple Logging Design

by: Caleb Jonasson - August 4th, 2011   Filed under: php, SQL, Web Development 2 Comments

Introduction

This article is the first in a series focussed on Statistics, Logging and Graphing web application events and information

When creating a web application it is important to keep track of everything a user does. Some people may think that this is a little over the top but the more information we can gather within a web application the better. It also allows simple and easy moderation practices for those that are moderators within the application.

Pre-Planning

This is by far the most important stage in development. During this stage you are going to need to create proper data structures that in theory will not have to be change (although this is extremely rare.) This means that you are going to create the general database that will handle most of the current up to date information and another section that will be used for logging.

Within the main database we are going to have simple things like a user table, a user settings table, a user profile table and a user information table. On tables that hold general information it is important to add a time stamp column that holds the value of last modified. The user should have a column that tells us when the users profile was created. These values aren’t going to really help us when gathering information for a single user simply because the sample of data that we can compare it to is so small. Fortunately these simple values are excellent when we need to pull up values quickly in our system to display user information.

Single User Sample

The logging database is going to help us create a large sample of a single user. This is where we are going to constantly insert information and never delete or modify the records. In one of these tables we can store required values that will tell us when a user has had one of their comments deleted by a moderator. Within this table we are going to log the moderator’s id, the user’s id, the time stamp that this action was performed on. and a numeric value that reflects on the reason that the comment was removed. With this data we can then count the comments that are still alive and well and count the comments that were deleted. With this information we can divide the comments that are still alive and well by the comments that were deleted thus giving us a ratio that we can work with.

Note: It is a good idea to keep track of numbers like this as well and when the ratio was calculated. This will allow us to track user behaviour.

Multiple User Sample

Given that we could have a ratio of how well the user follows rules when it comes to commenting on things and taking part in discussion we filter out the users that are not contributing to the community/application. We can also sort users by this ratio and from there bury into their statistics, activities and logs.

Because we have logged all of the individual comments from this user we can closely examine the user interactions by graphing comments, removed comment, comments removed for x

Tags: , , ,

SQL: Using Unions in Queries

by: Caleb Jonasson - April 12th, 2011   Filed under: SQL Add Comment

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.

Tags: ,

Getting Secured Using Mysqli

by: Caleb Jonasson - April 9th, 2011   Filed under: php, SQL, Web Development 1 Comment

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.

Tags: , , ,

Searching With %?% In a Prepared Statement

by: Caleb Jonasson - April 8th, 2011   Filed under: php, SQL Add Comment

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";
Tags: , , , ,

SQL: One To Many Using Inner Join

by: Caleb Jonasson - September 22nd, 2010   Filed under: php, SQL, Web Development 2 Comments

In this tutorial will be going through it as though we were about to create a browser game that involves units, players, stats, etc.

How Does an Inner Join Work?

Before we get to far into this and start making our own tables you need to first know how the tables are going to do what they do.  We are going to be using a join to connect two tables, this join is called an inner join, with this join type we will be able to link the tables together on a similar column. In this case it will be an ID.

In this game we are going to be needing some players and some units that they will be able to control. So, from within the units table we will have an id that will match the players id. This will allow us to merge the tables on a specific column.

What Will the Inner Join Tables Look Like?

Before we can get to the inner join table we will first need the players table. This will have a name, password and id. The id will have to be auto incremental in order to keep track of the id’s and keep them all unique.

Now that we have our users table we will need to merge them onto the units table to keep track of what units the player owns. The inner join will be performed on the user_id on the table below to the id on the table above.

Once the tables are merged together you will get the following results.

As you can see the table now consists of multiple players and the units. This method of table construction can be very powerful since you can then do another inner join from the units table to the singular unit table. This will allow you to link the individual health, strength, etc of a unit to the player.

SQL Query of an Inner Join

The sql query looks a little odd when you first look at it but once you go through what is going on you will begin to understand it a lot better.

1
2
3
4
5

SELECT
p.id p_id, p.name p_name,
u.user_id u_user_id, u.unit_id u_unit_id, u.id u_id
FROM players p
INNER JOIN units u ON p_id = u_user_id

You can also select all of the users based on the user id. This will involve using the previous statement but it will just require you to add the “WHERE” statement. Your code will now look like this.

1
2
3
4
5
6

SELECT
p.id p_id, p.name p_name,
u.user_id u_user_id, u.unit_id u_unit_id, u.id u_id
FROM players p
INNER JOIN units u ON p_id = u_user_id
WHERE u_id = '54'

This is now where the true one to many statement happens. one player to many units. And if you actually work on a game like this you can expand onwards and use a one to one unit or many to many using linking tables.

Query One To Many Using PHP

In order to query a one to many using PHP will just need to use the following code.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

//store the sql query in the variable $sql
$sql = "SELECT
p.id p_id, p.name p_name,
u.user_id u_user_id, u.unit_id u_unit_id, u.id u_id
FROM players p
INNER JOIN units u ON p_id = u_user_id
WHERE u_id = '2'";
 
//query the database and store the results into $t1. If query fails display the error and kill the page.
$t1 = mysql_query($sql)or die(mysql_error());
 
//This will loop through the results and display them for you
while($row = mysql_fetch_array($t1)){
	echo 'unit id: '.$row['u_unit_id'].'<br>';
}

Additional Notes:

When you are creating the tables keep in mind that every id field should be stored as an integer and should match the limit. If you expect to have a large game down the road make sure that all of your id’s have a length of 11 or 15. That number is up to you but it is best to keep them all at the same length.

Tags: , , , ,

SQL – An introduction to Keys

by: Caleb Jonasson - May 23rd, 2010   Filed under: SQL Add Comment

The Primary Key

Cells inside of your database are all located through an X-axis and a Y-axis. The X-axis is a row and the Y-axis is the column. Because the information inside of a database is stored downward as you go through the table each added record needs a unique identifier to be found. This is where the primary key comes in.

For every table in your database a unique ID is required to find the records and because of this every table has exactly one Primary Key. Without a primary key there would be no way to reference the information stored inside of that row and data would become inaccessible very quickly. This is also the reason why the primary key cannot have the value of “null.”

In a table there can never two of the same key. Two keys would conflict with each other and return bad records, which is why it is impossible to have this. Generally when working on a database you never want to modify the key after it has been created. This would change how the data is accessed and would be a bad idea when it comes to referencing the information should anything go wrong. There are two different kinds of keys. The first kind is a simple key and which is made of one column. The second kind of key is a composite key, which is made of 2 or more columns.

In most cases the Primary key is an integer. This allows simple ordering and counting of records on system however there are cases where a hex number can be used or even a pattern of numbers and letters.

For example, say that we were creating a database for items in a store. Each item has a unique SKU but this is something can change over time so we would still need our primary key to be a number that is completely unique. The naming also will not work for a unique ID due to name changes or the same product name with a change in SKU, which happens over time with different versions and packaging.

Foreign Keys

Foreign keys are used to reference objects or rows from other tables. Theses keys generally reference parent tables from child tables and are used to cross data between tables. This means that the table with the foreign key is always going to be the child in the link.

Unlike the primary key, the foreign key can have a null value, which leads to no link being made between the two tables for that object. These keys also do not need to be unique; in fact they often never are unique do to the nature of how they are used.

An example of the foreign key, continuing from the example used in primary keys, will require us to add another column to the initial table so we will now have ID, SKU, and Vendor. The tables will look like this.

As you can see, in this example we have the foreign key doubling as a primary key in the lower table, which is used in as a vendor in the top table. This is a quick and simple way to store the values easily in the database and comes in handy when working with relationships.

Tags: , , , , ,