by Frost

Introduction
In attempting to figure out my next PHP Tutorial to post I decided to do another basic tutorial, as it does seem a lot of people want basic PHP tutorials. As such I decided to do a tutorial about how to pull data from MySQL using GET data in PHP while filtering / sanitizing the data. Using the method provided in this tutorial you should be able to use in a multitude of ways, such as a profile page a "view contents" page etc. Pull out the Mountain Dew and let's get cracking!

The Setup
Setting up a simple DB for testing is necessary, at least just for this tutorial so we will do a view contents table that will hold a viewid, title and content. Keeping things simple is essential so only add fields you need. Time for the SQL Structure:

1
2
3
4
5
6

CREATE TABLE view_content (
	viewid INT NOT NULL auto_increment,
	title VARCHAR(50) NOT NULL,
	content TEXT NOT NULL,
	PRIMARY KEY (viewid)
);

Now for the test data:

1
2
3

INSERT INTO view_content VALUES (1, 'Test 1', 'Testing Content 1.'),
	(2, 'Test 2', 'Testing Content 2.'),
	(3, 'Test 3', 'Testing Content 3.');

Now that we have our SQL table created and the test data added we will go about how to pull this information from the database.

Retrieving the Data from SQL
Gathering the data from MySQL will probably be the most difficult part about the script, as everything else should be straight forward, so given that you have basic MySQL knowledge this should be a peice of cake. Let's just get down to the nitty gritty:

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

<?php
/**********************
File: view.php
Author: Frost
Website: http://www.slunked.com
***********************/
 
// Be sure to change these values to match your databases.
mysql_connect("localhost", "username", "password") or trigger_error("MySQL Connection Failed: " . mysql_error());
mysql_select_db("database") or trigger_error("MySQL Select DB Failed: " . mysql_error());
 
//Check if we have GET data and if so static cast it to an integer.
// Casting it to INT will prevent SQL Injection etc.
$viewID = isset($_GET['id'])?(int) $_GET['id']:false;
 
if ($viewID) {
	// Well we have a valid integer let's try to grab it:
	$sql = "SELECT viewid, title, content FROM view_content WHERE viewid = {$viewID} LIMIT 1";
 
	$result = mysql_query($sql) or trigger_error("Retrieving View Contents Failed: " . mysql_error());
 
	// verify that we have 1 result
	if (mysql_num_rows($result) == 1) {
		$row = mysql_fetch_assoc($result) or trigger_error("Fetching Row failed: " . mysql_error());
 
		$output = <<<OUT
			View ID: {$row['viewid']}<br />
			Title: {$row['title']}<br />
			Content: {$row['content']}<br />
OUT;
	}else {
		$output = "An invalid view id was passed.";
	}	
}else {
	$output = "An invalid view id was passed.";
}
 
echo $output;
?>

As you can see we make sure that we first have a valid ID to attempt to retrieve, if not then we display an appropriate error message. If so we fetch the data and put the data into a string to display at a later point in time. As a note, the URL to access a "view" would be something like http://www.yoursite.com/view.php?id=x . That is that.

Ending Notes
Not a very long tutorial, but sometimes it is simple enough to not require much more than just the script. As stated this is a very simple and broken down "view" script to help you grab the concept and be able to use this for your own site / needs. The key to remember, if you choose not to use an ID be sure to escape any GET / POST data that will be used against the database with mysql_real_escape_string(), if you do not you will be opening yourself up to SQL injection. I did not use that method in this script because we were using an integer which can be statically casted to prevent SQL Injections. As always if you found this script useful, please link to it!