Database Basics The power behind your web application

Overview Objective Learn the basics of connecting to a database and executing sql queries ProdigyView. Requirements A Mysql, Postrgesql or MSSQL database Estimated Time 10 Minutes

Follow Along With Code Example Download a copy of the example code at www.prodigyview.com/source. Install the system in an environment you feel comfortable testing in. Proceed to examples/database/Database.php

A Brief Note About The Database Even though you can write a complete application in ProdigyView without using the database, it is more than likely that you are going to need the database at some point. The great feature that PVDatabase has is that it can connect to MySQL, PostgreSQL and MSSQL. In this tutorial we are going to learn: Connecting to a database Executing A Query Iterating Through Results Current development is being done for MongoDB and SQLITE

Disabling the Database If we are going to learn about truly manipulating the database, let’s first boot ProdigyView in a way that disables the database. Turn the database initialization off and loading the plug-ins off since they require a database connection. Boot System Disable Options

Set a database Good, now with the database not initialized, we are going to configure a connection. Similar to the database configuration file, we are putting our database variables in an array.

Time to Connect In our last slide, we created configuration paramters for a database we have access too. Next we are going to add the connection to PVDatabase and then connect to it. Name of the connection Connection Parameters Connect to a specified database. Use to switch between connections

Create a table We are going to need a table to test with, so now lets create one. The process of creating tables is in another tutorial but we will give you a glimpse of it.

Sanitizing Data When a user is inputting data, it is a general rule NOT to trust that data because it might contain malicious code that can wreak havoc on your database. Before inputting any information into your database through a query, sanitize it through makeSafe() function. Data after it is sanitized Data to be sanitized Data to be sanitized

Executing We have our connection set We have a table and columns We sanitized our information We are ready for executing our first query. Executes any query Insert query with our sanitized data

Again but get the id Great, we now know how to insert data into the database. But what if we want the id of the last entry? No problem. Let’s run our example again except this time we are going to execute using the return_last_insert_query(); The column that contains the auto incrementing id The table to return the column from The generated id

A Closer Look The method return_last_insert_query(); takes the query and to additional arguments. The arguments are the column to be returned and the table to return the column from. These options are here because some databases require that there is a specified column or table to return the last generated id. If you want to make your application database agnostic, it is suggested that these parameters be used.

Find Results – Row Count PVDatabase::query executes any query so it can be used for creating, updating, finding and deleting. But how to we get the results that are found? Not a problem. Let’s create a search querying and get row count. Count the rows in the result

Find Results – Row Iteration That query in the previous slide will find the rows that we had previously inputted. Use the same $result with the method PVDatabase::fetchArray() to return an array of found data to iterate through.

Challenge! Ok so we have the database basics down. So here is your challenge, it two parts Add connections to multiple database and switch between those connections Execute a select query on a database but use fetchFields() instead of fetchArray.

The Not So Obvious The function setConnection will close the connection to the current database and open a connection to a new one. All the database methods are designed to automatically work with the set database Other methods in ProdigyView, such as when creating content, will automatically sanitize data for you. PVDatabase::makeSafe can also sanitize arrays

Summary Add a connection by using PVDatabase::addConnection() Connection to a database using PVDatabase::setConnection() Sanitize data before inserting into the database by using PVDatabase::makeSafe() Execute a query using PVDatabase::query(); Fetch results by using PVDatabase::fetchArray();

API Reference For a better understanding of the database, visit the api by clicking on the link below. PVDatabase www.prodigyview.com More Tutorials For more tutorials, please visit: http://www.prodigyview.com/tutorials