Showing posts with label model. Show all posts
Showing posts with label model. Show all posts

Saturday, 8 July 2017

Create Login System with CodeIgniter MVC Model

Create Login System with CodeIgniter MVC Model


Hello, I am a Core PHP programmer and currently learning MVC with codeigniter and bootstrap. So, dont expect some expert level stuff. So Let Begin.

What the hell is MVC?


I am not a reader as I want to code quickly, Here is a diagram to help us understand

Image taken from betterexplained.com. To read more Click here

After understanding the image, it can be presumed that HTML stuff will go to View section. Controllers will be our PHP scripts and boring databases will be in model section. Currently I am using Apache server in WAMP. If I am using MVC, I dont want to use it on simple website. Why should I give myself an headache to adopt it on a simple website? Hence, I am going to use it to create a login system for my own web interface.The framework will create a login system to make my data secure and easily manage of code. I am developing this on version 3.0.2 and this tutorial may not work in later version changes. Here are Simple steps to follow:

  1. Download Codeigniter and unzip it in respective www folder. Thats it. I myself though it would include complex process of modifying PHP extensions, modules and stuff but unzipping the zip installs it. Nothing much complex.
  2. Now for login you need MySQL. Login in into it, create your database you like and create table below.
    CREATE TABLE `users` (
    `id` tinyint(4) NOT NULL AUTO_INCREMENT,
    `username` varchar(10) NOT NULL,
    `password` varchar(100) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
  3. Add a user.
    insert into users (username, password) values (gman, MD5(password));
  4. Link Codeigniter with MySQL database in application/config/database.php
    $db[default] = array(
    dsn => ,
    hostname => localhost,
    username => root,
    password => password,
    database => databasename,
  5. Change the route from welcome to login in application/config/routes.php
    $route[default_controller] = "login";
  6. the file application/config/autoload.php loads packages, libraries, helpers, languages and drivers by default. The framework is kept minimalistic. Therefore none of them are activated by default. Add the following libraries and helper required for login system.
    $autoload[libraries] = array(database,session);
    ...
    $autoload[helper] = array(url);
  7. Set the encryption key in application/config/config.php. I had no clue what to keep so I generated it from http://jeffreybarke.net/tools/codeigniter-encryption-key-generator/
    $config[encryption_key] = J6HHz5G5F02ngqX1phtMFDkYvYshgOtC;
  8. Configure .htaccess file
    <IfModule mod_rewrite.c>
    RewriteEngine On
    # !IMPORTANT! Set your RewriteBase here and dont forget trailing and leading
    # slashes.
    # If your page resides at
    # http://www.example.com/mypage/test1
    # then use
    # RewriteBase /mypage/test1/
    RewriteBase /
    RewriteCond %{REQUEST_FILENAME} !-f
    RewriteCond %{REQUEST_FILENAME} !-d
    RewriteRule $1 !^(index.php|img|images|css|js|fonts|fontrobots.txt)
    RewriteRule ^(.*)$ index.php?/$1 [L]
    </IfModule>

    <IfModule !mod_rewrite.c>
    # If we dont have mod_rewrite installed, all 404s
    # can be sent to index.php, and everything works as normal.
    # Submitted by: ElliotHaughin

    ErrorDocument 404 /index.php
    </IfModule>
  9. Enough Config stuff. Lets get straight to the point yeah! Lets create a User Model by creating User.php as application/models/
    <?php
    Class User extends CI_Model
    {
    function login($username, $password)
    {
    $this -> db -> select(id, username, password);
    $this -> db -> from(users);
    $this -> db -> where(username, $username);
    $this -> db -> where(password, MD5($password));
    $this -> db -> limit(1);

    $query = $this -> db -> get();

    if($query -> num_rows() == 1)
    {
    return $query->result();
    }
    else
    {
    return false;
    }
    }
    }
    ?>
  10. Create a controller for login (application/controllers/Login.php). This controller is just to take you to the login page and not related to login verification. That comes later.
    <?php if ( ! defined(BASEPATH)) exit(No direct script access allowed);

    class Login extends CI_Controller {

    function __construct()
    {
    parent::__construct();
    }

    function index()
    {
    $this->load->helper(array(form));
    $this->load->view(login_view);
    }

    }

    ?>
  11. The html stuff comes in application/views/login_view.php. Use your js from cdn, css in css folder in root and use base_url(); to link to them.
    <!DOCTYPE html>
    <html>
    <head>
    <title>Simple Login with CodeIgniter</title>
    </head>
    <body>
    <h1>Simple Login with CodeIgniter</h1>
    <?php echo validation_errors(); ?>
    <?php echo form_open(verifylogin); ?>
    <label for="username">Username:</label>
    <input type="text" size="20" id="username" name="username"/>
    <br/>
    <label for="password">Password:</label>
    <input type="password" size="20" id="password" name="password"/>
    <br/>
    <input type="submit" value="Login"/>
    </form>
    </body>
    </html>
  12. Now this is the code that I was unable to get my head around. This controller verifies the login. Add the below code in application/controllers/Verifylogin.php
    <?php if ( ! defined(BASEPATH)) exit(No direct script access allowed);

    class VerifyLogin extends CI_Controller {

    function __construct()
    {
    parent::__construct();
    $this->load->model(user,,TRUE);
    }

    function index()
    {
    //This method will have the credentials validation
    $this->load->library(form_validation);

    $this->form_validation->set_rules(username, Username, trim|required);
    $this->form_validation->set_rules(password, Password, trim|required|callback_check_database);

    if($this->form_validation->run() == FALSE)
    {
    //Field validation failed. User redirected to login page
    $this->load->view(login_view);
    }
    else
    {
    //Go to private area
    redirect(home, refresh);
    }

    }

    function check_database($password)
    {
    //Field validation succeeded. Validate against database
    $username = $this->input->post(username);

    //query the database
    $result = $this->user->login($username, $password);

    if($result)
    {
    $sess_array = array();
    foreach($result as $row)
    {
    $sess_array = array(
    id => $row->id,
    username => $row->username
    );
    $login_data = array( logged_in => $sess_array );
    $this->session->set_userdata($login_data);
    }
    return TRUE;
    }
    else
    {
    $this->form_validation->set_message(check_database, Invalid username or password);
    return false;
    }
    }
    }
    ?>
  13. Create Homepage controller in application/controllers/home.php
    <?php if ( ! defined(BASEPATH)) exit(No direct script access allowed);

    class Home extends CI_Controller {


    function index()
    {
    if($this->session->userdata(logged_in))
    {
    $session_data = $this->session->userdata(logged_in);
    $data[username] = $session_data[username];
    $this->load->view(home_view, $data);
    }
    else
    {

    //If no session, redirect to login page
    redirect(login, refresh);
    }
    }

    function logout()
    {
    $this->session->unset_userdata(logged_in);
    session_destroy();
    redirect(home, refresh);
    }

    }

    ?>
  14. Finally, add a view of home in application/views/home_view.php
    <!DOCTYPE html>
    <html>
    <head>
    <title>Simple Login with CodeIgniter - Private Area</title>
    </head>
    <body>
    <h1>Home</h1>
    <h2>Welcome <?php echo $username; ?>!</h2>
    <a href="home/logout">Logout</a>
    </body>
    </html>
Thats all! Hope this is helpful. Please comment if you face any error.

{ Read More }


Friday, 23 June 2017

Creating DDL scripts from Excel Model using Pentaho Data Integration

Creating DDL scripts from Excel Model using Pentaho Data Integration


What this post is about

In this post, I will walk through an ETL transformation that takes a data model defined in spreadsheets into a DDL script that can be executed to create tables on PostgreSQL. I am using Pentaho Data Integration Community Edition for this exercise.

Creating Data Models in Spreadsheets

While creating data models, it is usually easier to create a data model using spreadsheets. I typically use multiple sheets in the spreadsheet for each table, list the column names and data types as descriptions.

The structure I use is as follows, with one sample row of data

ID AttributeName DataType DataWidth Description Dimension SourceColumns ExtractionMethod
1 W_ATTRIB_1 Numeric 4




Here is a sample fact table shown in Excel



Here is a Dimension table (its the same structure except the name ends in "_D" instead of "_F" based on naming conventions.


The problem with Spreadsheets

The problem with spreadsheets is of course creating the final SQL script is not straightforward. For this reason, I am creating the transformation in an ETL tool, so that it can be maintained and used over and over again.

Lets walk through the high level steps for the transformation.

High level steps

The high level steps in the ETL transformation are as follows.

1. Read all the spreadsheet(s) sitting in one folder, and read all the sheets in the spreadsheet.
2. Filter the sheets to be processed.
3. Add constants
4. Concatenate Fields
5. Sorting the rows
6. Denormalizing the rows
7. Concatenating the final SQL
8. Outputting to a text file

Here is a picture of the transformation



Reading all the spreadsheets in the folder

The first step is to read all the spreadsheets in a folder, as well as all sheets in each spreadsheet file. Since this step is an important and big enough step, I have posted it as a separate post on my blog.

Filter the sheets to be processed by the transformation

Since I may have multiple sheets in each spreadsheet, and only some may be relevant in the model, I added a step to Filter Rows. Filter Rows is available under the Flow group of transformers on the Design tab.


Click on Edit Step to configure this step. As you can see, I have added two filters to only allow sheets in each file that either end with _D or _F.


Adding some constants

Next step was to add some constants that I need at multiple places in the transformation to fully form the SQL. The Add Constants step can be found under the "Transform" group of steps under the Design tab.


The constants I added were the following:


#Constant NameValue
1.TableColumnKey TabColKey
2.OpenParanthesis (
3.ClosedParanthesis)
4.CreateTable CREATE TABLE
5.Semicolon ;
6.Space




Click Ok to continue. Next step is to concatenate fields to form column definitions.

Concatenating Fields to form Column Definitions

I used the "Concat Fields" step under the Transform group of steps on the Design tab as shown below.



After dragging and connecting it to the previous step, right click and select Edit Step. Enter the following details on the dialog box

1. TargetFieldName: TableColumnDefinition
2. Separator: or blank

Here are the input columns I concatenated, in the following order.

1. AttributeName
2. Space
3. DataType
4. OpenParanthesis
5. DataWidth
6. ClosedParanthesis

 This will create a column called TableColumnDefinition concatenating all the above fields in listed order.


Next we need to prepare to flatten the rows. Prior to feeding the rows into the Denormalizer, we need to sort these.

Sorting the definitions by Table name

The Sort Rows step is also present under the Transform group of steps. Drag and connect it to the previous step.


Right click and Edit step to make the following entries. The fields need to be in the order listed below.

1. TableName
2. ID

This will sort the input rows accordingly. Click Ok to continue.



Denormalizing the rows.

This is the most important step in this entire transformation. Drag and connect the Row Denormalizer step present under Transform steps, to the previous steps.

Right-Click and select Edit Step to continue.



On this dialog box, we will enter the following details

1. The Key Field: TableColumnKey (This is the constant we introduced in previous steps and is same for all input rows)

2. GroupField: TableName (We want to select all rows having the same TableName to be merged into a single row)

3. Target Fields: (We will enter only one target field with the following values)

a. TargetFieldName: AllColumns (Each output row will now have this additional column)

b. ValueFieldName: TableColumnDefinition (This is the concatenated column we created in a previous step)

c. Length: 5000 (We need space for long table statements)

d. Aggregation: Concatenate strings separated by , (This will take individual column definitions for a given table name, and concatenate them with comma separations, creating a list of column definitions)

Click Ok to continue.



Constructing the Full SQL


The last step before writing to file is to construct the full SQL. We will concatenate the following with a space between them. Enter the following details

1. TargetFieldName: FullSQL
2. Length of Target Field: 5000
3. Separator: " " (Single Space)
4. Fields:
a. CreateTable
b. TableName
c. OpenParanthesis
d. AllColumns
e. ClosedParanthesis
f. SemiColon



Writing to file

Last step in this transformation is writing to file. Drag and connect a Text File Output step from the Output group of steps.


Enter the following

1. Name of file: Your choice
2. Extension: sql



On the Content tab, I made no changes.


Finally on the Fields tab, we only need one output column with following details

1. Name: FullSQL
2. Length: 5000


Thats it. Save the tranformation and execute.


{ Read More }


Monday, 19 June 2017

Create CListView of a model class in another models view Yii

Create CListView of a model class in another models view Yii


Suppose we have two models: Project and User, in which each Project owns a number of Users. We wish to display a list of associated users in the project instances view

Modify the protected/views/project/view.php by adding the following line in the script file:


<?php $this->renderPartial(_viewUser, array(project=>$model)); ?>

Next create a partial view protected/views/project/_viewUser.php with the following codes for its implementation:


<?php
$users=new CActiveDataProvider(User,
array(
criteria=>array(
condition=>audit_project_id=:projectId,
params=>array(:projectId=>$project->id),
),
),
array(
pagination=>array(
pageSize=>20,
),
)
);

$this->widget(zii.widgets.CListView, array(
dataProvider=>$users,
viewData=>array(project=>$project),
itemView=>/user/_view,
));
?>

{ Read More }