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

Database - part 1

Written by Dave Lens -

Prerequisites

What will you learn?

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.

16 comments

Miguel Angel wrote 2 years ago

Hi!

You say: "For now, only MySQL is supported."
Is it true in the current version of Spoon?
In the documentation you say it depends on the server drivers.

Dave wrote 2 years ago

@Miguel: While it is possible to use SpoonDatabase for other drivers, it is being developed (and tested) only on MySQL for the time being.

Miguel wrote 2 years ago

Hi again!

Is not necessary to close the database connection?
I did not found any method to do that.

Bauffman wrote 2 years ago

@Miguel
There's no need to close the database connection. If the php script ends, the connection will be closed automatically.

drum lessons beginners wrote 1 year ago

Saved as a favorite, I love your website!

social network wrote 1 year ago

Because the admin of this web site is working, no question very soon it will be well-known, due to its quality contents.

enorme terugval wrote 1 year ago

Wonderful beat ! I wish to apprentice whilst you amend
your website, how could i subscribe for a blog website?

The account helped me a applicable deal. I have been a little
bit familiar of this your broadcast offered brilliant clear
idea

south africa tours from uk wrote 1 year ago

Surfing around stumbleupon.com I noticed your blog book-marked as: Database - part 1 - Tutorials.
I'm assuming you book marked it yourself and wanted to ask if social book-marking gets you a good deal of site visitors? I've been thinking of doing some book-marking for a few of my sites but wasn't sure if it would produce any positive results. Appreciate it.

Marilyn wrote 1 year ago

fantastic put up, very informative. I wonder why the other experts of this sector do
not notice this. You should proceed your writing.
I am sure, you've a huge readers' base already!

August wrote 1 year ago

Learning how to interact with women. It get's easier with practice!

iron condor strategy guide wrote 1 year ago

What is this Iron Condor thing I keep hearing about?

Romantic Trip Paris wrote 1 year ago

You could certainly see your enthusiasm within the article you write.
The world hopes for even more passionate writers like you
who are not afraid to mention how they believe. At all times
follow your heart.

The tao of bad Ass wrote 1 year ago

I am curious which blogging and site-building platform you are
using? I'm new to operating a blog and have been thinking about using the Quizilla platform. Do you consider this is a good foundation to start with? I would be extremely grateful if I could ask you some questions through e-mail so I can learn a bit more before getting started. When you have some free time, please be sure to contact me at: bernd_hatley@live.de. Appreciate it

check out my 21 day sugar detox review here wrote 1 year ago

Hi! This post couldn't be written any better! Reading through this post reminds me of my good old room mate! He always kept talking about this. I will forward this write-up to him. Pretty sure he will have a good read. Thanks for sharing!

Jamison wrote 1 year ago

Good way of describing, and pleasant post to obtain information concerning my presentation subject matter,
which i am going to present in college.