Prerequisites

• Introduction to Spoon

What will you learn?

• Establishing a database connection
• Passing on parameters to a query
  • Passing on a single parameter
  • Passing on multiple parameters
  • Passing on multiple parameters with named identifiers
• Fetching single or multiple records
  • getRecord
  • getRecords()
  • getPairs()

Tutorial

Intro

SpoonDatabase utilizes the PDO extension to handle all communication with your MySQL server(s). Because of its data-access abstraction layer it will make it easier for us to support other database types (such as PostgreSQL) later on. For now, only MySQL is supported.

Test data

To make life easier, all examples in this tutorial are based on the following test data. If you want to work with the same data, you can download a database dump for a test-database named spoondb:

Table: users (click to expand)

Table: users_settings (click to expand)

Establishing a database connection

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

As stated earlier, mysql is the only database driver we support for now. Others are planned for the near future.

For the rest of this tutorial, we'll assume you made a database connection with the variable $db.

Passing on parameters to a query

Passing on a single parameter

Queries usually require one or multiple variable parameters. The following example will illustrate how SpoonDatabase deals with this with its most basic data-retrieval function, getVar(). It returns a single string from a query:

// the questionmark means that a value needs to be assigned in that position
$var = $db->getVar('SELECT username FROM users WHERE id = ?', 1);

Output of Spoon::dump($var):

string(4) "Tijs"

The questionmark in the query above is part of the SpoonDatabase syntax; It means that a value needs to be parsed in the position of the questionmark. Internally, it will sanitize the input to counter SQL injection.

Passing on multiple parameters

To accomplish this, you pass them on in an array. Like so:

// build our query
$query = '
SELECT value
FROM users_settings
WHERE user_id = ? AND name = ?';

// fetch our needed value
$var = $db->getVar($query, array(1, 'website'));

Output of Spoon::dump($var):

string(23) "http://blog.verkoyen.eu"

Note: Always put your parameters in chronological order if you're using the questionmark syntax. The first questionmark matches the first value in the array of parameters, etc.

Passing on multiple parameters with named identifiers

You know those queries that require a gazillion parameters? Luckily, you won't have to put
20 parameters into the right order; for those queries you can use an alternative syntax:

// build our query - the colon (:) is the parameter identifier
$query = '
SELECT value
FROM users_settings
WHERE user_id = :user_id AND name = :setting';

// fetch our needed value
$var = $db->getVar($query, array('setting' => 'website', 'user_id' => 1));

Output of Spoon::dump($var):

string(23) "http://blog.verkoyen.eu" 

As you can see, this syntax does not require the parameters to be put in the right order; They just need to be in the array with their corresponding value.

Note: This syntax applies to all data-retrieval functions of SpoonDatabase: getVar(), getRecord(), getRecords(), retrieve() and execute().

Important: You cannot mix questionmarks and named identifiers in the same query.

Fetching single or multiple records

getRecord()

This function returns a one-dimensional array with the requested database record:

// build our query
$query = '
SELECT
u.id, u.username, u.email,
us.value AS website
FROM users AS u
INNER JOIN users_settings AS us ON us.user_id = u.id
WHERE u.id = :user_id AND us.name = :setting';

// fetch the record
$record = $db->getRecord($query, array('user_id' => 1, 'setting' => 'website'));

Output of Spoon::dump($record):

array(4) {
  ["id"] => string(1) "1"
  ["username"] => string(4) "Tijs"
  ["email"] => string(21) "tijs@spoon-library.be"
  ["website"] => string(23) "http://blog.verkoyen.eu"
}

getRecords()

This function returns a two-dimensional array with the requested database records:

// build our query
$query = '
SELECT
u.id, u.username,
us.value AS website
FROM users AS u
INNER JOIN users_settings AS us ON us.user_id = u.id
WHERE us.name = ?';

// fetch the record
$records = $db->getRecords($query, 'website', 'id');

Output of Spoon::dump($records):

array(3) {
  [1] => array(2) {
    ["username"] => string(4) "Tijs"
    ["website"] => string(23) "http://blog.verkoyen.eu"
  }
  [2] => array(2) {
    ["username"] => string(4) "Davy"
    ["website"] => string(23) "http://blog.bauffman.be"
  }
  [3] => array(2) {
    ["username"] => string(4) "Dave"
    ["website"] => string(22) "http://www.davelens.be"
  }
}

Note: Did you notice the third parameter passed on to getRecords()? This will trigger the database field id as the array key in the result set. This is useful when you cache your records and you need access to a specific record, without executing an additional query. If you'd need the data for user ID 2 in the example above, you could access it just by calling $records[2].

getPairs()

This function returns a one-dimensional array with the requested database records in key/value pairs. In this example u.id will be the key, website will be the value:

// build our query
$query = '
SELECT
u.id,
us.value AS website
FROM users AS u
INNER JOIN users_settings AS us ON us.user_id = u.id
WHERE us.name = ?';

// fetch the records
$records = $db->getPairs($query, 'website');

Output of Spoon::dump($records):

array(3) {
  [1] => string(23) "http://blog.verkoyen.eu"
  [2] => string(23) "http://blog.bauffman.be"
  [3] => string(22) "http://www.davelens.be"
}

Note: getPairs() is especially useful in combination with SpoonForm::addDropdown() to generate a quick dropdown.

Conclusion

Oh my, this was quite the tutorial! You have learned to retrieve a string and single/multiple records from a database using SpoonDatabase. Your queries are safe from SQL injection thanks to the parameter syntax, and your data is ready for use alongside SpoonTemplate.

Luckily for you, at this point the hard part is done. In the next tutorial we will cover insert/update/delete functionality and the execute() function.