Currently, I have a PHP script that displays an invoice from an ODBC data source. The invoice displays fine, but I think I can optimize my query a little better. I have 3 parts to the invoice: header, detail lines, and a footer. When I was writing the code initially, I found that I had to query the db twice to get all the information I needed. I know there must be a way to query the db once.
Basically, I query the db, assign variables to all the rows, and print out the invoice header. The I found that when I tried to display the details (i.e. line items of the invoice) I had to do another query in a loop. Because of server load on the datasource, and other factors, this can make the query twice as long, since it is asking the datasource for the same information twice, before it gets displayed.
How can I only use one query, and get all the needed information into the detail lines inside the loop from the first query?
Here's a little snipet of code to show an example:
$conn = odbc_connect("DSN=dsn_informix", "", "");
// DB Select
$cur = odbc_exec ($conn, "SELECT DISTINCT invhead.in_cust, invhead.in_num, invhead.in_po, invhead.in_job,
invhead.in_date, invhead.in_sales, invhead.in_mat, invhead.in_lab,
invhead.in_frt, invhead.in_tax, invhead.in_amt, invhead.in_name,
invhead.in_addr, invhead.in_addr2, invhead.in_city, invhead.in_state,
invhead.in_zip, invhead.in_cntry, invhead.in_bid, invhead.in_bname,
invhead.in_baddr, invhead.in_badd2, invhead.in_bcity, invhead.in_bstat,
invhead.in_bzip, invhead.in_bcntr, invhead.in_via, invhead.in_porc,
invhead.in_com1, invhead.in_com2,
invline.il_line, invline.il_qshi, invline.il_desc, invline.il_unpr,
invline.il_extp,
termcode.trm_desc
FROM
covmgr.invhead invhead,
covmgr.invline invline,
covmgr.termcode termcode
WHERE
invhead.in_cmpy = invline.il_cmpy AND
invhead.in_cust = invline.il_cust AND
invhead.in_num = invline.il_num AND
invhead.in_term = termcode.trm_code AND
invhead.in_num = 64805
");
/ create a loop, and store results in variables /
while (odbc_fetch_row($cur)){
$in_cust = odbc_result($cur, "in_cust");
$in_num = odbc_result($cur, "in_num");
...
...
}
Then display the header info, then I use the same sql statement again, to display the details of the invoice.
Thanks for any insight on how to do this better.
