REQUESTED TABLE DEFINITIONS
Table "public.call_record"
Column | Type | Modifiers
-----------------+------------------------+---------------
cntrct_id | character varying(15) | not null
call_regard | text |
port_type | character varying(9) |
inst | text |
info_taken | character varying(40) |
log_date | date | not null
log_time | time without time zone | not null
act_taken | text |
use_material | text |
targ_pest | integer |
work_comp_by | text |
emp_no | integer |
comp_date | date |
job_start_time | time without time zone |
job_leave_time | time without time zone |
comp_val | boolean | default false
fti_call_regd | public.tsvector |
fti_inst | public.tsvector |
fti_act_take | public.tsvector |
route | character(3) |
act_port | text |
targ_pest_opt | text |
call_regard_opt | text |
targpest_other | text |
date_sched | date |
custord_num | integer |
dist_id | integer |
phone_slot | integer | default 0
Indexes:
"call_record_pkey" PRIMARY KEY, btree (cntrct_id, log_date, log_time)
"route_index" hash (route)
Check constraints:
"call_record_targ_pest_check" CHECK (targ_pest <= 100)
"call_record_targ_pest_check1" CHECK (targ_pest >= 0)
Table "public.per_call"
Column | Type | Modifiers
---------+----------------------+-----------
dist_id | character varying(2) |
route | character varying(2) |
type | character(1) |
total | integer |
I need to get data from 2 tables and print it in a single report. The report should look like this:
district | route | type | total | callbacks
| 01 | T | 12 | 5
| 02 | P | 0 | 0
| 03 | P | 3 | 1
2 | 01 | T | 4 | 1
| 02 | T | 1 | 0
| 03 | P | 0 | 0
etc... (this is theoretical sample data)
So, in essence I need to get the dist_id, route, type, and count(*) from the table per_call and the count of call_backs from the table call_record
PROBLEM: looping through tables makes it go glacially slow. How can I adjust the following PSQL query so that I don't have to loop and I can echo the tabular data properly?
Let me know if anything is opaque and I will try to clarify
echo '<table align="center" border = 2>
<th>DISTRICT</th>
<th>ROUTE</th>
<th>TYPE</th>
<th>TOTAL</th>
<th>CALL BACKS</th>';
$SQL = " SELECT per_call.dist_id, per_call.route, per_call.type, per_call.total
FROM per_call, call_record
WHERE TRUE ";
if($type == 'termite'){
$SQL = $SQL." AND per_call.type = 'T' ";
}
else{
$SQL = $SQL." AND per_call.type = 'P' ";
}
$SQL = $SQL." AND call_record.dist_id = per_call.dist_id
AND call_record.log_date >= '$startDate'
AND call_record.log_date <= '$endDate'
ORDER BY per_call.dist_id, per_call.route, per_call.type ASC ";
echo $SQL;
/*AND call_record.log_date = '$startDate'
AND call_record.log_date = '$endDate'*/
$Q = pg_query($connect,$SQL);
while($row = pg_fetch_row($Q)){
$dist = $row[0];
$route = $row[1];
$type = $row[2];
$total = $row[3];
echo '<tr>';
echo '<td align="center">'.$dist.'</td>';
echo '<td align="center">'.$route.'</td>';
echo '<td align="center">'.$type.'</td>';
echo '<td align="center">'.$total.'</td>';
$SQL2 = "SELECT COUNT(*)
FROM call_record
WHERE dist_id = $dist
AND route = '$route'
AND substring(cntrct_id from 2 for 1) = '$type'
AND substring(call_regard_opt from 2 for 1) = '1'
";
$Q2 = pg_query($connect,$SQL2);
$row2 = pg_fetch_row($Q2);
$callbacks = $row2[0];
echo '<td align="center">'.$callbacks.'</td>';
echo '</tr>';
}
echo "</table>";