Database Tables And Columns

Overview Objectives Learn the basics of creating, updating and removing tables and columns from a database. Requirements A database connection A basic understanding of the types of fields in a database Estimated Time 8 minutes

What about tables and columns? If you are using this Framework, the possibility is very high that you are using some sort of database. With that database you will need to create, update and remove table and columns. By the end of this tutorial, hopefully you have the basics of creating and table columns. And because ProdigyView is database agnostic, there will work for any database that ProdigyView supports.

Defining Columns Before we make a table, we have to define columns. Columns are be defined in the format of an array where the key is the column name and the value is an array of attributes that define that column. The attributes that can be used to define a column are as follows: Auto_increment: A boolean that defines this column as a value that will be auto incrementing Unique: A boolean that defines if this column is unique. Type: The attribute with the most options, the type defines what kind of column is being created. Text, varchar, timestamp, boolean, etc. Precision: How precise the column is. So for example if you only want a varchar to be 20 characters, this would be set to 20. Default: The default value for the column. Hopefully that gives you an idea of where we headed with this example. Lets look at a code sample next.

Defined Columns Example Auto incrementing defined Set a type Precision is being defined Set a default value Store in array

Creating a Table Ok so we have our columns defined. Lets move onto creating a table with the columns. In our example below, we set the primary keys to be the ‘id ‘handle ‘handle’ column and we are passing in the table name and the columns from previous slide. 1. Checks if the table exist 2. Set table name 3. Set columns 4. Set primary Keys

Adding More Columns Let’s say we forgot columns because we were distracted by celebrity marriage that only lasted some 70 odd days and need to add some more columns. Not problem. You simply have to know the name of the table you are appending the column too, the name of the column you want to append, and the attributes that define that column. 1. Define columns 2. Iterate through columns 3. Add the columns individually

Removing A Column Oh wait, another mistake. Now you need to remove a column. Not a problem either. Simply pass in the table name and column name to dropColumn and the column with be removed. 2. The name of the table 3. The name of the column 1. Check if the column exist

Dropping A Table So the whole idea behind your table was a flop. Let’s get rid of the whole thing. Drop the table 1. Check if table exist 2. Drop/Delete the table

Challenge! Below is an optional challenge to help you better understand tables and columns in ProdigyView. Create an table that has columns for an auto-incrementing id, email address and column that holds the date registered. Make the email column unique. Insert an email address into the that column. Then try to enter the same email again. Add a Boolean column for if the user is active, set to false. *Add a password column that has a default value that generates a random string.

The Note So Obvious Checking if a table exist can be checked with PVDatabase::tableExist() method. Checing if a column exist can be checked with PVDatabase::columnExist() method. You can clear/truncate a table with PVDatabase::clearTableData() method.

Review Create a table using the PVDatabase::createTable() method. Add a column using the PVDatabase::addColumn method. Remove a column using PVDatabase::removeColumn() method. Drop a table using the PVDatabase::dropTable() method.

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