Database Tutorial

In this quick tutorial you'll learn how to set-up your database connection and execute queries.

With this new version we dropped support for SQLite databases so you must use MySQL or a compatible server (such as MariaDB).

Setting up the connection

The first thing that you must do is open your config.inc.php file, it is inside the framework folder:

sshot-05

Once you have opened it, locate the database entry. Please make sure you're using the correct profile, for this example we'll use the development profile:

sshot-08

Now, add your connection parameters under the appropiate keys. You'll need to provide the following details:

  • driver - Set it to mysql
  • host - Your database host, for example localhost
  • user - Your database user name, for example root
  • pass - Your database user password, in this example we're leaving it empty
  • name - Your database name, for example shop

It should look like this now:

sshot-09

Once you've set up your connection properties you'll be ready to start querying data from an to the database.

Executing queries

Using the database is pretty straightforward, as the default database class encapsulates a PDO object.

To get the instance of the database object, you should call the getDatabase() method from the global $site object:

# Get a grip onto the database object
global $site;
$dbh = $site->getDatabase();

Please note that if the connection parameters aren't correct this will raise an error. If it happens, just check your config.inc.php file.

Once you got the database instance, you can use it to execute SQL queries as you would do with PDO, check the PDO documentation for more details.

For this example we will execute a couple of simple INSERT and SELECT queries.

Important: The default database object uses PDO Exceptions to notify you when an error ocurrs, so bear that in mind and use try/catch blocks for your database code.

Inserting data

In this example we're going to insert an order into the orders table.

As PDO supports prepared statements for added security and performance we'll use one and bind a couple of variables to make sure they get properly sanitized and avoid SQL injection attacks while possible.

Remember, always sanitize SQL input variables no matter where they come from.

# Get a grip onto the database object
global $site;
$dbh = $site->getDatabase();
# Our order properties
$user_id = 195;
$total = 125.50;
# Now execute the SQL query
try {
    $sql = "INSERT INTO orders (id, user_id, total, status, created) VALUES (0, :user_id, :total, 'Pending', NOW())";
    $stmt = $dbh->prepare($sql);
    $stmt->bindValue(':user_id', $user_id);
    $stmt->bindValue(':total', $total);
    $stmt->execute();
} catch (PDOException $e) {
    error_log( $e->getMessage() );
}

As you can see it's just a thin layer over PDO.

Selecting data

Fetching data from the database is as simple as inserting it.

For this example, we'll get the last 10 orders from our orders table:

# Get a grip onto the database object
global $site;
$dbh = $site->getDatabase();
# Forward-declare the result array
$orders = null;
# Execute the query
try {
    $sql = "SELECT id, user_id, total, status, created FROM orders ORDER BY created DESC LIMIT 0,10";
    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $orders = $stmt->fetchAll();
} catch (PDOException $e) {
    error_log( $e->getMessage() );
}
# Check the dataset
if ($orders) {
    # Put your business logic here
}

Running DELETE or UPDATE queries is equally simple.

Go back to previous page