How to handle IF statements for every field in a table?

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

How to handle IF statements for every field in a table?

Post by thinsoldier »

How to handle an IF statement for every field in a table?

Because every client I've used this code for over the years wanted to play fast and loose with the values in most DB fields I've had to allow all kinds of values in most fields. For example there are a number of fields where you'd assume you can only enter an integer. But most clients want to enter short phrases or whole paragraphs.

So on many fields I have to test if the value is numeric or a string.
Display strings as-is but run numbers through number_format and add a dollar sign.
Some numbers just need to be run through number_format without the dollar sign.
Or run it through a square foot to acre converter and add 'acres' or "sq ft" suffix.
Or test if the value is a csv of strings and put a space between each comma.
Or if it's a csv of id numbers pull the related records from the database.
Or if it's a multi-line string convert it to an unordered list on some pages and a comma-space separated list on other pages.
If the value is zero, don't show a zero.
Some values are a zero or one and should be shown as yes or no.
Some values are y or n and should be shown as yes or no.

So what I've had to do is set up a bunch of IF statements for each field. The next problem was that there are many sections on the site where many of these fields would be displayed. So what I did next was to turn each field-specific IF statement into its own function. Later I grouped all those functions into a helper class.

Below is that helper class and an example of its usage.
Does this seem like a good idea? Is there a better way I could be handling this?


Usage:

Code: Select all

<?
$model = new ListingsModel(15671);
$ldh = new ListingDisplayHelper( $model );
$F = $ldh;
?>
<tr><td>Property Name</td><td><?=$F->property_name?></td></tr>
<tr><td>Categories</td><td><?=$F->category?></td></tr>
<tr><td>Sale Price</td><td><?=$F->price?></td></tr>
<tr><td>Rent Price</td><td><?=$F->rent_price?></td></tr>
<tr><td>List Date</td><td><?=$F->list_date?></td></tr>
<tr><td>Island</td><td><?=$F->island?></td></tr>
<tr><td>Location</td><td><?=$F->location;?></td></tr>
<tr><td>District</td><td><?=$F->district?></td></tr>
<tr><td>Property Types</td><td><?=$F->property_types?></td></tr>
<tr><td>Key Features</td><td><?=$F->key_features?></td></tr>
<tr><td>Building_style</td><td><?=$F->building_style?></td></tr>
<tr><td>Bedrooms</td><td><?=$F->bedrooms?></td></tr>
<tr><td>Bathrooms</td><td><?=$F->bathrooms?></td></tr>
<tr><td>Half Baths</td><td><?=$F->halfbaths?></td></tr>
<tr><td>Lot Size</td><td><?=$F->lot_size?></td></tr>
<tr><td>Living Area</td><td><?=$F->living_area?></td></tr>
<tr><td>NRA</td><td><?=$F->net_rentable?></td></tr>
<tr><td>GRA</td><td><?=$F->gross_rentable_area?></td></tr>
<tr><td>Land Price/Sq.Ft.</td><td><?=$F->price_sq_ft?></td></tr>
<tr><td>Building Rental Rate/Sq.Ft.</td><td><?=$F->sq_ft_rate?></td></tr>
<tr><td>Total Expenses</td><td><?=$F->total_expenses?></td></tr>
<tr><td>Vacancy Rate</td><td><?=$F->vacancy_rate?></td></tr>
Helper Class:

Code: Select all

my 2nd code block keeps disappearing. will add in reply.
 
Warning: I have no idea what I'm talking about.
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: How to handle IF statements for every field in a table?

Post by thinsoldier »

I give up. I put the class on pastebin instead.

http://pastebin.com/gqRzsr7G


Code: Select all

<?php

## This class should only be concerned with massaging
## the main listing values into preferred usable text values 
## most useful in most common situations!

## ListingDisplayHelper requires a listing model object or stdclass or array of db record fields be passed into it.

## MAGIC METHODS:
### 1. Any property requested that exists on the root of the object itself is immediately returned.
### 2. The end result of any value tweaking methods is saved in ->cooked. So already cooked values are returned if found.
### 3. If a method matching the requested property is found that method cooks the raw value and caches it in ->cooked.
###    It is also up to that method to decide what to do if the value is zero.
### 4. Any property requested that doesn't have an associated method will be pulled from ->raw.
###
### Any value that roughly equates to zero 0 will be converted to empty string.
### Things that don't actually exist in the db table will also go into ->cooked.


