My packing slips project for work has been well received and used by all our engineers. Now with daily use there are improvements and new features requested. This was the first app I built using CodeIgniter and I used some simple techniques along the way. In some cases I would have to completely rewrite sections to complete all the upgrades.
Vendors List
The first upgrade I made was the one of the simpler requests. In the first draft I hard coded two of the common addresses where we ship items. A drop down would list these address and when selected I used Javascript to fill in the address fields. I know my idea had been to just hard code more presets, but this was not going to work long term.
Enter the vendors table. I created a new table called vendors and added a separate page in the application to view and add new vendors. It’s a simple form that saves the name, address, city, state and zip code of a vendor. It also allows for editing and deleting from this Vendors page. My problem was finding a way to get this model and table data into the rest of the application.
It’s quite easy and only takes one query. I created a new function in my PackingSlips_model called get_the_vendors().
public function get_the_vendors()
{
$query = $this->db->get('vendors');
return $query->result_array();
}Then in my controller I call this function when in the create function.
public function create()
{
$this->load->helper('form');
$this->load->library('form_validation');
$data['title'] = 'Create a Packing Slip';
$data['vendors'] = $this->PackingSlips_model->get_the_vendors();Now I have an array variable called vendors sent to the create page. Within my drop-down menu I loop through this array creating an option for each vendor.
<select class='select-address form-control' id='select-address'>
<option value='none' selected='selected'>None</option>
<?php
$i = 0;
foreach($vendors as $vendor) {
echo "<option value='$i'>".$vendor['vendor_name']."</option>";
$i = $i + 1;
}
?>
</select>Here I used my iteration variable to give each option the same index value of the array. This worked great to create a list of all the names of vendors. The only problem is this array is a PHP variable and I can’t use Javascript to interact with it.
I found the json_encode() function will return a JSON representation of the variable. Inside my script tag I can return my variable as a JSON object and set that to a Javascript variable.
var vendors = <?php echo json_encode($vendors) ?>;Now that I have a Javascript variable with all of my vendors I can write the Javascript needed to watch the select and update the form with the data from the JSON object.
window.onload = function() {
if(window.addEventListener) {
document.getElementById('select-address').addEventListener('change', loadAddress, false);
} else if (window.attachEvent){
document.getElementById('select-address').attachEvent("onchange", loadXMLDoc);
}
function loadAddress(){ // When select change detected, use the vendors variable to fill in the address fields
var select = document.getElementById('select-address');
var val = select.options[select.selectedIndex].value;
var name = document.getElementById('slip_shipName');
var address = document.getElementById('slip_shipAddress');
var city = document.getElementById('slip_shipCity');
var state = document.getElementById('slip_shipState');
var zip = document.getElementById('slip_shipZip');
if(val == 'none'){ // Check if None preset selected
name.value = '';
address.value = '';
city.value = '';
state.value = '';
zip.value = '';
return;
}
name.value = vendors[val]['vendor_name'];
address.value = vendors[val]['vendor_address'];
city.value = vendors[val]['vendor_city'];
state.value = vendors[val]['vendor_state'];
zip.value = vendors[val]['vendor_zip'];
}
}With this in place we can add any Vendor address using the Vendor’s form and be added to a new packing slip. I rolled out this update and started working on the next request, many devices per packing slip.
The actual relational database part
It took longer than I thought to describe the addition of a dynamic vendors list. Now let me move on to the bigger update. Currently you could only add one device to a packing slip. This was fine for the majority of slips created, but still needed improvement.
When I first built the app I used one database table that held all the data about the slip as well as the device. The first step was normalizing this table and move the device information to their own table. The relationship between the tables would be one to many. I started by creating a new table called ‘items’. I prefixed the fields with item_ except for the foreign key which I named slip_id_fk. This gave me a clear definition to create the relationship.
Before getting started I added sample data and got the single and index views working. My index page that listed all the slips used the name of the device as a title of the packing slip. I could now have many devices so I decided to add a field to the slip database called slip_description. This would be a short description used in both the main list of slips as well as the title of the single view.
My controller stayed the same
public function index()
{
$this->load->library('session');
$this->load->helper('form');
$data = array();
$data['slips'] = $this->PackingSlips_model->get_slips();
$data['title'] = 'Packing Slip Archive';
$this->load->view('templates/header', $data);
$this->load->view('slips/index.php', $data);
$this->load->view('templates/footer');
}
public function view()
{
$id = $this->uri->segment(2);
$data['slips_item'] = $this->PackingSlips_model->get_slips($id);
if (empty($id))
{
show_404();
}
$data['title'] = 'Packing Slip: #'.$data['slips_item']['slip_id'];
$this->load->view('templates/header', $data);
$this->load->view('slips/view', $data);
$this->load->view('templates/footer');
}And in fact my model for the index page stayed the same as well. I decided the index list didn’t need to list any of the device information. Instead it shows the slip ID, Description, Vendor, FedEx Tracking #, Last Modified Date, and Status. This is all information from the Slips Table so I didn’t need to do any joins. Both the ID and the Description are anchor tags that link to the single view of the packing slip.
public function get_slips($id = 0)
{
if ($id === 0)
{
$this->db->select('*');
$this->db->from('slips');
$query = $this->db->get();
return $query->result_array();
}So my query is a select all from slips. On a single slip view I needed to merge the info from the slips and items table. For this you use a join clause in your query. Here is the complete get_slips model
public function get_slips($id = 0)
{
if ($id === 0)
{
$this->db->select('*');
$this->db->from('slips');
$query = $this->db->get();
return $query->result_array();
}
$this->db->select('*');
$this->db->from('slips');
$this->db->join('items', 'items.slip_id_fk = slips.slip_id');
$this->db->where('slips.slip_id', $id);
$query = $this->db->get();
return $query->result();
}Here I’m using Codeigniter’s Query Builder class. I add properties to the query before it executes based on the if statement. The join clause lets us merge data from many database tables that we selecting from the database. The sql join needs several parameters. First the table to add, then the fields that should match. The first field is the added table, and then the original. After that I add the where clause to limit the query to the id of the current page. The $id is passed from the controller. You’ll see I use the uri segment to get the ID and pass it on to the model.
My Controller uses the get_slips function and assigns it to the ‘slips_item’ index of $data. This variable is an array of objects that hold the data returned from the database. In my view template I echo data from the object using the first item in the array.
<?php echo $slips_item[0]->slip_shipName; ?>This basic information is the same in each iteration of the array. I use the first one to print out the shipping and other information. Down the road I’ll need to see if there is a better way to go about this.
Next to display many devices I loop through the array and print out the item data.
<?php foreach ($slips_item as $item) : ?>
<tr>
<td><?php echo $item->item_manufacturer; ?></td>
<td><?php echo $item->item_deviceName; ?></td>
<td><?php echo $item->item_modelNumber; ?></td>
<td><?php echo $item->item_serialNumber; ?></td>
<td><?php echo $item->item_quantity; ?></td>
</tr>
<?php endforeach;?>Next step: Creating new relational database data
Viewing the relational data ended up being pretty easy once you get your head wrapped around database joins. I thought creating new database rows would be difficult, but it’s all about separating out what you need. Let’s begin with the controller.
public function create()
{
$this->load->helper('form');
$this->load->library('form_validation');
$data['title'] = 'Create a Packing Slip';
$data['vendors'] = $this->PackingSlips_model->get_the_vendors();
if ($this->form_validation->run('packing-slip') === FALSE)
{
$this->load->view('templates/header', $data);
$this->load->view('slips/create');
$this->load->view('templates/footer');
}
else
{
$this->PackingSlips_model->set_slip();
$this->load->library('session');
$this->session->set_flashdata('msg', '<div class="alert alert-success" role="alert">Packing Slip Created</div>');
redirect('slips');
return TRUE;
}
}The first thing I do is load my helper files for form and form_validation. Next I bring in the vendors data that I mentioned at the top of this post. My initial version had a lot of form validation up next, but I learned about separating out the validation to clean up the controller. You can create a form_validation.php file in your config folder. Next you create a $config variable that is an array of different validations you’ll want to use in your application.
<?php
$config = array(
'packing-slip' => array(
array(
'field' => 'item_assetTag[]',
'label' => 'Asset Tag',
'rules' => 'required',
),
array(
'field' => 'item_manufacturer[]',
'label' => 'Manufacturer',
'rules' => 'required',
),
etc...Then when you want to run form validation as I do in my if statement above you just reference the name of the index in your config array. In this case “packing-slip.” I only have the one form to validate so I could have done it all in the controller, but I like the clean look here since I had so many items to validate. The views load if the form doesn’t validate (or reload with the error information), and the set_slips function runs if it succeeds.
public function set_slip($id = 0)
{
$this->load->helper('url');
$this->load->helper('date');
$slip_data = array(
'slip_shipName' => $this->input->post('slip_shipName'),
'slip_shipAddress' => $this->input->post('slip_shipAddress'),
'slip_shipCity' => $this->input->post('slip_shipCity'),
'slip_shipState' => $this->input->post('slip_shipState'),
'slip_shipZip' => $this->input->post('slip_shipZip'),
'slip_fedexTracking' => $this->input->post('slip_fedexTracking'),
'slip_rmaNumber' => $this->input->post('slip_rmaNumber'),
'slip_comments' => $this->input->post('slip_comments'),
'slip_status' => $this->input->post('slip_status'),
'slip_id' => $this->input->post('slip_id'),
'slip_customerContact' => $this->input->post('slip_customerContact'),
'slip_customerPhone' => $this->input->post('slip_customerPhone'),
'slip_description' => $this->input->post('slip_description'),
'slip_lastModified' => date('F j, Y g:i a')
);
$item_data = array(
'item_assetTag' => $this->input->post('item_assetTag'),
'item_manufacturer' => $this->input->post('item_manufacturer'),
'item_deviceName' => $this->input->post('item_deviceName'),
'item_modelNumber' => $this->input->post('item_modelNumber'),
'item_serialNumber' => $this->input->post('item_serialNumber'),
'item_quantity' => $this->input->post('item_quantity')
);
if ($id === 0 )
{
$this->db->insert('slips', $slip_data);
$item_data['slip_id_fk'] = $this->db->insert_id();
for( $i = 0 ; $i < count($item_data['item_assetTag']) ; $i++){
$insert = array(
'item_assetTag' => $item_data['item_assetTag'][$i],
'item_manufacturer' => $item_data['item_manufacturer'][$i],
'item_deviceName' => $item_data['item_deviceName'][$i],
'item_modelNumber' => $item_data['item_modelNumber'][$i],
'item_serialNumber' => $item_data['item_serialNumber'][$i],
'item_quantity' => $item_data['item_quantity'][$i],
'slip_id_fk' => $item_data['slip_id_fk']
);
$this->db->insert('items', $insert);
}
}
else
{
$slip_data['slip_id'] = $this->input->post('slip_id');
$this->db->where('slip_id', $slip_data['slip_id']);
$this->db->update('slips', $slip_data);
$item_data['item_id'] = $this->input->post('item_id');
for( $i = 0; $i < count($item_data['item_assetTag']); $i++ ) {
$insert = array(
'item_id' => $item_data['item_id'][$i],
'item_assetTag' => $item_data['item_assetTag'][$i],
'item_manufacturer' => $item_data['item_manufacturer'][$i],
'item_deviceName' => $item_data['item_deviceName'][$i],
'item_modelNumber' => $item_data['item_modelNumber'][$i],
'item_serialNumber' => $item_data['item_serialNumber'][$i],
'item_quantity' => $item_data['item_quantity'][$i],
'slip_id_fk' => $slip_data['slip_id']
);
$this->db->where('item_id', $insert['item_id']);
$this->db->replace('items', $insert);
}
}
}Since this function handles both creating and updating data it has a lot going on. The first thing I do it grab my post data and assign the slip data and item data to their own array variables. In my form I assign the name of each of the devices to an array:
<input name="item_assetTag[0]" type='input' class='form-control' id='item_assetTag_0' placeholder='' value="<?php echo set_value('item_assetTag[]', ''); ?>">So my structure of the $item_data variable for 3 devices is:
$slip_data
(
'item_assetTag' => Array
(
[0] => a
[1] => b
[2] => c
)
'item_manufacturer' => Array
(
[0] => a
[1] => b
[2] => c
)
'item_deviceName' => Array
(
[0] => a
[1] => b
[2] => c
)
'item_modelNumber' => Array
(
[0] => a
[1] => b
[2] => c
)
'item_serialNumber' => Array
(
[0] => a
[1] => b
[2] => c
)
'item_quantity' => Array
(
[0] => a
[1] => b
[2] => c
)
)We’ll loop over this array in a second. First we deal with creating new table rows when the id is zero.
if ($id === 0 )
{
$this->db->insert('slips', $slip_data);
$item_data['slip_id_fk'] = $this->db->insert_id();
for( $i = 0 ; $i < count($item_data['item_assetTag']) ; $i++){
$insert = array(
'item_assetTag' => $item_data['item_assetTag'][$i],
'item_manufacturer' => $item_data['item_manufacturer'][$i],
'item_deviceName' => $item_data['item_deviceName'][$i],
'item_modelNumber' => $item_data['item_modelNumber'][$i],
'item_serialNumber' => $item_data['item_serialNumber'][$i],
'item_quantity' => $item_data['item_quantity'][$i],
'slip_id_fk' => $item_data['slip_id_fk']
);
$this->db->insert('items', $insert);
}
}First we insert our slip_data array into the slips table. We need to know the id of that row to reference in our foreign key so we use $this->db->insert_id() to return the id of the last row created. I assign this to the ‘slip_id_fk’ key of my $item_data variable. Unlike the other values it isn’t an array so we’ll need to make sure we deal with that in our loop.
Next in the loop I count the number of items in the item_assetTag array. This will be the same count as the rest of the keys in the array so I just went with the first one. Then we create a new array called insert and assign the variables for our first new row. Make sure to use the [$i] key on each item_data field. Then we assign the foreign key, ‘slip_id_fk’, that we got from the database. We don’t need the iterator value here since it is not an array.
Then we insert that array of values into our table and loop through the next device. This way we can add as many new devices to a packing slip that we want.
Edit a relational database packing slip
When we open up the edit.php template I use a hidden input to capture the slip_id of the row we are editing.
<?php
$attributes = array('class' => 'form-horizontal', 'id' => 'packing-slip-form');
$hidden = array('slip_id' => $slips_item[0]->slip_id);
echo form_open('slips/edit/' . $slips_item[0]->slip_id, $attributes, $hidden);
?>I also created a hidden input for the item id of each device.
<input type="hidden" name="item_id[]" value="<?php echo $item->item_id; ?>" style="display:none;">This input is in a foreach loop that I’ll get to in a minute.
Now in the else portion of our set_slip function we grab the id’s before updating the database.
else
{
$slip_data['slip_id'] = $this->input->post('slip_id');
$this->db->where('slip_id', $slip_data['slip_id']);
$this->db->update('slips', $slip_data);
$item_data['item_id'] = $this->input->post('item_id');
for( $i = 0; $i < count($item_data['item_assetTag']); $i++ ) {
$insert = array(
'item_id' => $item_data['item_id'][$i],
'item_assetTag' => $item_data['item_assetTag'][$i],
'item_manufacturer' => $item_data['item_manufacturer'][$i],
'item_deviceName' => $item_data['item_deviceName'][$i],
'item_modelNumber' => $item_data['item_modelNumber'][$i],
'item_serialNumber' => $item_data['item_serialNumber'][$i],
'item_quantity' => $item_data['item_quantity'][$i],
'slip_id_fk' => $slip_data['slip_id']
);
$this->db->where('item_id', $insert['item_id']);
$this->db->replace('items', $insert);
}
}And that’s about it for creating and updating relational databases. I did all this a few weeks ago so I forget exactly where I struggled the most. I know it took me a while to learn about the insert_id() function to get the actual id of the row created. For a while I was only writing to one database table and couldn’t figure out what the issue was.
Javascript for dynamic forms
Getting my data into the database was my first problem. Next I needed to figure out my form so that my engineers could add as many devices to the list as they needed, but also be able to target these devices to import data from our wiki.
Each form starts with one device so I hard coded a zero into the index for each necessary value. Then my “Add Device” button runs the following jQuery.
$('#add-device').click(function(e){
e.preventDefault;
counter = $('.device').length;
$('#form').append(
"<div class='row device' id='device_"+(counter+1)+"'>\
<h2 id='device_1'>Device "+(counter+1)+"</h2>\
<div class='col-md-6'>\
<div class='form-group'>\
<label for='item_assetTag' class='col-sm-3 control-label'>Asset Tag Number</label>\
<div class='col-sm-9'>\
<input name='item_assetTag["+counter+"]'' type='input' class='form-control' id='item_assetTag_"+counter+"' placeholder='' value=''>\
</div>\
</div>\
<div class='form-group'>\
<label for='item_manufacturer' class='col-sm-3 control-label'>Manufacturer</label>\
<div class='col-sm-9'>\
<input name='item_manufacturer["+counter+"]' type='input' class='form-control' id='item_manufacturer_"+counter+"' placeholder='' value=''>\
</div>\
</div>\
<div class='form-group'>\
<label for='item_deviceName' class='col-sm-3 control-label'>Device Name</label>\
<div class='col-sm-9'>\
<input name='item_deviceName["+counter+"]' type='input' class='form-control' id='item_deviceName_"+counter+"' placeholder='' value=''>\
</div>\
</div>\
</div>\
<div class='col-md-6'>\
<div class='form-group'>\
<label for='item_modelNumber' class='col-sm-3 control-label'>Model Number</label>\
<div class='col-sm-9'>\
<input name='item_modelNumber["+counter+"]' type='input' class='form-control' id='item_modelNumber_"+counter+"' placeholder='' value=''>\
</div>\
</div>\
<div class='form-group'>\
<label for='item_serialNumber' class='col-sm-3 control-label'>Serial Number</label>\
<div class='col-sm-9'>\
<input name='item_serialNumber["+counter+"]' type='input' class='form-control' id='item_serialNumber_"+counter+"' placeholder='' value=''>\
</div>\
</div>\
<div class='form-group'>\
<label for='item_quantity' class='col-sm-3 control-label'>Quantity</label>\
<div class='col-sm-9'>\
<input name='item_quantity["+counter+"]' type='input' class='form-control' id='item_quantity' placeholder='' value=''>\
</div>\
</div>\
</div>\
</div>"
);
$('#deviceNumber').append('<option value="'+counter+'">Device '+(counter+1)+'</option>');
console.log('button clicked');
counter++;
});When clicked it creates a counter variable that counts the number of current devices. I began with this hard coded, but when adding a device when editing a slip I needed a way to start with the current number and not 1. Then it creates new fields in the form for a device using the counter variable to increase the index number of each subsequent device. This was necessary to import data from our wiki using the following script:
var assetData = {};
var deviceNumber = 0;
function loadData() {
console.log('loading-data');
var $body = $('body');
var $returned = $('#returned');
var asset = $('#wiki-asset').val();
var wikiRequestTimeout = setTimeout(function() {
$('body').append("Failed to get wikipedia resources");
}, 8000);
var wikiUrl = "http://###.###.###.###/mediawiki/api.php?format=json&action=parse&page=" + asset;
$.ajax({
url: wikiUrl,
dataType: "jsonp",
success: function( response ) {
var page = response.parse;
var title = page.displaytitle;
var text = page.text["*"];
$returned.html(text);
var $table = $('.wikitable tbody').first();
$table.children('tr').each(function(e) {
var key = $(this).find('th').text().trim();
var value = $(this).find('td').text().trim();
assetData[key] = value;
});
console.log(assetData);
insertData(assetData);
clearTimeout(wikiRequestTimeout);
}
});
return false;
}
$('#wiki-submit-btn').on('click', function(e) {
e.preventDefault();
loadData();
var asset = $('#wiki-asset').val();
deviceNumber = $('#deviceNumber').find(":selected").val();
$('#item_assetTag_'+deviceNumber).val(asset);
$('#wiki-asset').val('927-######');
});
function insertData(assetData) {
$('#item_manufacturer_'+deviceNumber).val(assetData['Manufacturer']);
$('#item_deviceName_'+deviceNumber).val(assetData['Device Name']);
$('#item_modelNumber_'+deviceNumber).val(assetData['Model Number']);
$('#item_serialNumber_'+deviceNumber).val(assetData['Serial Number']);
}I wrote about how I was able to capture data from a mediawiki in this blog post. This script gets that data and then inserts it into the values of which ever selected device in the dropdown. That’s the deviceNumber, its a select with options for each device. Every time you click the Add Device button the javascript adds a new dropdown option to this select.
Wrapping things up
I still have some work to do on this project. I need to add the option to delete individual devices from a packing slip. I also need to rearrange some of the pages. The main change creating a new page as an archive for completed slips. I also need to add search functionality. But for now everything is working great and my coworkers are happy to use the application.
Leave a Reply