codeNothing? Web Development from a Beginner

Using the previous blog where I showed you how database.php works, now I am going to show you what you can do with that class. First, we need to set up some tables and fill them in:

##
# tables.sql - Show's the table layout used for this example
#
# mysql -u yourusername -D yourdatabasename -p < /path/to/tables.sql
#
# To run from the command line, replace 'yourusername' and 'yourdatabasename' with their 
# respective name, as well as correct the path to your tables.sql file.
#
# Corey Hart @ http://www.codenothing.com
# January 17, 2009
##


#
# Create the users table
#
DROP TABLE IF EXISTS users;

CREATE TABLE users(
	id int,
	first_name varchar(50),
	last_name varchar(50),
	fav_color varchar(50),
	primary key(id)
);


#
# Fill in the table with some values
#
INSERT INTO users(id, first_name, last_name, fav_color) VALUES (1, 'John', 'Doe', 'green');
INSERT INTO users(id, first_name, last_name, fav_color) VALUES (2, 'Jane', 'Doe', 'blue');
INSERT INTO users(id, first_name, last_name, fav_color) VALUES (3, 'Rob', 'Johnson', 'red');
INSERT INTO users(id, first_name, last_name, fav_color) VALUES (4, 'Scott', 'Smith', 'violet');
INSERT INTO users(id, first_name, last_name, fav_color) VALUES (5, 'Calvin', 'Robertson', 'yellow');

Great, now that we have our table set up, lets look at a script that uses this table.

/**
 * example.php - Show's how to use database.php script
 * 
 * Corey Hart @ http://www.codenothing.com
 * January 17, 2009
 */


// Include the database class
require("/path/to/database.php");

// Retrieve the details from the user associated with id #3
$single = $database->hash("SELECT * FROM users WHERE id=3");

// Display that users information
echo $single['first_name'] . " " . $single['last_name'] . "'s favorite color is " . $single['fav_color'] . "<br /><br /><br />";


// Select all other values from the database
$result = $database->query("SELECT * FROM users WHERE id != 3");

// Display each value
while ($row = mysql_fetch_assoc($result)){
	echo $row['first_name'] . " " . $row['last_name'] . "'s favorite color is " . $row['fav_color'] . "<br />";
}

The above script, combined with the table implemented at the top, should have an output of the following:

Rob Johnson's favorite color is red


John Doe's favorite color is green
Jane Doe's favorite color is blue
Scott Smith's favorite color is violet
Calvin Robertson's favorite color is yellow

Easy right? I won't go into full detail as to why this and that works, but I will hit 2 key points. The first of which, is the call to the hash function within the database class

// Retrieve the details from the user associated with id #3
$single = $database->hash("SELECT * FROM users WHERE id=3");

Here, we are using the object decalred at the end of the database class, $database, to call a function within it called hash. This function retrieve's a single row from the database, and set's it to our $single variable as an array. We can then use the values from the selected row as we please.

// Select all other values from the database
$result = $database->query("SELECT * FROM users WHERE id != 3");

// Display each value
while ($row = mysql_fetch_assoc($result)){
	echo $row['first_name'] . " " . $row['last_name'] . "'s favorite color is " . $row['fav_color'] . "<br />";
}

The first part does what I last explained, calls the function query and select's the remaining rows from the MySQL database that aren't associated with the id 3. We then loop through each row and display it's information.

There are many things you can do with this easy to use class. Most importantly, you can add your own personalized functions for customized queries as you need them.