24 July, 2013 — Development, Snippets

Simple PHP MySQL Database Class

Update: Now available on GitHub here. Please commit improvements.

A while back now I decided to dive into OOP (Object-oriented programming) in PHP. Until then I had not really had any experience with OOP as functional PHP did the job just fine for me at the time. Now I have had some time getting to know OOP I realised why so many people now use it!

The first thing I wanted to learn was the basics and like with 90% of my projects this would involve a mySQL database connection and queries. That was where I began. Reading about the PDO class (a database package included in PHP) I quickly understood that using the class command “extends” I could write my own class to simplify my database interaction process.

Download

Click here to download my mySQL database class. (Note: this class can be adjusted for other database types)

The Usage

Like with any class we need to initialize the class and assign it to a variable.

$database = new Database();

Then we can do any of the following…

Simple Insert Query
Simply insert records to a table.

// Simple Insert Query
$database->query('INSERT INTO mytable (FName, LName, Age, Gender) VALUES (:fname, :lname, :age, :gender)');

// Bind the data
$database->bind(':fname', 'John');
$database->bind(':lname', 'Smith');
$database->bind(':age', '24');
$database->bind(':gender', 'male');

$database->execute();

Insert Multiple Records using a Transaction
You can insert multiple records using the transaction function. This allows you to run 1 query and multiple binds to insert a lot of rows at once.

// Begin the Transaction.
$database->beginTransaction();

// Query
$database->query('INSERT INTO mytable (FName, LName, Age, Gender) VALUES (:fname, :lname, :age, :gender)');

// Bind the Data
$database->bind(':fname', 'Jenny');
$database->bind(':lname', 'Smith');
$database->bind(':age', '23');
$database->bind(':gender', 'female');

// Execute that data
$database->execute();

// Bind the next set of data
$database->bind(':fname', 'Jilly');
$database->bind(':lname', 'Smith');
$database->bind(':age', '25');
$database->bind(':gender', 'female');

// Execute that data
$database->execute();

// End the transaction
$database->endTransaction();

Get Last Insert ID
Simple – Get the ID of the last record inserted.

echo $database->lastInsertId();

Select a Single Row
Select just a single row of data from a table.

// Query
$database->query('SELECT FName, LName, Age, Gender FROM mytable WHERE FName = :fname');

// Bind data
$database->bind(':fname', 'Jenny');

// Next we run the single method and save it into the variable $row.
$row = $database->single();

echo "
";
print_r($row);
echo "
";

Select Multiple Rows
Get a selection of rows from a table and add them to an array ready to be processed using foreach().

// Query
$database->query('SELECT FName, LName, Age, Gender FROM mytable WHERE LName = :lname');

// Bind data
$database->bind(':lname', 'Smith');

// Run the resultSet method and save it into the $rows variable.
$rows = $database->resultset();

echo "
";
print_r($rows);
echo "
";

Count Rows

// Display the number of records returned
echo $database->rowCount();

I hope you find this script useful. Please comment if you have any questions or found this useful.

Photo by: Chris Hsia