« Authentication (Login / Logout) & Sessions | Main | Common Header & Footer Includes »

March 30, 2009

Doing Database "Create Review Update Delete" With Functions In add_update.php

There are a set of functions for abstracting DB CRUD functions in the common_php/add_update_functions.php script.

The add_new_row($table_name) function

This function adds a new row into the table specified, populating only the insert_by and insert_date columns and returning the new primary key value.

The update_table($table_name, $r, $primary_key) Function

This function is for applying updates to individual rows in a table. It is typically used to update a row with the $r / $_REQUEST hash from a form submission. The last argument is the primary key of the row in question, which may have been derived from calling the add_new_row() function.

In the following doozer example code snippet, the same piece of code can be used to either insert a new row or update an existing one depending on whether or not a primary key has been passed in the request:

if(!$r['page_id']){ $r['page_id'] = add_new_row('simple_page'); }

$reply = update_table('simple_page', $r, $r['page_id']);

This function can also be used to delete / expire a row thus:

$reply = update_table('simple_page', array('expired_date' => mktime()), $r['page_id']);

If you want to set a NULL value in a column, for example to undelete / unexpire a row, use:

$reply = update_table('simple_page', array('expired_date' => 'nulled'), $r['page_id']);

This function can also be used to delete / expire a row thus:

$reply = update_table('simple_page', array('expired_date' => mktime()), $r['page_id']);

The update_table_extra($table_name, $r, $where, $primary_key) Function

This function behaves in the same way as the above but allows you to add an extra where clause into the SQL.

$reply = update_table('simple_page', array('expired_date' => mktime()), "page_tags not like '%Public%Document%'", $r['page_id']);

This code expires a particular row in the simple_pages table but only if it hasn't been tagged as a public document.

The date_splitter('publication_date') Function

The interface code returns dates in dd/mm/yyyy format and this needs to be turned into a unix timestamp for storage (date columns should be set as int(20)).

This function takes a date in the $r hash and converts it into a timestamp. The function call is a little unusual in that the function doesn't return anything. So where you have a date in $r['publication_date'] call: 

date_spliter('publication_date');
$reply = update_table('my_pages', $r, $page_id);

The call above extracts the date from $r and resets the value with the corresponding timestamp: 

function date_splitter($key){

    global $r;

    $b = explode('/', $r[$key]);

    $ts = mktime(0,0,0, $b[1], $b[0], $b[2]);
    
    $r[$key] = $ts;
    
    //print_r($r);
    
    }

The get_options($reply, $value_column, $label_column) Function

This functions more properly belongs in the interface_functions.php file as it is related to form building. Its purpose is to populate <select/> form elements with a list of options.

It takes a reply hash from just_sql() as it's first argument, the second two parameters are the column names you want to map to the value and label of the option respectively. The function can be called like so: 

return get_options(just_sql("select * from panel_group where expired_date is null order by panel_group_name"), "panel_group_id", "panel_group_name");

The function is used extensively in option_switcher.php which determines what the option list should be for any <select/> elements in forms generated by build_form($skini_handle).

Tags: Lightweight PHP Web Application Framework

Posted by pj at March 30, 2009 04:05 PM

Comments