Why does SQL work differently between phpMyAdmin and report?
Posted: Wed Jul 10, 2013 6:10 am
I need some help trying to figure out why a SELECT statement works differently in phpMyAdmin than it does in the NuBuilder Report Writer. I have a property management application where the user enters information about property violations. For example, the tenant may have broken a window and needs to get it fixed. Based on the information in the violations table, a SELECT statement is used to get information from the violations table and 4 other tables that are joined in the query. The results of the query are put into #dataTable# and a letter is created to be sent to the tenant with information about the violation. The same violation can generate up to five letters with each letter becoming more serious about the violation. I have a ‘letter’ table that contains five different versions of the letter for that violation. (The letter table has a total of 15 different letters. Violations are divided into 3 categories with 5 letters for each category.) Based on the “notice number” the proper letter is selected from the table. (‘Notice number’ and ‘letter number’ are the same thing.)
Here is the select statement used in the report.
In order to do my testing, I commented out any additional code in the report. I ran a test in the report writer where the only thing I changed was the ‘notice number’. When I used ‘notice number’ ‘1’ or ‘2’, the letter was generated in about 20 seconds. When I used ‘notice numbers’ ‘3’, ‘4’ or ‘5 ‘, the letter was never generated. It either ran out of memory or I cancelled it after about 5 minutes.
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
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