class ListingDisplayHelper 
{

public $_cooked; // adjusted values go in here
public $_raw; // original values
public $_log;
public $_logging = false;

// Some fields should never show zero even if that's their value.
// But for debugging you can have them show another value (like - or !) 
// to make them easier to spot
public $_zero_placeholder = '';




function __construct( $listing )
{
	$this->cooked = new stdclass; 
	
	if( is_a( $listing, 'ListingsModel') ) 
	{
		$this->raw = (object) $listing->rowdata;  
		$this->model = $listing;
	}
	elseif( is_array($listing) ) 
	{
	 $this->raw = (object) $listing;
	}
	elseif( is_object($listing) )
	{
		$this->raw = $listing;
	}
}


# See MAGIC METHODS comment at top.
public function __get($name)
{
	if( false )
	{ $nothing; }
	
	// check for top level class vars
	elseif(  property_exists( $this, $name) )
	{
		$this->_log("found on obj   `$name`");
		$to_return = $this->$name;
	}
	
	// check for vars that were already cooked (cached)
	elseif (  property_exists($this->cooked, $name) ) 
	{
		$this->_log("found in cooked `$name`" );
		$to_return = $this->cooked->$name;
	}
	
	// check for a matching method that would cook the vars value and cache it in ->cooked.
	elseif( method_exists($this, 'get_'.$name) )
	{
		$method = 'get_'.$name;
		$this->_log("method exists   `$name`");
		$to_return = $this->$method( $name );
	}

	// check for the raw value
	elseif (  property_exists($this->raw, $name) ) 
	{
		$this->_log("found in raw     `$name`" );
		$to_return = $this->raw->$name;
	}
	else {
		$this->_log("!!!!!! $name not found anywhere !!!!!!!");
		$to_return = "!!!!!! $name not found anywhere !!!!!!!";
		//$to_return = null;
	}
	
	if( (string) $to_return == '0' || $to_return == '0000-00-00' )
	{ $to_return = $this->_zero_placeholder; }

	return $to_return;
	
}

private function _log($msg)
{
	if($this->_logging) {
	$this->_log[] = $msg; }
}



public function get_island( $name )
{
	return $this->cooked->island = $this->model->island();
}


public function get_location( $name )
{
	return $this->cooked->location = $this->model->location();
}

public function get_district( $name )
{
	return $this->cooked->district = $this->model->district();
}


public function get_property_types( $name )
{
	$typeArray = $this->model->propertyTypes();
	return $this->cooked->property_types = implode(', ', $typeArray);
}

public function get_view_types( $name )
{
	$typeArray = $this->model->viewTypes();
	return $this->cooked->view_types = implode(', ', $typeArray);
}

public function get_key_features( $name )
{
	return $this->get_view_types ($name);
}

public function get_amenities( $name )
{
	$typeArray = $this->model->amenities();
	return $this->cooked->amenities = implode(', ', $typeArray);
}


public function get_visible_on_site()
{
	return $this->cooked->visible_on_site = ($this->raw->hide === 'no') ? 'yes' : 'no';
}

public function get_under_mls_control()
{
	return $this->cooked->under_mls_control = ($this->raw->mls_imported === 'y') ? 'yes' : 'no';
}





function get_youtube_ids()
{
	if(!empty( $this->raw->youtube_ids ))
	{ 
		$ytstring = $this->raw->youtube_ids;
		$ytstring = str_replace("\r\n", "\n", $ytstring);
		$ytstring = str_replace("\r", "\n", $ytstring);
		return $this->cooked->youtube_ids = $ytstring;
	}
}

function get_youtube_ids_array()
{
	return $this->cooked->youtube_ids_array = explode("\n", $this->get_youtube_ids() );
}

function get_youtube_ids_linked()
{
	$array = $this->get_youtube_ids_array();
	if(!empty($array))
	{
		$url = '<a href="https://www.youtube.com/watch?v=%s">%s</a>';
		foreach($array as $ytid)
		{
			$out[] = sprintf($url, $ytid, $ytid);
		}
		return $this->cooked->youtube_ids_linked = implode(', ', $out);
	}
}


// number of photos for this listing on local server
public function get_local_photo_count()
{
	$sql = "SELECT COUNT(*) AS `count` FROM media WHERE parent={$this->raw->listing_id} AND section='listings' 
	AND (type='photo' OR type='cropA' OR type='cropB') GROUP BY parent";
	$result = sqlQuery($sql);
	if(!empty($result)) {
	return $this->cooked->local_photo_count = $result[0]['count']; }
}


public function get_category()
{
	return $this->cooked->category =  ucwords(str_replace(',' , ', ', $this->raw->category));
}


function get_price( $name )
{
	return $this->get_sale_price( $name );
}


function get_sale_price( $name )
{
	return $this->_cashmoney( $name );
}


function get_rent_price( $name )
{
	return $this->_cashmoney( $name );
}



function get_land_price_sq_ft( $name )
{ return $this->get_price_sq_ft( $name ); }

function get_price_sq_ft( $name )
{
	return $this->_cashmoney( $name );
}


function get_building_sq_ft_rate( $name )
{
	return $this->get_sq_ft_rate( $name );
}

function get_sq_ft_rate( $name )
{
	return $this->_cashmoney( $name );
}


function get_total_expenses( $name )
{
	return $this->_cashmoney( $name );
}

function get_vacancy_rate( $name )
{
	return $this->_cashmoney( $name );
}


function get_goi( $name )
{
	return $this->_numeric( $name );
}

function get_noi( $name )
{
	return $this->_numeric( $name );
}

function get_insurance( $name )
{
	return $this->_numeric( $name );
}


function get_assoc_fees( $name )
{
	return $this->_numeric( $name );
}

function get_maint_fees( $name )
{
	return $this->_numeric( $name );
}

function get_rent_deposit( $name )
{
	return $this->_numeric( $name );
}

function get_security_deposit( $name )
{
	return $this->_numeric( $name );
}

function get_phone_deposit( $name )
{
	return $this->_numeric( $name );
}

function get_water_deposit( $name )
{
	return $this->_numeric( $name );
}

function get_power_deposit( $name )
{
	return $this->_numeric( 'bec_deposit' );
}

function get_bec_deposit( $name )
{
	return $this->_numeric( $name );
}


/**
* For things that are probably a number but might not be. 
* In the negative case return the original string.
*/
private function _numeric( $name ) 
{
	if(is_numeric($this->raw->$name)){ return $this->_cashmoney( $name ); }
	else { return $this->cooked->$name = $this->raw->$name; }
}


// Damned `RPTax` is the only camel cased table field.
function get_rptax( $name )
{
	$name = 'rptax'; // normalize the name
	$value = $this->raw->rptax = $this->raw->RPTax; // the lower case name needs to exist in raw for _cashmoney to work
	$this->cooked->$name = $value;
	if(is_numeric( $value )){ return $this->_cashmoney( $name ); }
	else { return $this->cooked->$name; }
}




function get_land_sq_ft()
{
	return $this->get_lot_size();
}

function get_lot_size()
{
	$size = $this->_sqftacre($this->raw->lot_size);
	$this->cooked->land_sq_ft = $size;
	$this->cooked->lot_size = $size;
	return $size;
}


function _sqftacre( $input )
{
	if(empty($input)){ return ''; }
	$out = strtolower(sqftacre( $input ));
	$out = explode(' ', $out);
	if( $out[1] == 'acres' || $out[1] == 'acre'){ return implode(' ', $out); }
	else {return $out[0].' sq.ft.';}
}

function get_living_area()
{
	return $this->_squarefeet('living_area');
	# Sizes of building internals should never be represented in acres.
}

function get_gross_building()
{
	return $this->_squarefeet('gross_building');
	# Sizes of building internals should never be represented in acres.
}

function get_building_area()
{
	return $this->get_gross_building();
}


function get_net_rentable()
{
	return $this->_squarefeet('net_rentable');
	# Sizes of building internals should never be represented in acres.
}

function get_nra()
{
	return $this->get_net_rentable();
}


function get_gross_rentable_area()
{
	return $this->_squarefeet('gross_rentable_area');
	// anything building related should always be in square feet.
}

function get_gra()
{
	return $this->get_gross_rentable_area();
}

private function _squarefeet( $var )
{
	if( empty($this->raw->$var) ){ return ''; }
	return $this->cooked->$var = number_format($this->raw->$var).' sq.ft.';
}

// For when you're sure something is money and should show a $ and commas.
// In the negative case show nothing.
private function _cashmoney( $var )
{
	if( 0 == (int) $this->raw->$var ) { $this->cooked->$var = ''; }
	else { $this->cooked->$var = '$'. number_format($this->raw->$var); }

	return $this->cooked->$var;
}


public function get_view_count()
{
	$sql = "SELECT totalhits FROM listings_hits WHERE listing_id = {$this->raw->listing_id}";
	$count = sqlQuery($sql);
	if( !empty($count) ) { return $this->cooked->view_count = $count[0]['totalhits']; }
}


public function get_bedrooms()
{
	if($this->raw->bedrooms  == 0) { return $this->cooked->bedrooms = null; }
	else { return $this->cooked->bedrooms = $this->raw->bedrooms; }
}

public function get_bathrooms()
{
	$bathrooms = (float) $this->raw->bathrooms;

	if($bathrooms == 0)	{ $bathrooms = null; }

	if(strpos($bathrooms, '.5') !== false)
	{ 
		$bathrooms = str_replace('.5', "&frac12;", $bathrooms); 
	}
	elseif( (int)$this->raw->halfbaths > 0 && $bathrooms > 0)
	{
		$bathrooms .= "&frac12;";
	}
	return $this->cooked->bathrooms = $bathrooms;
}


}
?>
Warning: I have no idea what I'm talking about.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to handle IF statements for every field in a table?

