query help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
smilesmita
Forum Commoner
Posts: 30
Joined: Thu May 24, 2007 1:52 pm

query help

Post by smilesmita »

ihave this page which helps you find an invoice...it is basically a search for details of invoice.you can search by multiple fields on that page.
following are the feilds listed:
PRO/Air Bill Number:
PRO/Airbill Number:
Carrier's Invoice Number :
Carrier Account Number:
Purchase Order Number :
Carrier Customer Cost Object:
Payment Date Pick Date on Calendar __ THROUGH __
Invoice Receipt Date
Pick Date on Calendar __ THROUGH __
Check Number

the first field PRO/Air bill number...currently the users put a pro number and hit search and they get result.wht we want is to be able to enter tracking number in that field and hit search.
so if a pro is entered the query will search by pro number while if i enter tracking number instead ..the query shud then search by tracking number.
so either ways the query shud search...may be first by pro number and if that search comes blank then it shud search by tracking number.
we are using that field to take 2 kinds of input-PRO number or tracking number.
this is the query which we are using right now which pulls the record by different search criterias and i am confused as to where i can modify to make the search critirea get the results which i am looking for:

Code: Select all

var $browse_query = "
        SELECT  am_client.client_name,
                am_invoice_line_item.batch_id,
                am_batch_status.status_text,
                am_invoice.invoice_date_received,
                am_payment_group.payment_group_create_date,
                am_invoice_line_item.payment_id AS check_number,
                am_payment.payment_date AS check_date,
                carrier.company_name AS carrier_company,
                am_invoice_line_item.line_item_pro,
                am_invoice_line_item.line_item_amount_billed,
                am_invoice_line_item.line_item_amount_paid,
                am_invoice_line_item_billing.ilib_amount,
                (NOT am_invoice_line_item_billing.ilib_amount =
                    am_invoice_line_item.line_item_amount_paid) AS split,
                am_short_pay.short_reason,
                am_invoice.carrier_invoice_number,
                billing.company_name AS billing_company,
                coalesce(am_purchase_order.po_number,
                    am_invoice_line_item.line_item_purchase_order) AS po_number,
                am_invoice_line_item.line_item_ardmore_invoice,
                am_invoice.invoice_id,
                am_invoice_line_item.line_item_id,
                am_invoice.carrier_account_number,
                am_invoice.invoice_problem_flag
           FROM am_invoice
                LEFT OUTER JOIN am_invoice_line_item USING ( invoice_id )
                LEFT OUTER JOIN am_invoice_line_item_billing USING ( line_item_id )
                LEFT OUTER JOIN fw_company carrier ON
                    ( carrier.company_id = am_invoice.carrier_id )
                LEFT OUTER JOIN am_invoice_batch USING ( batch_id )
                LEFT OUTER JOIN am_client using ( client_id )
                LEFT OUTER JOIN am_batch_status
                    ON ( am_batch_status.status_code =
                        am_invoice_batch.batch_status )
                LEFT OUTER JOIN fw_company billing ON ( billing.company_id =
                    am_invoice_line_item_billing.ilib_company_id )
                LEFT OUTER JOIN am_short_pay ON ( am_short_pay.short_code =
                    am_invoice_line_item.line_item_short_pay_reason )
                LEFT OUTER JOIN am_purchase_order ON ( am_purchase_order.po_id =
                    am_invoice_line_item.purchase_order_id )
                LEFT OUTER JOIN am_payment USING ( payment_id )
                LEFT OUTER JOIN am_payment_group USING ( payment_group_id )
            WHERE TRUE ";
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Re: query help

Post by anjanesh »

smilesmita wrote:the first field PRO/Air bill number...currently the users put a pro number and hit search and they get result.wht we want is to be able to enter tracking number in that field and hit search.
I dont understand how you get PRO results when the WHERE clause is empty (WHERE TRUE) ?
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

Why dont you use the pro/tracking number to query both columns in database within the same query?
smilesmita
Forum Commoner
Posts: 30
Joined: Thu May 24, 2007 1:52 pm

Re: query help

Post by smilesmita »

this class extends some other php program where theres is function whichc builds the where thingi..here it is

filter is the fields which we search by

Code: Select all

Function Build_Query($options = '', $browseQueryArrayIdx = null)
    {
        global $view;

        if (IsBlank($this->browse_query)) {
            $this->browse_query = $this->Build_Base_Query();
        }

        $use['filter'] = array();
        if ($view['filter']) {
            
            $use['filter'] = array_merge($use['filter'], $view['filter']);
            
        }
        if ($options['filter']) {
            $use['filter'] = array_merge($use['filter'], $options['filter'] );
        }

        $kwErrors = "";
        foreach ($use['filter'] as $fields => $term) {
            if (!IsBlank($term) && !isset($this->skip_fields[$fields])) {
                $val = $filter_field = "";
                if (is_array($term)) {
                    foreach($term AS $ff => $newterm) {
                        if ( $this->search_fields[$ff] == $fields ) {
                            $filter_field = $ff;
                            $val = $newterm;
                        }
                    }
                }
                if (!IsBlank($val) && $fields != 'submit') {
                    $or = '';
                    $current = "( ";
                    $field = explode (',',$fields);
                    foreach ($field as $k => $field_name) {
                        if(!$filter_field) {
                            $filter_field = $field_name;
                        }
                        $foptions = array();
                        if (isset($this->field_options[$filter_field])) {
                            $foptions = $this->field_options[$filter_field];
                        }
                        $f = Get_Field($filter_field, $foptions);
                        $current .= $or.$f->Get_Filter_SQL(
                            $field_name,
                            $f->Get_Filter_Value($val),
                            $kwErrors,
                            $f->Get_Not_Value("view[filter][$fields]")
                            );
                        $or = ' OR ';
                    }
                    $current .= " )";
                    $filters[] = $current;
                }
            }
        }

        if (!isBlank($kwErrors)) {
            // Set $view['filter']['submit'] to empty so that we don't get
            // ourselves into an infinite redirect loop.
            $view['filter']['submit'] = '';
            Abort_Post($kwErrors);
        }

        if ($options['sql_filters']) {
            if (is_array($filters)) {
                $filters = array_merge($filters, $options['sql_filters']);
               
            } else {
                $filters = $options['sql_filters'];
            }
        }

        if (!empty($joins) || !empty($filters)) {
            if (stristr($this->browse_query,'WHERE')) {
                $this->browse_query .= " AND ";
            } else {
                $this->browse_query .= " WHERE ";
            }

            if (!empty($joins)) {
                $this->browse_query .= join (' AND ', $joins);
            }
            if (!empty($joins) && !empty($filters)) {
                $this->browse_query .= " AND ";
            }
            if (!empty($filters)) {
                $this->browse_query .= join (' AND ', $filters);
              echo "<pre>";
              print_r($filters);
              echo "</pre>";
            }
        }
        // show ($this->browse_query); // DEBUG
    }
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Also check out ORDER BY CASE if you want to sort the results

Code: Select all

SELECT ...
WHERE
`col1` = $PRO_Number OR
`col2` = $Tracking_Number
ORDER BY
CASE
WHEN `col2` = $PRO_Number THEN 1
WHEN `col1` = $Tracking_Number THEN 1000
ELSE 500
END
mabwi
Forum Commoner
Posts: 27
Joined: Wed Aug 01, 2007 4:51 pm

Post by mabwi »

Are the two types of numbers significantly different, in terms of number of digits or something?

If so, could you do the query based on which type of number was entered? This would also allow you to check if the entered number was completely invalid, and save a query.
Post Reply