From SQL Script to Zend Db Query

Yesterday, I have debugged an AJAX request that gets a value from the database. It was actually me who developed that module and all calculations and SQL query are based on the specifications given by our analyst.

The query was to get the charge (money value but integer only) from the database. It was based on the current user, plus the revise date, plus some other parameters but is very complex to be implemented in Zend_Db or Zend_Db_Table. The original SQL query was not correct, but as I understand the logic, I modified it right away. Here is it:

select top 1
	busyo_cd,
	work_com_cd,
	tariff,
	weight,
	work_charge,
	convert(varchar (20), revise_day, 111) as revise_date
from
	t_tariff2
where
	busyo_cd = @busyo
	and work_com_cd = @workcom
	and tariff = @tariff
	and weight
		between
			@weight
		and
			(
				select
					min(weight) as min_weight
				from
					t_tariff2
				where
					weight >= @weight
					and busyo_cd = @busyo
					and work_com_cd = @workcom
					and tariff = @tariff
					and revise_day <= @d
			)
	and revise_day <= @d
order by
	revise_day DESC;
&#91;/sourcecode&#93;

It will get the Work_Charge which was based on when the transaction occurred since charges differs on dates. It also differs on the user who   processes the transaction and of course the other parameters such as weight and tariff.

As you can see, the query so complex that it would be hard for me to create a Zend_Db equivalent. Instead, I use the plain SQL statement and build it and pass to Zend_Db. Here it is:

&#91;sourcecode language='php'&#93;
	/**
	 * Gets charge from tariff2 table
	 * Conditions are either for Weight or M3 field
	 * 
	 * @param $field
	 * @param $fieldData
	 * @return array $data | false
	 */
	protected function _getCharge($field, array $fieldData)
	{
		$db = $this->_getDb();
		$sql = '
				select top 1
					Work_Charge
				from
					T_Tariff2
				where
					' . $db->quoteInto('Busyo_Cd = ?', $fieldData['Busyo_Cd']) . '
					and ' . $db->quoteInto('Work_Com_Cd = ?', $fieldData['Work_Com_Cd']) . '
					and ' . $db->quoteInto('Tariff = ?', $fieldData['Tariff']) . '
					and ' . $field . '
						Between
							' . $fieldData[$field] . '
						and
							(
								Select
									MIN(' . $field . ') as Min_Val
								from
									T_Tariff2
								where
									' . $db->quoteInto($field . ' >= ?', $fieldData[$field]) . '
									and ' . $db->quoteInto('Busyo_Cd = ?', $fieldData['Busyo_Cd']) . '
									and ' . $db->quoteInto('Work_Com_Cd = ?', $fieldData['Work_Com_Cd']) . '
									and ' . $db->quoteInto('Tariff = ?', $fieldData['Tariff']) . '
									and ' . $db->quoteInto('Revise_Day <= ?', $fieldData&#91;'Revise_Day'&#93;) . '
							)
					and ' . $db->quoteInto('Revise_Day <= ?', $fieldData&#91;'Revise_Day'&#93;) . '
				order by
					Revise_Day DESC;
			';
		$row = $db->fetchRow($sql);
		if ($row)
		{
			return $row['Work_Charge'];
		}
		return false;
	}

For SQL Injection protection, I used quoteInto() method of Zend_Db to properly quote values before finally executing the query. It works great!