Note: each tutorial contains information about one specific part of Spoon. If something is unclear, post a comment.

Database - part 2

Written by Dave Lens -

Prerequisites

What will you learn?

Tutorial

Intro

Part two in our SpoonDatabase series of tutorials is less heavy, but not less important. After covering several ways to fetch data in part one, we'll take a look at the other SQL statements. For the sake of this tutorial we will insert, update and delete a new user.

Test data

We will use the same test data as illustrated in part one. For your convenience, here's the download link again for the dump of test database spoondb.

Let's start again with a database connection:

// required classes
require_once 'spoon/spoon.php';

// make a connection
$db = new SpoonDatabase('mysql', 'localhost', 'user', 'pass', 'spoondb');

insert/update/delete functions

insert()

We're going to add a new user called “Joe”. The insert() function requires 2 parameters;

  • The table to insert the record in.
  • The record itself.
// set our record values
$record = array();
$record['username'] = 'Joe';
$record['password'] = md5('spoon4');
$record['email'] = 'joe@spoon-library.be';

// the insert function returns the insert ID
$id = $db->insert('users', $record);

The keys in the $record array must match the column-names, followed by their respective values.

The insert function returns the ID of the inserted record. We already have 3 user records so in this example $id will return 4.

update()

Now suppose you want to change Joe's password. Enter the update() function. It requires 4 parameters;

  • The table to perform the update on.
  • The record with columns to update.
  • The WHERE-clause of the update.
  • The parameters used in the WHERE-clause.
// set our record value
$record = array();
$record['password'] = md5('spoon5');

// the update function returns the number of affected rows
$rows = $db->update('users', $record, 'id = ?', 4);

Same deal as with insert(); the keys in the $record array must match the column-names, followed by their respective values.

The update function returns the number of affected rows. Considering we only updated the record with ID 4, $rows will return 1.

The parameter syntax is explained extensively in part one.

delete()

And finally you realise Joe isn't a nice guy at all, so you'd like him removed from the database. The delete() function will take care of business. It requires 3 parameters;

  • The table to perform the delete on.
  • The WHERE-clause of the delete query.
  • The parameters used in the WHERE-clause.
// the delete function returns the number of affected rows
$rows = $db->delete('users', 'id = ?', 4);

Logically, it only requires the condition on which to remove records. It returns the number of affected rows, just like update().

the execute() function

Now say you want to run one of those “special” queries that you'd like to type out yourself. You can do this with the execute() function. For kicks, let's create a query that utilizes
ON DUPLICATE KEY.

In the example below we're going to try and add the user ID “3”. Should that user ID already exist, we'll perform an update of the same record.

// build a query
$query = '
INSERT INTO users(id, username, password, email)
VALUES (:id, :username, :password, :email)
ON DUPLICATE KEY UPDATE password = :updated_password, email = :updated_email;';

// set the query parameters for the insert-part
$parameters = array();
$parameters['id'] = 3; // the ID is our primary key in the DB
$parameters['username'] = 'Dave';
$parameters['password'] = md5('spoon5');
$parameters['email'] = 'dave@gmail.com';

// set the query parameters for the update-part
$parameters['updated_password'] = md5('spoon5');
$parameters['updated_email'] = 'dave@gmail.com';

// execute the query
$db->execute($query, $parameters);

Executing this code will result in the user “Dave” having his password and e-mail updated.

Conclusion

Compared to the previous tutorial, this was a walkover. You have covered the most important functions of SpoonDatabase and are ready to move onwards!