Post by califdon »

I would say the biggest part of your problem is that your database schema doesn't conform to the rules for a normalized relational database. True, you can make almost anything work if you're really determined to make up for a flawed initial plan (you can drive a nail with a pipe wrench, too), but if you're at all serious about using a relational database, you simply cannot allow multiple values in a field, for example. The SQL language just wasn't designed to handle that. I've heard the excuse that "the users insist on entering data that way" over and over, but in nearly every case that's not the basic problem, it's the design of the database and the user interface. It would take far more space than a forum post to even begin to teach how a relational database is properly designed, but there are numerous tutorials online, as well as several books.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How to handle IF statements for every field in a table?

Post by Christopher »

califdon wrote:but if you're at all serious about using a relational database, you simply cannot allow multiple values in a field, for example. The SQL language just wasn't designed to handle that.
I'd agree with Don if these are fields that you query on they should not have multiple formats. However, if they just hold data that comes in from records selected using other fields -- then you can store what you like in them (and deal with the consequences).

Have you thought of storing the data in these fields with a format value. For example, you could store them in JSON as:

['format':'money', 'value':'123.00']
or
['format':'string', 'value':'Hello world']
or
['format':'csv', 'value':'"red","green","blue","yellow"'].

Then you could have a formatting function like:

Code: Select all

