Here is the select statement used in the report.
Code: Select all
// Logic for Notice Letters
$unit_fk = $formValue['vio_unit_id'];
$rules_fk = $formValue['vio_rules_id'];
// Create dataTable
nuRunQuery(
<<<EOSQL
CREATE TABLE #dataTable#
SELECT
violations.violations_id,
violations.vio_unit_id,
violations.vio_rules_id,
unit.unit_id,
unit.unit_building_unit,
violations.vio_violation_number,
violations.vio_printable_information,
people.people_id,
people.peo_type,
people.peo_building_unit,
people.peo_name,
people.peo_company,
people.peo_address_1,
people.peo_city,
people.peo_state,
people.peo_zip,
rules.rules_id,
rules.rule_key_phrase,
rules.rule_section,
rules.rule_frequency,
letter.let_frequency,
letter.let_notice_number,
letter.let_base,
DATE_FORMAT(CURDATE(),'%M %d, %Y') AS today_date
FROM violations
INNER JOIN (unit, rules, people, letter)
ON (
unit.unit_id = violations.vio_unit_id AND
rules.rules_id = violations.vio_rules_id AND
people.peo_building_unit = unit.unit_building_unit AND
letter.let_frequency = rules.rule_frequency)
WHERE violations.vio_unit_id = '$unit_fk'
AND violations.vio_rules_id = '$rules_fk'
AND let_notice_number = violations.vio_violation_number
EOSQL
);
I took the same select statement, hard coded the foreign keys, ‘vio_unit_id’ and ‘vio_rules_id’ to match the same violation and hard coded the ‘notice number’. I ran the query five times in phpMyAdmin and each time changed ‘let_notice_number’ from 1 to 5. Each time the query took about 2 seconds to return the expected results.
What I don’t understand is why the query runs fine in phpMyAdmin but when I run it through the Report Writer, it only works when I select letters ‘1’ or ‘2’.
I have tried a number of different things to try to determine why there is this problem. I have tried ‘inner joins’ and ‘left joins’ and get the same results. I have checked the data in the tables and don't see any problems. I can use the same violation for different units and get different results. For example, I have 7 units with the violation of ‘windows – screens’. Six of the units cannot generate a letter with a notice number of ‘2’ but the seventh unit has no problems with any notice letter for this violation. I have a unit that has 4 different violations. One violation has no problems with generating any of the 5 letters. Of the remaining 3 violations, 2 of the violations have problems with notice numbers (letters) ‘3’ and ‘4’ while the last one has problems with ‘3’, ‘4’ and ‘5’.
There seems to be no pattern to which letters work and which ones don’t. I also don’t understand why the same query works in phpMyAdmin but it has problems with some letters in the Report Writer.
I am looking for suggestions on how I can debug this problem.
Thanks,
John