In my last article (in CODE Magazine's May/June 2013 issue ), I discussed the basics of writing an application using PHP and a framework called CodeIgniter. That application was rather simple and limited in scope. It consisted of only one page, no menu, and no integrated security.

Real applications, however, usually need some form of integrated security as well as menus, data entry screens, and more.

In this article, I'll delve further into CodeIgniter and show how easily it can be used to build large-scale applications with the features that any good application should have.

In my last article, I didn't really look at the MySQL database that powers the data behind the app. In truth, your database is just as important to your application as the PHP is. The more you know about how to store and manipulate data within the database, the more flexible your application can be.

Your database is just as important to your application as the PHP is.

MySQL and PHP were truly meant to partner together to create world-class Web applications. In this article, I'll take a closer look at MySQL and some of the great power it brings to working with data in your application.

All Politics are Local

“All politics are local.” This famous phrase is credited to the famed late Speaker of the House, Tip O'Neill. At its most basic, it means that, in the end, the essence of any political victory happens at the local level. People vote based on what they feel is best for them and, therefore, you have to:

  • Convince the people that you (or your candidate) are the right person.
  • Get them registered (if they're not).
  • Get them to the polls.

For a campaign that wants to make use of voter registration data, which is public information and available for a $5 fee from the county board of elections, the campaign either has to contract with a consulting or software company that will intelligently use the data for voter registration and other efforts (which costs an arm and a leg) or they have to build their own.

This is the story of a “build your own” solution that, while currently still being enhanced and expanded, is a perfect vehicle for delving deeper into the rich functionality that you have at your finger tips with PHP, CodeIgniter, and MySQL.

The Facts on the Ground

It's municipal election time in New Jersey. There is a small community in a New Jersey municipality that has been trying to identify those members of the community who are not registered to vote so that they can be contacted and asked to register. Each time an election comes about, a few members of the community get hold of the voter registration and try various methods, both electronic and manual, to come up with a list of people to contact and register.

With an all-volunteer group, time was limited. The more time spent on manually comparing lists, the less time there was to get people to register. So I wrote a simple application that we could enhance over time. The application was to:

  • Compare the community list to the voter registration list and output a list of people on the community list that did not match up.
  • Download a raw copy of the voter registration list in an Excel spreadsheet.
  • Be accessible via user name and password.

The first item provides an idea of who to call and the second makes it possible to do some manual analysis.

The Challenges

In looking at the work that was required, a few challenges became apparent quickly.

Linking the Data

The system works with two disparate data sets:

  • The voter registration list from the county
  • A list of community members

The challenge in working with these sets of data is that there is no objective way to match them up. Under normal circumstances, when relating two tables, you would create some kind of unique identifier that could be used to provide an objective link between the two data tables. In this case, since the origins of the data are distinct, I had to be a little creative. I was aided in this task by two facts.

  • The geographic area under discussion is fairly small and mostly suburban residential.
  • Street names do not repeat within the city limits.

This allowed me to make a simple assumption: I'd match the people by using their last names, house numbers, and street names. Although this wasn't perfect, it provided a data set that I could work with.

Disparate Data Formats

The data formats are different in the two files. For example, the voter registration list used standard street-type designators such as ST. and AVE. and the community list didn't. House numbers weren't consistent either.

In order to deal with this, I had to write a program that standardized the address elements.

Data Refresh

The lists are maintained by third parties. As a result, I had to be able to import the raw data periodically to refresh the data sets for corrections, additions, and deletions.

The Tables

The two data sets each have a table. The structure of these tables is dictated by the structure of the data being imported as well as the additional columns needed for cleaning up the data.

Beyond those two tables, there are two additional tables. One holds the user information and another is a metadata table to hold the system sessions. I'll get into the system sessions later on.

The structure of three of the four files was created by someone other than me so I could not standardize the names. I created the users table, so I was able to name the columns whatever I liked.

I used my old favorite Hungarian Notation and used other naming conventions to identify what I intended when I created the object.

There are two helpful hints here:

  • Use naming conventions where you can. They'll save you some heartache when trying to program and debug your routines.
  • The book of rules should never be so carved in stone that it gets in the way of getting the job done. I don't mean that you should violate rules willy-nilly; if you do that, you render the rules useless. But there will be times when you need to violate a rule because there is a higher purpose. When you do this, document it in your code

The Voterfile Table

The Voterfile table has the county's voter registration database. The file comes in as a pipe-delimited CSV file. Table 1 shows the MySQL structure.

Table 2 shows the table indexes.

The Communityfile Table

This file is a telephone-book-type listing of members of the community. Like the Voterfile, this file also comes in as a CSV file. Table 3 shows the columns and Table 4 has the indexes for Communityfile.

The Users Table

This table holds the authorized user information. Unlike the voterfile and communityfile tables, this one is maintained within the system. Later on in this article, I'll show you how to create and update mechanisms. In the meantime, Table 4 and Table 5 show the columns and indexes for the Users table.

The CI_Sessions Table

The structure of this table is dictated by CodeIgniter. It's used to hold session data. Tables 6 - 7 hold column structure and the list of indexes, respectively.

Populating the Data

The data for both CommunityFile and VoterFile came in text format. CommunityFile is in CSV format and VoterFile is in pipe delimited format. The tables were created to mimic the column layouts of their respective text files. When the time came to populate the tables, I did two things:

  • I used the LOAD DATA command to read in the text files and then I did some work to make sure that the columns I used to link the tables (name, street name, etc.) contain homogenous data.
  • I wrote a SQL procedure for that.

Now it's time to get into the application.

Components of an Application

I know it might seem silly to go over the components of an application but it's useful for this discussion, so please bear with me.

The possible features of any application are as infinite as the permutations of problems they are designed to solve. Still, it is possible to generalize application features into three groups:

  • Security
  • Input
  • Output

Security

There are a few sides to the issue of security. Most visible is access security, typically a log in screen. Internally, however, there are other aspects, such as storage of passwords and, specific to a Web application, how to pass information between portions of the application.

Security in a standard desktop application is usually a fairly simple matter. You build it into the executable that is always running. The application has only one means of entry so you only have one entrance to secure. Once a user has authenticated access to the application, that's the ballgame.

It's different with a Web application. Web applications have many entry ways. Every page that can be accessed via a URL is a potential entry into the application and has to be secured. One way to do this would be to have the user enter a user name and password on every page. Of course, no one would use it. A better solution is to somehow store that the user has authenticated into the system and then access that stored information whenever you need to check access security.

Sessions

In a Web application, where you cannot set global variables specific to a user's session, you need a way to pass information from page to page. The way to do this is using sessions.

A session is a pool of data that is maintained and passed through from page to page. The information is usually stored in a client cookie or passed around in URL strings.

CodeIgniter adds a third way that is the most secure choice: Session information can be stored in the database.

Using sessions, you can check the necessary authentication information at every entry point into the application once it's stored. In addition, you can store other information that you need to pass from one portion of the application to another, such as a user security level or even the last page visited.

If the authentication information exists when the user navigates around, the application allows access; otherwise they're redirected to the login controller. If you want to record the controller they were trying to access before you redirected them to the login controller, you can store that location in the session too.

The CI_Session table holds all session data. In order for it to work, first you have to set up the sessions in CodeIgniter. You do that in the config.php file.

The first value that the config.php file needs is an encryption key. You don't have to enable encryption in CodeIgniter to work with sessions, but you do have to give config.php an encryption key. The encryption key can be anything you want. I like a long randomly generated key. You can do randomly generate the key this simply enough in three ways:

  • You can generate a GUID or UUID.
  • You can type a key in yourself.
  • You can use a key generating site.

I used the last method and went to RandomKeyGen.com. Then I took a CodeIgniter encryption key value and pasted it into my config.php as follows:

// from RandomKeyGen.com
$config['encryption_key'] = 'E5B471jcLw59wj6e2Ig16y4k3rERpvg9';

The next section of config variables are the key settings to work with for customizing the sessions.

$config['sess_cookie_name']     = 'ci_session';
$config['sess_expiration']      = 600;
$config['sess_expire_on_close']	= true;
$config['sess_encrypt_cookie']	= true;
$config['sess_use_database']	= TRUE;
$config['sess_table_name']      = 'ci_sessions';
$config['sess_match_ip']        = FALSE;
$config['sess_match_useragent']	= TRUE;
$config['sess_time_to_update']	= 300;

Most of these values are the default, except sess_use_database and sess_expiration.

By default, sess_use_database is false. Setting the value to true tells CodeIgniter to store the session information in the database, which is more secure.

To tell the cookie to expire in a stated number of seconds, set sess_expiration.

There's an additional set of configuration settings that deal with cookie storage of session information that's inapplicable here because I'm storing session information in the database.

Because I'll need to update the session class consistently, it makes sense to make sure it autoloads. To accomplish this, I modified the autoload.php configuration file.

$autoload['libraries'] = array('database',
'session', 'encrypt');

This autoloads the session, database, and encryption libraries. I will need the database library to access the database. As for the encrypt library, I will leave the discussion of that for another time.

Storing Passwords

There are two separate yet related methods for storing password information. One way is to encrypt them. Another is to hash them. Both encrypting and hashing information turn a normal string into gobbledygook. The principle difference between them, other than the technogeekish details, is that the encrypted information can be decrypted, whereas hashed information cannot be unhashed. On the Web, hashing is usually used.

There are a variety of hashing algorithms out there. PHP supports several dozen of them. The most popular hashing algorithms are MD5 and SHA. MD5 is not as strong as SHA and I don't recommend it. I recommend versions from the SHA2 family that have two functions: SHA-256 and SHA-512.

A one-way hash makes it a little more difficult to hack a password but not impossible. It might not be easy, but it could be done with sufficient resources.

A good way to increase your security is to add a salt to your passwords. A salt is a string of characters that are added to a string prior to hashing it. You can add the salt to the string at the beginning, end, or some point in the middle of the string.

A salt is a string of characters that are added to a string prior to hashing it.

There are many theories about how to effectively salt passwords. The most secure way is to use a different salt for each password. When you save a password, generate a salt using any one of a number of methods. A simple method is to salt with a newly generated UUID (you could theoretically salt with the ID on the user record and kill two birds with one stone). You add the salt to the user password, hash it, and then store it. If you are using a different salt with every user, you need to store the salt in the database too.

Another method, albeit somewhat less secure, is to reuse the salt. The principle objection to this method is that it results in two users who use the same password having the same hash. Still, if you are using a long enough salt and it's sufficiently random, many applications can use this method safely enough.

I opted for the second approach because it also gives me the opportunity to show off another CodeIgniter feature: setting your own configuration items. It is a very low risk system without any sensitive information. Almost all of the information is public.

Because I plan to reuse the salt, I created a configuration item and used it when needed. When adding a configuration item, I could add it to the config.php file or I could create my own configuration php file. Each approach has its benefits. If I add the item to config.php, I can be assured that the configuration item will load along with all the rest. On the other hand, adding it to config.php mixes custom items in with the standard items. This can create a confusing mess and also makes it difficult to reuse them if I want to use them in another application.

Creating a custom config file has the benefit of creating a single place for all of the custom settings but they won't load automatically; it needs a little additional work.

I prefer the second approach and I created my own custom config file called custom.php. The file is short in this application.

<?php

$config['salt'] = 'bVb5Xs5usogrPTjGAmu51qJPkeK2k24F';

The salt I chose is a random key from RandomKeyGen.com.

CodeIgniter does not load custom.php automatically. Any configuration file can be manually loaded in a controller as follows:

$this->config->load('filename');

If you have a configuration file you always want to load, add it to the autoload.php configuration file:

$autoload['config'] = array('custom');

One concern when you create custom configuration items is that the configurations are stored in an array. Because of this, it's possible to accidentally overwrite an existing configuration setting. When you load a custom configuration file manually, you can protect it by adding a parameter of TRUE on the load call, an option that is unavailable when autoloading. I didn't find anything about this in the documentation.

it's a good idea not to accidentally overwrite another setting. For instance, I checked to see if there was a setting called “salt” before I used it. If you create many custom settings, it might be a good idea to come up with a unique prefix or suffix for your configuration item names.

Comparing Passwords

Comparing passwords is slightly trickier when storing them in hashed format. Because you cannot unhash the passwords, the only way to compare a password entered at login to the stored password in the database is to salt and hash the entered password and then compare it to the stored value.

Establishing the First User

There's one last piece of set up that's necessary before using the system. I set up a Users table, worked out how to check authentication after login with sessions, and then mapped out the scheme for storing the passwords securely. But how do I get that first user into the system?

There are three options on how to do this. The first option is to insert a row directly into the MySQL. The second option is to temporarily remove the login restrictions on the Maintain User page (which I will discuss later) so that a new user may be entered. The third option is to write a special routine in PHP to put a row into the users table.

I chose the third option.

To insert a row into MySQL, I want to put in the salt and password and then hash it in the INSERT statement. SHA-2 was added to MySQL in version 5.5.5 and I'm not using the latest version of MySQL. And, before you say that I should just upgrade, my ISP isn't equal to 5.5.5 either. I can't rely on the functionality being available within MySQL.

This leaves me with a PHP-based solution.

If I temporarily allow adding a user with the Maintain Users screen, it could work for a custom application but it's not viable for an application that someone else will install.

That leaves the final option.

Most applications ask for an admin password on install and add the admin user during the install process. I didn't need to be quite that fancy here.

The Controller

The controller that generates the admin record in the user file is called setupadmin and is a part of users.php (see Listing 1) that holds all the controller functions related to dealing with users, including maintenance and login.

The main routine to look at is setupadmin but I need to cover a few things before I get into it.

It's important to look at the class definition for users.php:

class Users extends MY_Controller {

MY_Controller is not the normal base class for CodeIgniter controller (it's normally CI_Controller). There are a few methods I want to have for my controllers, so I subclassed CI_Controller into MY_Controller first.

Subclassing CodeIgniter Classes

You can subclass the CodeIgniter classes by writing the subclass and placing the PHP file in the application/core folder. Subclassed CodeIgniter classes are given a prefix that is defined in config.php:

$config['subclass_prefix'] = 'MY_';

MY_ is the default, and I didn't see any reason to change it. You could if you like. If you need to have additional levels of subclasses (i.e., a subclass of your MY_ class), you can put it in the same file as the initial subclass.

MY_Controller.php

The code listing for MY_Controller.php can be found in Listing 2. As you can see, the controller has six added functions.

Before I go through the functions individually, note that each function name begins with an underscore. This tells CodeIgniter that this method is not usable on the URL line and is only used within the class and subclasses.

The _isloggedin() Function

The first of the added methods checks to see if a user is logged in. It checks for a specific value in the session that only a logged in user would have.

function _isloggedin(){
    $userdata = $this->session->userdata('id');
    if (!isset($userdata) or !$userdata){
        $loggedin = false;
    }else{
        $loggedin = true;
    }
    return $loggedin;
}

Note the call to $this->session->userdata(). First, the session class is never explicitly loaded because it was autoloaded. Second, the userdata() method retrieves data from the session. If the data element doesn't exist, the function returns false.

This function checks to see if there is an element in the session called ID. If the element exists, the user is logged in. If not, the user is not yet logged in.

The _isadmin() Function

Sometimes, it's not enough to know if a user is logged in or not. Sometimes I need to know if a user is an admin user or not. That's the job of this method.

function _iadmin(){
    if(!$this->_isloggedin()){
        $ladmin = false;
    }else{
        $ladmin = ($this->session->userdata('ladmin')==1);
    }
    return $ladmin;
}

When a user logs in, the contents of their user record is read into the session. The ladmin field is used to determine whether the user is an admin or not and is queried in this function to get a proper return value.

The _uriencode() and _uridecode() Functions

As you will see later on in the code, there are times when it is necessary to pass information on the URL that you don't want a user to see. For example, if you are sending through the ID of a row, you can keep that information to yourself. In those cases, encrypt the URI segment that has that information.

Here's the code for those two functions:

function _uriencode($tcURI){
    $encoded = base64_encode($this->encrypt->encode($tcURI));
    return $encoded;
}

function _uridecode($tcEncoded){
    $decoded = $this->encrypt->decode(base64_decode($tcEncoded));
    return $decoded;
}

You may recall entering an encryption key in config.php. It was intended, at the very least, for sessions. If you want to use encryption in CodeIgniter as I do here, you have to go a little further in your config file.

In config.php, there is a setting called permitted_uri_characters. Note what characters are allowed by default:

'a-z 0-9~%.:_\-';

The slash in the string of allowed characters is a problem. If you have a / in a URI, it is interpreted as the end of the segment. You need to remove that slash from the list of allowed characters so that your encrypted URI segments work properly. Here's what the modified config line looks like:

$config['permitted_uri_chars'] = '+=\a-z 0-9~%.:_-';

The _newid() Function

The next function, which can be seen in Listing 2, is _newid(). I needed a function that generated a full UUID in PHP. I considered writing a function that ran a query to the MySQL server to return SELECT UUID() but decided to try to find a PHP native solution to avoid a round trip to the server. I searched for it and found what I was looking for in http://stackoverflow.com/questions/4049455/how-to-create-a-uuid-in-php-without-a-external-library. I copied the code and turned it into a method of the controller subclass.

The _checkBoxPostValue() Function

This function came about because of an oddity in how HTML passes values on a submitted form. I expected that a checked checkbox would return whatever the stated value was and an unchecked checkbox would return an empty string (or a “0”). I was wrong. If the checkbox is not checked, the value is never passed through to the $_POST array.

That was somewhat disconcerting. After all, who wants to constantly check to see if a checkbox is in the $_POST array before addressing the indexed value? It seemed a natural for a method in the subclass and here it is:

function _checkBoxPostValue($index){
    if (!isset($_POST[$index])){
        $retval = 0;
    }else{
        $retval = $_POST[$index];
    }
    return $retval;
}

The method is passed to the index of the checkbox and checks to see if the index is in the $_POST array. If it is, the indexed value is returned, otherwise a literal 0 is returned.

Autoloading a Model

There's one last piece to cover before I can go into setupadmin(). If you look at the code to setupadmin that is part of Listing 1, you will notice that the first bit of code immediately calls users_model (Listing 3) without loading it.

You can autoload models by modifying the autoload.php configuration file but I don't do that for models that are specific to a particular set of controllers. Users_model is only relevant within the users controller so I only want to load it when I am using controllers within users.php. Fortunately, there is another way to autoload the model when running within users.php: put the load code in the constructor. Since the constructor always runs when the class is instantiated, putting a standard $this->load->model call in the constructor covers any method in the class.

Here's the code:

function __construct(){
    parent::__construct();
    $this->load->model("users_model");
}

Setupadmin()

Now that I have discussed the parent class, what I added there, and how to autoload a model, I can go through the code for setupadmin(), which is a controller method within users.php (Listing 1).

The first step is to see if you have an admin record already setup.

$adminuser = $this->users_model-> 
        getUserRecord('admin@voters.local', 'email');

if (isset($adminuser[0]['id'])){
    return;
}

The getUserRecord is a method within users_model.php (Listing 3). Pass an identifier (which can be the ID or the email address), and an indicator as to which type of identifier you are sending, to the method and it returns a user record if it finds one. The function is also used when validating user login so it returns all the fields used during the application for one purpose or another.

function getUserRecord($iduser, $idtype="id"){
    $this->db->select('id, cemail, cname, ' .
        'ladmin, cpassword, ldisabled, ' . 'tlastlogin');
    $this->db->from('users');

The where clause depends on what kind of ID was passed to the function. It can check against either the ID field, which is a UUID, or the email address.

if ($idtype=="id"){
    $this->db->where('id', $iduser);
}else{
    $this->db->where('cemail', $iduser);
    }
    $query = $this->db->get();
    $array_result = $query->result_array();
    return $array_result;
}

Getting back to setupadmin(), if the admin record is not found in the users table, the next bit of code adds it so I can log in.

In order to store the password, I have to retrieve the salt value from the config array. I can accomplish this using the config class:

$salt = $this->config->item('salt');

After that, I fill an array with the values I want in the user record.

$userrec['id'] = $this->_newid();
$userrec['cname'] = 'Menachem Bazian, CPA (NY)';
$userrec['cemail'] = 'admin@voters.local';
$userrec['cpassword'] = hash("SHA256",$salt.'admin');
$userrec['ladmin'] = 1;
$userrec['ldisabled'] = 0;
$userrec['tlastlogin'] = '0000-00-00';

Finally, I can insert it into the database.

$this->db->insert('users', $userrec);

Notice how simple it is to add a row to a table using CodeIgniter's ActiveRecord class.

Once the record is in the table, I can go right to the login page and log in.

redirect('/users');

Note that I am not referring to any particular method within users.php on the URL. When no method is specified, CodeIgniter calls the index method.

The Login Page

Now that I have everything set up, I can log in to the system. You can see the login page in Figure 1.

![Figure 1: The Login page is basic. ](https://codemag.com/Article/Image/1312091/Figure 1.png)

The user enters an email address and a password. Clicking on Login asks the system to verify the entered credentials and let the user into the system. If the information entered is wrong, the login screen redisplays with an error message: “Invalid user name or password,” as shown in Figure 2.

![Figure 2: The Login Page shows an error if incorrect information is provided.](https://codemag.com/Article/Image/1312091/Figure 2.png)

If the credentials are accepted, the application loads as shown in Figure 3.

![Figure 3: Once the credentials are accepted, the Application page displays.](https://codemag.com/Article/Image/1312091/Figure 3.png)

The Login Controller

The controller for the login page is the login method of users.php (Listing 1). The method first checks to see if the user is already logged in. If the user is not logged in, the login page has to be shown

public function login(){
    if (!$this->_isloggedin()){

The first question to ask once the controller knows that a login is required is whether or not there was a previous attempt to login that was unsuccessful. When there is a failed login attempt, the failure is recorded in the session. The first thing to do is check to see if there is a login error.

$loginerror=$this->session->userdata('loginerror');

The next line of code sets a flag so you can know if you are in the middle of a login attempt. This will come in handy later on.

$this->session->set_userdata("inlogin", "true");

Then, load up the loginerror variable into a data array and pass it off to the view.

$data['loginerror'] = $loginerror;
$this->load->view('modules/login_view', $data);

If the user is already logged in, the method redirects the user to the voter base controller (i.e., the index method of voters.php) which displays the main application page.

}else{
    redirect('/voters');
}
}

The Login View

The login view, which is in login_view.php, can be seen in Listing 4. It is technologically unremarkable; it generates the login page as displayed. There are a few things to note:

  • The view calls three additional views: header, appheader and appfooter.
  • Header.php generates the HTML header information. It can be passed a Title variable and an array of additional lines (usually <script> or <link> tags but it could be anything) that are inserted right before the end of the head section.
  • Appheader.php is the beginning of the body section that includes loading the menu (which, in itself, is its own view file - appmenu.php).
  • Appfooter has the page footer code.
  • These view files are standard throughout the application and allow a standard look and feel throughout the application.
  • For a further discussion of this method, check out my May/June 2013 article on CodeIgniter in CODE Magazine.

There are four view files that are standard in the application:

  • Header.php (Listing 5)
  • Appheader.php (Listing 6)
  • Appfooter.php (Listing 7)
  • Appmenu.php (Listing 8)

As for the CSS files, you can download those from various sources. Use whatever template you like on your system; I chose one from freecsstemplates.org.

The only other notable point from login_view.php is that the form action calls /users/verify to complete the login process.

The Verify Method

When the user clicks login, the user name and password they entered is sent to the verify method via the $_POST array. Verify takes those values and compares them to what's in the users table. If there's a match on email address and the password, and the user is not disabled, the user is authenticated and allowed into the application.

The password cannot be directly compared to the users table; it first has to be salted and then hashed before a comparison may be made.

The first task is to make sure that the user entered information in the user screen. You can check that by making sure there is data in the $_POST array for the email address. I also check to make sure that the “inlogin” flag has been set in the session. I did this in the login() controller as you may recall. I'm trying to prevent someone from writing their own version of the login page that they automatically submit to /users/verify to try to get through the security system. By creating that session flag, I can prevent that because, unless the hacker had access to my code, they can't know about that flag.

I get another benefit from using database storage for the sessions; if I were to use cookies, they could be inspected and the flag detected. This way, if the test fails, the user is sent right back to the login page.

public function verify(){
    if(!isset($_POST["cemail"]) and !$this->session->userdata("inlogin")){

redirect('/users/login');

The next step is to gather the information needed to do a comparison. That consists of the form data sent through, the password salt, and a hash() routine to prepare the password for comparison.

}else{
    $cemail = $_POST["cemail"];
    $salt = $this->config->item('salt');

    $cpassword = hash("SHA256", $salt.$_POST['cpassword']);

The next step is to retrieve the user record based on the email address entered.

$userdata = $this->users_model-> getUserRecord($cemail, "email");

Check to see if a row is returned and, if so, whether the password matches. If a row is not returned or the password doesn't match, record that a loginerror occurred and redirect back to the login page.

if (count($userdata)==0 or ($userdata[0]["cpassword"] != $cpassword)){

    $this->session->set_userdata('loginerror', true);
    redirect('/');

If the data matches, copy the user record into the session information. Note that the set_userdata() method, which is used to set information in the session, accepts an array in which case the array is exploded with each index becoming a named item in the session with the corresponding value. Finally, redirect to the voters controller.

}else{
    //logged in
    $this->session->unset_userdata("inlogin");
    $this->session->set_userdata($userdata[0]);
    redirect('/voters');
    }
}
}

User Maintenance

User maintenance is accomplished using the user menu item. Clicking on Users brings up the list of users, as can be seen in Figure 4.

![Figure 4: This is the Users List](https://codemag.com/Article/Image/1312091/Figure 4.png)

If you look at appmenu.php (Listing 8) you might notice that this user menu is only generated when the logged in user has administrative rights.

The page shows a list of all users in the users table. The controller for this, the index method of users.php, gets a list of the users and sends the list off to a view for display.

The first thing the controller does is ask two basic questions:

  • Is the user logged in?
  • Is the user an admin?

Remember that anyone can type in voters.local/users. But you can't assume that a user running this controller has authorized access. You have to check each and every time.

public function index(){
    if (!$this->_isloggedin()){
        redirect('/users/login');
    }elseif (!$this->_iadmin()){
        redirect('/voters');

Assuming that the user is logged in and has administrative capabilities, the controller queries for the data using users_model (Listing 3), which is loaded in the class constructor, and sends it off to the view, users_list_view.php.

}else{
$userlist =
$this->users_model->Get_user_list();

$data['userlist'] = $userlist;
$lcView = 'modules/users_list_view';
$this->load->view($lcView, $data);
}
}

The code for the view, users_list_view, can be found in Listing 9.

There are a few things to note in users_list_view. I will only go through the code I want to highlight here; you can see the rest of the code in Listing 9.

$moreheader[0] =
'<script type="text/javascript">';
$moreheader[1] =
'	$(document).ready(function()';
$moreheader[2] = '		{';
$moreheader[3] =
$("#userlist").tablesorter();';
$moreheader[4] = '		}';
$moreheader[5] = '	);';
$moreheader[6] = '</script>';

$data['moreheader'] = $moreheader;

I said earlier that header.php (Listing 5) allows you to pass through additional header code lines as a data array. This is a perfect example. The code I'm passing through is a jQuery script for the user list table to turn it into a tablesorter table. There is formatting built into the lines in $moreheader.

I like my code neat and properly indented when possible. When I send code through on $moreheader, I prefer to keep it formatted as if I entered it into the HTML myself. This makes debugging the resulting HTML page much easier.

The next thing I would like to point out deals with the contents of the users list. The name column is a hyperlink that opens up the edit page for that user. Take a look at this code:

<?php
$lnCounter = 0;
foreach($userlist as $row):
$url = '/users/edit/'.
$ci->_uriencode($row['id']);
$lnCounter = $lnCounter + 1;
$admin = ($row['ladmin']==1 ?
"Yes" : "No");
$disabled = ($row['ldisabled']==1 ?
"Yes" : "No");

In order to tell the edit controller which user I want to edit, I pass through the UUID of the row on the URL as a URI segment. I prefer to keep that information encrypted as an added security measure. The _uriencode method of MY_Controller does precisely that.

By the way, here's an example of an ID URI segment after encryption with _uriencode:

VTM2azRacklEcEF3eVRWNXl4SkU2Tnlkc2haSis5TUJiQ3BMd
WtmQjkwcW9HZWluYU93bFJyZmt4cnFhb0twSjhhamJNNEw2TV
BFOHhzYkliQkE4bWdBdWI2Vi85blFyMFhQQi9DMHFScEFGUFN
1ZW5CRHgrZzJhVnJrR29XTGQ=

In case you're interested, the 36 byte URI segment worked out to 176 characters.

Check out the assignment of value to $admin and $disabled. The syntax you see in these two lines are, in effect, a single line if construct. It is the equivalent to the C# ?: conditional operator and the Visual Basic IIF() function.

The first part of the statement is the expression to test. The parameter after the question mark is the value to return if the expression is true, and the last parameter (that follows the colon) is the value to return when the expression is false.

Adding a User

To add a user to the table, the user has to click on the “Add User” link. This activates the add() method of users.php.

public function add(){
if (!$this->_isloggedin()){
redirect('/users/login');
}elseif (!$this->_iadmin()){
redirect('/voters');
}else{
$this->session->
set_userdata('usereditmode', 'Add');

$lcView = 'modules/users_edit_view';
$this->load->view($lcView);
}
}

Once again, note that the controller checks to make sure that the user is logged in and that they have admin rights. After that, the controller sets a session variable indicating that a row is being added and the view, users_edit_view (Listing 10), is called. Look at Figure 5 to see this in action.

![Figure 5: This is the Users Edit view.](https://codemag.com/Article/Image/1312091/Figure 5.png)

The view is used both for add and edit mode.

if (isset($userdata)){
$lcName = $userdata[0]['cname'];
$lcEmail = $userdata[0]['cemail'];
$lcPassword = "";

The code first checks to see if it received a record of information. When a user is edited, the user record is retrieved and passed through. If a record was passed through, you can pick up those values and put them in variables that plug into the HTML value properties.

if ($userdata[0]['ladmin']==1){
$lcAdmin = "checked";
}else{
$lcAdmin = "";
}
if ($userdata[0]['ldisabled']==1){
$lcDisabled = "checked";
}else{
$lcDisabled = "";
}

Checkboxes are a little different. They are stored in the database as a 1 or 0 depending on their state. HTML uses the checked keyword to determine if the box is initially checked or not. The code checks the value of those fields and sets the variables appropriately. Later on, in the HTML, you'll plug the values into the checkbox HTML definitions.

}else{
$lcName = "";
$lcEmail = "";
$lcPassword = "";
$lcAdmin = "";
$lcDisabled = "";
}

If there is no record passed through, the view knows that a row is being added so the fields are initialized to blank values.

The rest of the view is unremarkable. The form action for the view is /users/save.

Saving a User Record

When the user hits the save button, the Save routine is called.

public function save(){
//Intended to be used with a POST save
if (!$this->_isloggedin()){
redirect('/users/login');
}elseif (!$this->_iadmin()){
redirect('/voters');

Once again, the first code snippet of the function checks to ensure that the user is both logged in and an administrator.

}else{
$editmode = $this->session->
userdata('usereditmode');
$salt = $this->config->item('salt');

This next bit of code retrieves the edit mode setting from the session and the salt from the configuration array.

if(!isset($_POST['cpassword'])
or !$editmode){
exit('Invalid form submit');
}

This snippet checks that the data came from within the application via a proper form submit. If the data came from somebody trying to submit data with a bot or other method (other than using the user edit form), editmode is not set and the userdata() method returns a Boolean false.

if ($editmode == "Add"){
$userrec['id'] = $this->_newid();

If the user is in Add mode, the ID row is initialized to a new UUID with a call to _newid().

}else{
$userrec['id'] =
$this->session->userdata('iduser');
}

Otherwise, the ID can be pulled from the session. The edit() method puts the ID of the user being edited into a named item called iduser. I did it this way because I didn't want to send the ID across in a hidden field in the HTML. This way is more secure.

$userrec['cname'] = $_POST['cname'];
$userrec['cemail'] = $_POST['cemail'];

$userrec['cpassword'] =
hash("SHA256",
$salt.$_POST['cpassword']);

Next, retrieve the user name, email, and password. Note that the password is automatically salted and hashed in this line of code.

$userrec['ladmin'] =
$this->_checkBoxPostValue('ladmin');
$userrec['ldisabled'] =
$this->_checkBoxPostValue('ldisabled');
$userrec['tlastlogin'] = '0000-00-00';

Checkboxes are a different animal. Checkboxes are not included in the $_POST array if they aren't checked. Verify the value of the checkbox using the _checkBoxPostValue method you created in MY_Controller.

if($editmode=="Add"){
$this->db->
insert('users', $userrec);
}else{
$this->db->
where('id', $userrec['id']);
$this->db->
update('users', $userrec);
}

If the user adds a record, an insert is called; otherwise an update is called.


$this->session->
unset_userdata('usereditmode');
redirect('/users');
}
}

Finally, the usereditmode session value is removed and the application returns the user to the users list page.

Voters

Like users, the voters table starts out with a sortable list, except that this list shows people in the community list that aren't registered to vote. The match cannot be a direct match because the voter list is individuals whereas communityfile lists families. I was able to work a match by comparing the last names, house number, and street name from communityfile to voterfile. It's not perfect but it produced the information I needed.

All the controllers for voter functionality are methods in voters.php (Listing 11) and the database functionality is in voters_model.php (Listing 12).

The Unregistered Voters List

The first item in the voters menu shows the non registered voters list. The controller for this is voters/nonregistered. This controller executes the method NonRegisters in voters_model and sends that data off to a view called voters.php.

You may note that the controller class is also called voters.php. It is perfectly legal in CodeIgniter to have a controller and view with the same name. For clarity, I have captioned the codelisting for the voters view as voters.php (view) and it can be found in Listing 13.

You can see what the page looks like in Figure 6.

![Figure 6: This is the Families Not Registered page.](https://codemag.com/Article/Image/1312091/Figure 6.png)

This next code snippet is from the nonregistered function in the voters controller class.

if (!$this->_isloggedin()){
redirect('/users/login');
}else{

Note that the controller only checks for logged in status. There is no need to check for administrative rights because this function is for all users.

This next code snippet is from the voters view.

$url = '/voters/withlast/' .
$row['idnum'];

It shows the construction of the URL that loads when the last name is clicked in the non-registered voters list. It loads a controller called withlast that is designed to show all voters in voterfile that might match with this family. It is a match by last name.

Every row in communityfile has an ID number called idnum. It is provided by the community list owner. I sent that through to the withlast method so it can retrieve the communityfile row and then generate the matching rows from voterfile. I didn't bother encrypting the ID here. The information is not particularly sensitive, so I wasn't concerned with encrypting the ID like the users file.

Voters with Last Name…

Withlast does two things. It retrieves the communityfile row that the user clicked on by ID and retrieves a list of all voters with the same last name. You can see what it looks like in Figure 7.

![Figure 7: This list is of voters with the same Last Name.](https://codemag.com/Article/Image/1312091/Figure 7.png)

The controller for this page is voters/withlast and the view is voterswithlast.php (Listing 14). By showing the list of voters with the same last name, I had the ability to check for an incorrect address in communityfile.

Exporting the Reg List

The final requirement was to be able to export the voter registration list. This is useful for mail merging and other things. By having an export function, anyone with access to the system could get a CSV file of the entire registration list.

The model code for this was the simplest of all… a simple SELECT * FROM voterfile did the trick. The question was how to get it to download as a CSV file.

One of the nicer aspects of CodeIgniter is that there are tons of add-ons available for it. I searched and found a CSV helper for CodeIgniter at http://maestric.com/en/doc/php/codeigniter_csv. I downloaded it and put it in my application/helpers folder. Once I had that, the controller for this function was absurdly simple:

public function export(){
if (!$this->_isloggedin()){
redirect('/users/login');
}else{
$this->load->helper('csv');
$query = $this->
voters_model->ExportList();
query_to_csv($query, TRUE,
'reglist.csv');
}
}

The cool part about this is that all I have to do is retrieve my data and run one function, query_to_csv, and the export runs precisely as I want it to.

The intent of this article was to show you a complete system with the theory that went into the implementation of the features. Although the article contains many specifics regarding the voter system, the concepts are universal.

To write a robust, full-featured Web application, you need more than just PHP programming skills. PHP is critical for displaying and manipulating information but it's a mistake to forget the crucial role that MySQL plays in the application. MySQL does much more than SQL SELECTs, INSERTs and UPDATEs. It contains a fairly full-featured programming language that you can use to create procedures and functions that can make your app easier to write and more efficient to boot.

The real challenge, sometimes, is trying to remember what should be put where. When working on data manipulation, consider which tool in your suite makes the most sense. The answer is not always obvious and sometimes there is more than one answer. Using the right technology for the job always make it easier.

I also wanted to show you how to use CodeIgniter to your advantage. I showed you CodeIgniter's session and encryption capabilities to add security to your application and how CodeIgniter's extendibility and large base of add-ons can make programming much easier.

SELECT UUID()
SELECT NEWID()

Listing 1: Users.php

<?php if ( ! defined('BASEPATH'))
exit('No direct script access allowed');

class Users extends MY_Controller {
function __construct(){
parent::__construct();
$this->load->model("users_model");
}

public function index(){
if (!$this->_isloggedin()){
redirect('/users/login');
}elseif (!$this->_iadmin()){
redirect('/voters');
}else{
$userlist =
$this->users_model->Get_user_list();

$data['userlist'] = $userlist;
$lcView = 'modules/users_list_view';
$this->load->view($lcView, $data);
}
}

public function add(){
if (!$this->_isloggedin()){
redirect('/users/login');
}elseif (!$this->_iadmin()){
redirect('/voters');
}else{
$this->session->
set_userdata('usereditmode', 'Add');

$lcView = 'modules/users_edit_view';
$this->load->view($lcView);
}
}

public function edit(){
if (!$this->_isloggedin()){
redirect('/users/login');
}elseif (!$this->_iadmin()){
redirect('/voters');
}else{
$this->session->
set_userdata('usereditmode', 'Edit');
$iduser =
$this->
_uridecode($this->uri->segment(3));
$this->session->
set_userdata('iduser', $iduser);
$userdata =
$this->users_model->
getUserRecord($iduser);

$data['userdata'] = $userdata;
$lcView = 'modules/users_edit_view';
$this->load->view($lcView, $data);
}
}

public function save(){
//Intended to be used with a POST save
if (!$this->_isloggedin()){
redirect('/users/login');
}elseif (!$this->_iadmin()){
redirect('/voters');
}else{
$editmode = $this->session->
userdata('usereditmode');
$salt = $this->config->item('salt');
if(!isset($_POST['cpassword'])
or !$editmode){
exit('Invalid form submit');
}
if ($editmode == "Add"){
$userrec['id'] = $this->_newid();
}else{
$userrec['id'] =
$this->session->userdata('iduser');
}

$userrec['cname'] = $_POST['cname'];
$userrec['cemail'] = $_POST['cemail'];

$userrec['cpassword'] =
hash("SHA256",
$salt.$_POST['cpassword']);
$userrec['ladmin'] =
$this->_checkBoxPostValue('ladmin');
$userrec['ldisabled'] =
$this->_checkBoxPostValue('ldisabled');
$userrec['tlastlogin'] = '0000-00-00';

if($editmode=="Add"){
$this->db->
insert('users', $userrec);
}else{
$this->db->
where('id', $userrec['id']);
$this->db->
update('users', $userrec);
}

$this->session->
unset_userdata('usereditmode');
redirect('/users');
}
}

public function setupadmin(){
$adminuser = $this->users_model->
getUserRecord('admin@voters.local',
'email');

if (isset($adminuser[0]['id'])){
return;
}

//If we get this far, we have to add the admin record
$salt = $this->config->item('salt');
$userrec['id'] = $this->_newid();
$userrec['cname'] =
'Menachem Bazian, CPA (NY)';
$userrec['cemail'] = 'admin@voters.local';
$userrec['cpassword'] =
hash("SHA256",$salt.'admin');
$userrec['ladmin'] = 1;
$userrec['ldisabled'] = 0;
$userrec['tlastlogin'] = '0000-00-00';
$this->db->insert('users', $userrec);
redirect('/users');
}

public function login(){
if (!$this->_isloggedin()){
$loginerror =
$this->session->
userdata('loginerror');

$this->session->
set_userdata("inlogin", "true");

$data['loginerror'] = $loginerror;

$this->load->view('modules/login_view',
$data);
}else{
redirect('/voters');
}
}

public function verify(){
if(!isset($_POST["cemail"]) and
!$this->session->
userdata("inlogin")){

redirect('/users/login');
}else{
$cemail = $_POST["cemail"];
$salt = $this->config->item('salt');

$cpassword = hash("SHA256",
$salt.$_POST['cpassword']);

$userdata = $this->users_model->
getUserRecord($cemail, "email");

if (count($userdata)==0 or
($userdata[0]["cpassword"] !=
$cpassword)){

$this->session->
set_userdata('loginerror', true);
redirect('/');
}else{
//logged in
$this->session->
unset_userdata("inlogin");
$this->session->
set_userdata($userdata[0]);
redirect('/voters');
}
}

}

public function logout(){
$this->session->sess_destroy();
redirect('/users/login');
}
}

Listing 2: MY_Controller.php

<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class MY_Controller Extends CI_Controller
{

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

function _isloggedin(){
$userdata = $this->session->userdata('id');
if (!isset($userdata) or !$userdata){
$loggedin = false;
}else{
$loggedin = true;
}
return $loggedin;
}

function _iadmin(){
if(!$this->_isloggedin()){
$ladmin = false;
}else{
$ladmin = ($this->session->
userdata('ladmin')==1);
}
return $ladmin;
}

function _uriencode($tcURI){
$encoded = base64_encode($this->encrypt->
encode($tcURI));
return $encoded;
}

function _uridecode($tcEncoded){
$decoded = $this->encrypt->
decode(base64_decode($tcEncoded));
return $decoded;
}

function _newid() {
/* based on code found in
* http://stackoverflow.com/questions/
* 4049455/how-to-create-a-uuid-in-php-
* without-a-external-library
*/

return sprintf('%04x%04x-%04x-%04x-%04x-%04x%04x%04x',
// 32 bits for "time_low"
mt_rand( 0, 0xffff ),
mt_rand( 0, 0xffff ),

// 16 bits for "time_mid"
mt_rand( 0, 0xffff ),

// 16 bits for "time_hi_and_version",
// four most significant bits holds
// version number 4
mt_rand( 0, 0x0fff ) | 0x4000,

// 16 bits, 8 bits for
// "clk_seq_hi_res",
// 8 bits for "clk_seq_low",
// two most significant bits holds
// zero and one for variant DCE1.1
mt_rand( 0, 0x3fff ) | 0x8000,

// 48 bits for "node"
mt_rand( 0, 0xffff ),
mt_rand( 0, 0xffff ),
mt_rand( 0, 0xffff )
);
}

function _checkBoxPostValue($index){
if (!isset($_POST[$index])){
$retval = 0;
}else{
$retval = $_POST[$index];
}
return $retval;
}
}





Listing 3: users_model.php

<?php if ( ! defined('BASEPATH'))
exit('No direct script access allowed');

class Users_model extends CI_Model {
function Get_user_list(){
$this->db->select('id, cemail, cname, ' .
'ladmin, cpassword, ldisabled, ' .
'tlastlogin');
$this->db->from('users');
$this->db->order_by('cname');
$query = $this->db->get();

$array_result = $query->result_array();
return $array_result;
}

function getUserRecord($iduser, $idtype="id"){
$this->db->select('id, cemail, cname, ' .
'ladmin, cpassword, ldisabled, ' .
'tlastlogin');
$this->db->from('users');

if ($idtype=="id"){
$this->db->where('id', $iduser);
}else{
$this->db->where('cemail', $iduser);
}
$query = $this->db->get();

$array_result = $query->result_array();
return $array_result;
}
}
?>

Listing 4: login_view.php

<?php
$data['title'] = "Voter Management - Login";

$this->load->view('header', $data);

unset($data);

$data['nomenu'] = true;
$this->load->view('appheader', $data);
?>

<form name="frmlogin"
action="/users/verify/" method="post">
<div class="container">
<H1>Please Login</H1>

<?php
if($loginerror)
echo '<p style="font-color:red">
Invalid user name or password.</p>';
?>

<table border="0" style="margin-left:20px">
<tr>
<td style="padding-bottom:.5em;
padding-top:.5em;
padding-right:.5em;">
Email Address</td>
<td style="padding-bottom:.5em;
padding-top:.5em">
<input type="text"
id="cemail" name="cemail"
size="70" /></td>
</tr>

<tr>
<td style="padding-bottom:.5em;
padding-top:.5em;
padding-right:.5em;">
Password</td>
<td style="padding-bottom:.5em;
padding-top:.5em">
<input type="password"
id="cpassword"
name="cpassword" size="70" /></td>
</tr>

<tr>
<td colspan="2"
style="padding-bottom:.5em;
padding-top:.5em;">
<input type="submit"
name="cmdsubmit" id="cmdsubmit"
value="Login" /> &nbsp;&nbsp;
<a href="/users/forgot/">
Forgot Password?</a></td>
</tr>
</table>
</div>
</form>
<?php
$this->load->view('appfooter');
?>

Listing 5: Header.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">

<link rel="stylesheet"
href="/css/basestyles.css" type="text/css"
media="screen,projection">

<link rel="stylesheet"
href="/css/smoothness/
jquery-ui-1.8.21.custom.css"
type="text/css"
media="screen,projection">

<link rel="stylesheet"
href="/css/1140.css" type="text/css"
media="screen" />

<link rel="stylesheet"
href="/css/menustyles.css" type="text/css"
media="screen,projection">

<script type="text/javascript"
src="/js/jQuery.js"></script>

<script type="text/javascript"
src="/js/jquery.tablesorter.js"></script>

<?php
if(!isset($title)) $title = "CFO Elf";
?>
<title><?=$title?></title>
<?php
if(isset($moreheader)) {
foreach ($moreheader as $line){
echo $line . "\n";
}
}
?>
</head>
<body>

Listing 6: Appheader.php

<div class="container" style="margin-top: 20px;
align:left; width:80%; margin-left:
auto;margin-right: auto;" >

<div class="container"
style="margin-top:20px;
align:left; width:100%;
margin-left: auto;" >

<div class="row">
<div class="fourcol"
style="float:left;
width: 400px;">
<h1>Voter Information</H1></div>
</div>
</div>

<?php
if (!isset($nomenu) or !$nomenu){
$this->load->view('appmenu');
}
?>
<div style="clear:both; margin: 0 0 30px 0;">
&nbsp;</div>

<div class="container" id="body"
style="margin-top: 20px; align:left;
width:100%; margin-left: auto;
height:auto" >

Listing 7: Appfooter.php

<br><br><br>
<div class="container" id="footer"
style="height:4em" >
<p style="text-alignment:center;">
Copyright (c) BC Consulting
Services, Inc., 2013</p>
</div>
</div>

</div>

Listing 8: Appmenu.php

<div class="container"
style="margin-top: 20px; align:left;
width:100%; " >

<div class='cssmenu'>
<ul>

<li class='active '><a href='/'>
<span>Home</span></a></li>

<li><a href="#">
<Span>Voters</Span></a>
<ul>
<li><a href='/voters/nonregistered'>
<span>Unregistered</span></a></li>

<li><a href='/voters/Export'>
<span>Export to CSV</span></a></li>
</ul>
</li>

<?php
$ladmin=$this->session->userdata("ladmin")==1;
if ($ladmin){
echo "<li><a href='/users'>" .
"<span>Users</span></a></li>";
}
?>
<li><a href='/voters/about'>
<span>About</span></a></li>

<li><a href='#'><span>Contact</span></a></li>
<li><a href='/users/logout'>
<span>Logout</span></a></li>
</ul>
</div>
</div>

Listing 9: users_list_view.php


<?php
$data['title'] =
"Voter Management - Users List";

$moreheader[0] =
'<script type="text/javascript">';
$moreheader[1] =
'	$(document).ready(function()';
$moreheader[2] = '          {';
$moreheader[3] =
'               $("#userlist").tablesorter();';
$moreheader[4] = '          }';
$moreheader[5] = '     );';
$moreheader[6] = '</script>';

$data['moreheader'] = $moreheader;

$this->load->view('header', $data);
$this->load->view('appheader');

$numnames = count($userlist);
$ci = & get_instance();
?>

<div class="container">
<table>
<tr>
<td><H1>Authorized Users</H1></td>
</tr>
<tr>
<td><P><?=$numnames?> users.&nbsp;&nbsp;&nbsp;
Add User</P></td>
</tr>
</table>
<table id = "userlist" class = "tablesorter">
<thead>
<tr>
<th>Name</th>
<th>Email Address</th>
<th>Admin?</th>
<th>Disabled?</th>
</tr>
</thead>

<tbody>

<?php
$lnCounter = 0;
foreach($userlist as $row):
$url = '/users/edit/'.
$ci->_uriencode($row['id']);
$lnCounter = $lnCounter + 1;
$admin = ($row['ladmin']==1 ?
"Yes" : "No");
$disabled = ($row['ldisabled']==1 ?
"Yes" : "No");
?>
<tr>
<td><a href='<?=$url?>'
target='_blank'>
<?=$row['cname']?></a></td>

<td><?=$row['cemail']?></td>
<td><?=$admin?></td>
<td><?=$disabled?></td>
</tr>
<?php endforeach; ?>

</tbody>
</table>
</div>

<?php
$this->load->view('appfooter');
?>

Listing 10: users_edit_view.php


<?php
/* File........................: Users_edit.php
* View for Controller/Method..:
*       users/edit
* Created by..................:
*        Menachem Bazian, CPA (NY), CITP
* Date........................: 1/21/2013
*
*/

$data['title'] = "Voter Management - Users Edit";
$moreheader[0] = '<script type="text/javascript" '.
'src="/js/usersedit.js"> ' .
'</script>';
$data['moreheader'] = $moreheader;

$this->load->view('header', $data);
$this->load->view('appheader');

if (isset($userdata)){
$lcName = $userdata[0]['cname'];
$lcEmail = $userdata[0]['cemail'];
$lcPassword = "";

if ($userdata[0]['ladmin']==1){
$lcAdmin = "checked";
}else{
$lcAdmin = "";
}

if ($userdata[0]['ldisabled']==1){
$lcDisabled = "checked";
}else{
$lcDisabled = "";
}
}else{
$lcName = "";
$lcEmail = "";
$lcPassword = "";
$lcAdmin = "";
$lcDisabled = "";
}
?>

<div id="diverrors" style="visibility: hidden;
width:500px; margin-left:0px;
margin-right:auto; border:solid 1px red">
</div>

<br>

<h1>Maintain User Information</h1>
<H2 style="font-size:medium; font-weight:bold;">
(All fields are required)</h2>
<br>
<form class="cmxform" name="frmUserEdit"
action="/users/save/" method="post">

<table border="5" align="center">
<tr>
<td class="de_label">Name&nbsp;&nbsp;</td>
<td><input type="text" name="cname"
id="cname" maxlength="50" size="50"
value="<?=$lcName?>"></td>
</tr>

<tr>
<td class="de_label">
Email Address&nbsp;&nbsp;</td>
<td><input type="text" name="cemail"
id="cemail" maxlength="100" size="70"
value="<?=$lcEmail?>"></td>
</tr>

<tr>
<td class="de_label">
Password&nbsp;&nbsp;</td>
<td><input type="password" name="cpassword"
id="cpassword" maxlength="100"
size="35"></td>
</tr>

<tr>
<td class="de_label">
Confirm Password&nbsp;&nbsp;</td>
<td><input type="password"
name="cpasswordconfirm"
id="cpasswordconfirm"
maxlength="100" size="35"></td>
</tr>

<tr>
<td class="de_label"></td>
<td>
<input type="checkbox" name="ladmin"
id="ladmin" value="1"
<?=$lcAdmin?>/>
User is an administrator
</td>
</tr>

<tr>
<td class="de_label"></td>
<td>
<input type="checkbox"
name="ldisabled" id="ldisabled"
value="1" <?=$lcDisabled?> />
User is disabled
</td>
</tr>

<tr>
<td class="de_label"></td>
<td>
<input type="button" name="cmdsave"
value="Save"
onclick="formValidate();" />
&nbsp;&nbsp;
<input type="button" name="cmdcancel"
value="Cancel" />
</td>
</tr>
</table>
</form>

<?php
$this->load->view('appfooter');
?>

Listing 11: voters.php

<?php if ( ! defined('BASEPATH'))
exit('No direct script access allowed');

class Voters extends MY_Controller {

function __construct(){
parent::__construct();
$this->load->model("voters_model");
}

public function index(){
if (!$this->_isloggedin()){
redirect('/users/login');
}else{
$this->load->
view('modules/blank_view');
}
}

public function about(){
if (!$this->_isloggedin()){
redirect('/users/login');
}else{
$this->load->
view('modules/aboutus_view');
}
}

public function nonregistered(){
if (!$this->_isloggedin()){
redirect('/users/login');
}else{
$this->output->enable_profiler(false);
$voters = $this->
voters_model->NonRegisters();

$data['voters'] = $voters;
$this->load->
view('modules/voters', $data);
}
}

public function withlast(){
if (!$this->_isloggedin()){
redirect('/users/login');
}else{
$idnum = $this->uri->segment(3);
$cfrow = $this->voters_model->
CFListRow($idnum);
$lastname = $cfrow[0]['family_name'];
$voters = $this->voters_model->
VotersWithLast($lastname);

$data['cfrow'] = $cfrow[0];
$data['voters'] = $voters;
$data['lastname'] = $lastname;
$data['nomenu'] = true;
$this->load->
view('modules/voterswithlast', $data);
}
}

public function export(){
if (!$this->_isloggedin()){
redirect('/users/login');
}else{
$this->load->helper('csv');
$query = $this->voters_model->ExportList();
query_to_csv($query, TRUE, 'reglist.csv');
}
}
}

Listing 12: voters_model.php

<?php if ( ! defined('BASEPATH'))
exit('No direct script access allowed');

class Voters_model extends CI_Model {
function NonRegisters(){
$lcSQL = "SELECT cf.idnum, vf.voter_id, " .
"cf.family_name, " .
"if(his_first='', her_first, " .
"his_first) as address1mm, " .
"CONCAT(cf.houseno, ' ', " .
"cf.street, ' ', cf.sttype) " .
"as address, cf.telephone " .
"FROM communityfile cf " .
"LEFT OUTER JOIN voterfile vf " .
"ON cf.family_name = vf.last " .
"AND cf.houseno = vf.houseno " .
"AND cf.street = vf.street " .
"WHERE vf.voter_id is null " .
"ORDER BY cf.family_name ";

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

$array_result = $query->result_array();
return $array_result;
}

function VotersWithLast($tcName){
$lcSQL = "SELECT voter_id, last, first, " .
"CONCAT(houseno, ' ', street, " .
"' ', sttype, ' ', apt) " .
"AS address, dob, " .
"registration_date " .
"FROM voterfile WHERE last='" .
$tcName . "' ORDER BY first";

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

$array_result = $query->result_array();
return $array_result;
}

function CFListRow($idnum){
$lcSQL = "SELECT cf.idnum, cf.family_name, " .
"his_first as address1mm, " .
"CONCAT(cf.houseno, ' ', " .
"cf.street, ' ', cf.sttype) " .
"as address, cf.telephone " .
"FROM communityfile cf " .
"WHERE idnum = " . $idnum ;

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

$array_result = $query->result_array();
return $array_result;
}

function ExportList($tlForExport = true){
$lcSQL = "SELECT * FROM voterfile ";

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

if (!$tlForExport){
$retval = $query->result_array();
}else{
$retval = $query;
}

return $retval;
}

}
?>

Listing 13: voters.php (view)

<?php
$data['title'] = "Unregistered Voters";

$moreheader[0] =
'<script type="text/javascript">';
$moreheader[1] =
'     $(document).ready(function()';
$moreheader[2] =           '          {';
$moreheader[3] =
'            $("#voterlist").tablesorter();';
$moreheader[4] = '          }';
$moreheader[5] = '     );';
$moreheader[6] = '</script>';

$data['moreheader'] = $moreheader;

$this->load->view('header', $data);
$this->load->view('appheader');
$this->load->helper('url');

$numnames = count($voters);
?>

<div class="container">
<table>
<tr>
<td><H1>Families Not Registered to Vote</H1>
</td>
</tr>
<tr>
<td><P><?=$numnames?> families listed.</P></td>
</tr>
</table>
<table id = "voterlist" class = "tablesorter">
<thead>
<tr>
<th>Last Name</th>
<th>M/M Name</th>
<th>Address</th>
<th>Phone</th>
</tr>
</thead>

<tbody>

<?php
$lnCounter = 0;
foreach($voters as $row):
$url = '/voters/withlast/' .
$row['idnum'];
$lnCounter = $lnCounter + 1;
?>
<tr>
<td><a href='<?=$url?>'
target='_blank'>
<?=$row['family_name']?></a>
</td>
<td><?=$row['address1mm']?></td>
<td><?=$row['address']?></td>
<td><?=$row['telephone']?></td>
</tr>
<?php endforeach; ?>

</tbody>
</table>
</div>

<?php
$this->load->view('appfooter');
?>

Listing 14: voterswithlast.php

<?php
$data['title'] = "Registered Voters with " .
"last name \"" . $lastname .
"\"" ;
$moreheader[0] =
'<script type="text/javascript">';
$moreheader[1] =
'     $(document).ready(function()';
$moreheader[2] = '          {';
$moreheader[3] =
'            $("#voterlist").tablesorter();';
$moreheader[4] = '          }';
$moreheader[5] = '     );';
$moreheader[6] = '</script>';

$data['moreheader'] = $moreheader;

$this->load->view('header', $data);
$this->load->view('appheader');
$this->load->helper('url');

$fullcf = $cfrow['address1mm'] . " " .
$cfrow['family_name'] . ", " .
$cfrow['address']
?>

<div class="container">
<table>
<tr>
<td><H1>Registered Voters from Voter
Reg List</H1></td>
</tr>
<tr>
<td><P>With last name:
<?=$cfrow['family_name']?></P></td>
</tr>

<tr>
<td><P>Looking for a match for
<?=$fullcf?></P></td>
</tr>

</table>
<table id = "voterlist" class = "tablesorter">
<thead>
<tr>
<th>Last</th>
<th>First</th>
<th>Address</th>
<th>Date of Birth</th>
<th>Registratin Date</th>
</tr>
</thead>

<tbody>

<?php
$lnCounter = 0;
foreach($voters as $row):
?>
<tr>
<td><?=$row['last']?></td>
<td><?=$row['first']?></td>
<td><?=$row['address']?></td>
<td><?=$row['dob']?></td>
<td>
<?=$row['registration_date']?>
</td>
</tr>
<?php endforeach; ?>

</tbody>
</table>
</div>

<?php
$this->load->view('appfooter');
?>

Column Type `Null` Default
voter_idint(11)NO'0'
lastvarchar(50)NO''
firstvarchar(50)NO''
middlevarchar(50)NO''
suffixvarchar(50)NO''
street_numbervarchar(50)NO''
sufavarchar(50)NO''
sufbvarchar(50)NO''
street_namevarchar(50)NO''
aptvarchar(50)NO''
cityvarchar(50)NO''
municipalityvarchar(50)NO''
zipvarchar(50)NO''
dobvarchar(50)NO''
partyvarchar(50)NO''
wardvarchar(50)NO''
districtvarchar(50)NO''
voter_statusvarchar(50)NO''
registration_datevarchar(50)NO''
reg_prov_ballotvarchar(50)NO''
housenovarchar(50)NO''
streetvarchar(50)NO''
sttypevarchar(50)NO''
ckeyvarchar(100)NO''

Index Name Key Column
Primary Key voterid
idx_voterfile_family_namelast
idx_voterfile_housenohouseno
idx_voterfile_streetstreet
idx_voterfile_ckeyckey

Column Type `Null`s Default
family_namevarchar(50)NO''
address1mmvarchar(50)NO''
addressvarchar(50)NO''
cityvarchar(50)NO''
statevarchar(50)NO''
zipvarchar(50)NO''
telephonevarchar(50)NO''
his_firstvarchar(50)NO''
her_firstvarchar(50)NO''
address1mrvarchar(50)NO''
address1mrsvarchar(50)NO''
idnumint(11)NO'0'
housenovarchar(50)NO''
streetvarchar(50)NO''
sttypevarchar(50)NO''
aptvarchar(50)NO''
ckstreetvarchar(50)NO''
voteridint(11)NO'0'
ckeyvarchar(100)NO''

Index Name Key Column
Primary Key Idnum
idx_communityfile_family_namefamily_name
idx_communityfile_housenohouseno
idx_communityfile_streetstreet
idx_communityfile_ckeyckey

Column Type `Null` Default
idvarchar(36)NO''
cemailvarchar(100)NO''
cnamevarchar(100)NO''
cpasswordvarchar(100)NO''
ladminint(11)NO'0'
ldisabledint(11)NO'0'
tlastloginDatetimeNO�00-00 00:00:00'

Index Name Key Column
Primary Keyid
idx_users_cemailcemail

Column Type `Null` Default
session_idvarchar(40)NO'0'
ip_addressvarchar(45)NO'0'
user_agentvarchar(120)NO
last_activityint(10) unsignedNO'0'
user_datatext NO

Index Name Key Column
Primary_Keysession_id
last_activity_idxlast_activity