function format($data) {
    if (substr($data, 0, 1) == '[')) {
        $data = json_decode($data);
        switch ($data['format']) {
        case 'money':
            return money_format('%i', $data['value']);
            break;
        case 'string':
            return $data['value'];
            break;
        // other formats
        }
    }
    return $data;
}
If you don't like JSON you could serialize PHP or use your own compact format like '=m:123.00' or '=s:Hello world' or '=c:"red","green","blue","yellow"'.

You'd need to do a one time conversion of the existing data, and modify the code that save these values to the database.
(#10850)
thinsoldier
Forum Contributor
Posts: 367
Joined: Fri Jul 20, 2007 11:29 am
Contact:

Re: How to handle IF statements for every field in a table?

Post by thinsoldier »

califdon wrote:I would say the biggest part of your problem is that your database schema doesn't conform to the rules for a normalized relational database. True, you can make almost anything work if you're really determined to make up for a flawed initial plan (you can drive a nail with a pipe wrench, too), but if you're at all serious about using a relational database, you simply cannot allow multiple values in a field, for example. The SQL language just wasn't designed to handle that. I've heard the excuse that "the users insist on entering data that way" over and over, but in nearly every case that's not the basic problem, it's the design of the database and the user interface. It would take far more space than a forum post to even begin to teach how a relational database is properly designed, but there are numerous tutorials online, as well as several books.
Most of the nasty fields aren't being queried on. Just shown. The few that are queried on actually have a proper normalized cross-reference table. But a csv of that data is also saved in the main table because various old templates (that nobody wants to pay to update) expect to find a csv on that same data. Saves the trouble of updating those to do another query of the cross reference table to generate that csv (which gets passed into other functions that generate <select> or other inputs).

So for searching there are proper cross reference tables and join queries. I'm more concerned about sanely displaying the other 90 fields that need to change their formatting slightly or greatly depending on what kind of data is found in them. Repeat. The nasty CSV fields all have proper cross reference fields which are used for searching. My real question is about all the other fields.
Christopher wrote:Have you thought of storing the data in these fields with a format value. For example, you could store them in JSON as:

['format':'money', 'value':'123.00']
or
['format':'string', 'value':'Hello world']
This assumes I can convince the clients to enforce rules among their employees. Sure I could and have had validation that enforced integers only on certain money fields. But with every client they eventually forward an e-mail from multiple employees asking that the field allow short phrases like "$123 US if they pay US but 3% discount from Gov. Tax if they pay with local currency". For the handful of brand new clients who've never had a web-app it's all super strict. But the vast majority of clients come to us with decade old data with no desire to change the way they work. And their old systems allowed this crazy flexibility to put data in but did not even attempt to prettify the data when showing it.

So my problem/question is all about cosmetics.
When/if a pure number is discovered in certain fields, I must format it as "$1,254.00"
for other fields I must format it as "1,254 sq.ft."
for others I must format it as "0.028 acres".
If it isn't a pure number, run it through markdown and output paragraphs.
For 2 fields I must regex search the string for any whole number and turn just the numbers into <a href=remote.com/1254/video/ target=_blank>
Etc.

And I'm looking for a way to have all these IF/Then statements packaged up in a re-usable way. Because currently when a change or addition related to this stuff is requested I have to tweak dozens of locations in dozens of template files.
Warning: I have no idea what I'm talking about.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: How to handle IF statements for every field in a table?

Post by califdon »

So you're saying that these fields are purely descriptive and users may enter almost anything they please? If that's the case, there's really nothing you can do, in my view. They must be advised that they may be unable to query against these fields and they may be unable to use such "values" in calculations. The other thought that occurs to me is, in the case of units of measure, could you in each case have an input field for a pure numeric and a dropdown selection box for the U/M, defaulting to the most commonly used U/M?

Christopher's suggestion of using a formatted storage also sounds to me like it might help your tangled If statements problem.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: How to handle IF statements for every field in a table?

Post by Christopher »

thinsoldier wrote:And I'm looking for a way to have all these IF/Then statements packaged up in a re-usable way. Because currently when a change or addition related to this stuff is requested I have to tweak dozens of locations in dozens of template files.
I was assuming that you control when the data is saved into the database -- so you could check the input and do any filtering necessary -- then wrap the data with formatting information. How you store data should be transparent to the user, so do a one time conversion and then your code enforces the formatting.

As far as how to handle this in a reusable way instead of if()'s, I would recommend a Filter Chain that you can add custom filters to on a field by field basis.

PS - This kind of flexible input is actually the kind of thing we are supposed to be doing: namely making computers adapt the people, not making people adapt to computers!
(#10850)
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

Re: How to handle IF statements for every field in a table?

Post by Weiry »

I tend to agree with Christopher on this.

I think that JSON would probably be one of the best approaches to achieving this as long as you have your database fields for referencing the information.

You would probably have a separate class solely for the processing of that data (maybe even a static class methods).
I have recently had to work in a VERY similar situation when a user tries to create a survey with logic, there was no way to identify exactly what the user wanted to enter for fields or values and we also had no way of knowing what the end-user would also enter as their result to the question.

Storing the entire survey in JSON allows you basically endless customization as you store all the information you require inside the JSON, formatted correctly of course, and then simply allow your helper classes or javascript to process exactly what is inside the JSON (this would be your if statements etc).

For example, here is (approximately) what we started with for a survey structure.
(Forgive the poor formatting, NetBeans' autoformatter sucks with JSON)

Code: Select all

[{
	"id":"68bb44f2-32ea-43e5-46ae-5ff228b7efde",
	"title":"A brand new survey",
	"questions":[{
		"id":"5e3f38f9-fb56-fa68-8d09-7fb560661d0",
		"question":"Multi-choice question: What fruit(s) do you like?",
		"type":2,
		"choices":[{
			"code":"Banana",
			"display":"Banana"
		},{
			"code":"Apple",
			"display":"Apple"
		},{
			"code":"Orange",
			"display":"Orange"
		},{
			"code":"Pear",
			"display":"Pear"
		},{
			"code":"Lemon",
			"display":"Lemon"
		},{
			"code":"Plum",
			"display":"Plum"
		}]
	},{
		"id":"da18fcd8-a3e8-a0b-e52f-93dbf4227022",
		"question":"This is a plain text question.",
		"type":0,
		"choices":[{
			"code":""
		}]
	},{
		"id":"c07a70fc-79cb-e75b-a7d6-6b37766aec88",
		"question":"On a scale of 1 to 5, how great is a slider?",
		"type":3,
		"choices":[{
			"code":"1"
		},{
			"code":"5"
		},{
			"code":"1"
		}]
	}]
}]
In that JSON example above, we process the "type" field to be a different kind of item, namely 0- Free Text, 2- Multiple Choice, 3- Step Slider and output the relevant html equivalent of each input.

I would imagine that if you could combine that sort of storage with Christopher's suggestions, then i think you would get pretty close to a solution :)

cheers

Weiry.
Post Reply