query help

[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:

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 ";
    

[kireol]

what does a PRO number look like and what does a tracking number look like?

 

If it were me, I would write in the PHP code to doa  check to see if what the user entered was a PRO or tracking number then mod my sql to look for that particular one

[Psycho]

You say that query currently pulls records based upon the entered PRO number, but that doesn't seem to be the case looking at your WHERE clause.

 

So, based upon your statement and the fact the query is so long, I would put the query into a funtion which will change the where clause to look for either PRO number or by tracking number. You could then call the query by PRO number, if ther is no result, then call it by tracking number.

 

the function

<?php
function search_query($field_value, $where_field='pro_no') {

   if ($where_field=='tracking_no') {
       $where_clause = "am_invoice_line_item.line_item_pro = ''";
   } else {
       $where_clause = "am_invoice.carrier_invoice_number = '$field_value'";
   }

   var $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 " . $where_clause;;
}

 

The process:

<?php
//$search_value is the value entered by the user


//first search for pro no
$result = mysql_query(search_query($search_value, 'pro_no'));

//if no results search by invoice no
if ($mysql_num_rows($result)==0) {
 $result = mysql_query(search_query($search_value, 'tracking_no'));
}

if ($mysql_num_rows($result)==0) {
 echo "No results found";
} else {

 //Display the found value(s)

}


?>

[smilesmita]

tracking number 1Z7301170232081682

pro number: 000695918 / 2007-05-12 23:18:43.147712

 

any idea?

[kireol]

what I would do is like I already said.  Look at the text and figure out which type was input.  then adjust my SQL accordingly.  MYSQL calls are costly in time. 

 

Doing it this way also allows you to do 2 things:

1)echo to your result page let the user know what type that was detected.

2) validate that what they did enter is in the correct format that you expect

 

tracking number 1Z7301170232081682

pro number: 000695918 / 2007-05-12 23:18:43.147712

 

 

if(strstr($search_value, "/")
{
     //user entered a pro number.  make SQL search on PRO number
}
else
{
    //user entred a tracking number.  make SQL search on tracking number
}