Announcement

Collapse
No announcement yet.

Check of existing dataset

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Check of existing dataset

    I'm stucked here becouse it's not so clear which method to use to be sure that it works.

    This is the echoes and what is happening :
    SQL SELECT paymentID FROM payments WHERE InvoiceID = 1 AND invoiceDate = 2013-01-15 AND customerID = 1 // Error while accessing database
    I did check on DB and the record is not present . So I would like to go to INSERT routine.

    $sql_check_payment = "SELECT paymentID FROM payments
    WHERE InvoiceID = [glo_invoiceNr] AND invoiceDate = [glo_invoiceDate] AND customerID = [glo_customerID]";
    sc_lookup(check_payment, $sql_check_payment);
    ECHO " check_payment " . {check_payment[0][0]} . " SQL " . $sql_check_payment ;
    //" This is the result of SQL ->

    if (false == {check_payment})
    {
    ECHO " // Error while accessing database";
    }
    elseif ({check_payment}->EOF)
    {
    Echo " HERE I WILL DO INSERT routine" ;
    }
    else
    {
    Echo " HERE I WILL DO UPDATE routine" ;
    }
    }
    elseif ([yes_no] == 2)
    {
    [yes_no]=0;
    echo " DO NOT MAKE NOTHING ";
    }


    Where is mistake ?? How is the right way to make this check ? Everytime is a nightmare for me.
    Bye

  • #2
    Sorry I forgot to insert the error I got.


    ERROR
    Undefined offset: 0

    Script: C:Program Files (x86)Scriptcase 7_008wwwrootscriptcaseappZ_ASSOCIA TION orm_invoices orm_invoices_apl.php (1651)

    1651 --> ECHO " check_payment " . $this->check_payment[0][0] . " SQL " . $sql_check_payment ;

    Comment


    • #3
      The reason is most likely that your query did not return results. You can check that with the php function isset. If you go to an event like onvalidate you have code snippets on your right. Pick the select and you will find a snippet with an example how to check the result before accessing it.
      Albert Drent
      aducom software netherlands
      scriptcase partner, reseller, support and (turn-key) development
      www.scriptcase.eu / www.scriptcase.nl

      Comment


      • #4
        Thanks,
        I did it yet what you suggested . Yes the select do not find any record so I'm sure that I can use the following code. The problem is that strange error.

        $sql_inser_payment = "INSERT INTO payments
        (memberID, payment_cbID, payment_timingID, payment_casusal_ID, payment_ledgerID, paymentDate, paydescr, amount_in,
        currency, exchange_rate, note, cashin_type, projectID, invoiceID, invoiceDate, customerID,
        amount_vat_escl, amount_vat_incl, amount_vat)
        VALUES ([glo_employeeID], 1, 1, 2, 1, '[glo_invoiceDate]', '[payment_text]', [glo_Invoice_Vat_incl],
        [glo_currencyID], [glo_exchange_rate], '[customer_name]', '19', [glo_projectID], [glo_invoiceNr],
        '[glo_invoiceDate]', [glo_customerID], [glo_Invoice_Vat_escl], [glo_Invoice_Vat_incl], [glo_tot_vat])";
        sc_exec_sql($sql_inser_payment);

        Comment


        • #5
          If you test the pointer for existence then you won't have this 'strange' error. You where accessing a non-assigned array element due to the fact that the result was nil. So actually the errormessage is correct.

          so:
          Code:
          if (isset(your array element)) 
           {
           -- do your access here
           }
          else
           {
            --- nothing found.
           }
          Now you have a new issue with your insert yes? Go to application and set the debug mode on. It will preview each sql statement executed. You'll see the problem more easy than this way because the full parsed sql is shown (including field values). What strange error do you get now?
          Last edited by aducom; 07-23-2013, 01:02 PM.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            Hi ,
            it's still not clear how to make these kind of test. I got another similar case.
            It do not work properly but do not make errors.
            Now it makes only update also if I insert a new record (????)
            I also tryed to invert Update and Insert (as below) sections but it do not works the same.

            $sql_check_payment = "SELECT paymentID FROM payments WHERE InvoiceID = [glo_invoiceNr] AND invoiceDate = '[glo_invoiceDate]' AND vendorID = [glo_vendorID]";
            sc_select(check_payment, $sql_check_payment);
            $check_payment = {check_payment};
            echo "VAR ". {check_payment} . " $ ". $check_payment ;

            if (false == {check_payment})
            {
            // Error while accessing database
            }
            elseif ({check_payment}->EOF)
            { echo "SON UPDATE";
            $sql_vendor_name = "SELECT CompanyName FROM suppliers WHERE SupplierID = [glo_vendorID]";
            sc_lookup(vendor_name, $sql_vendor_name);
            $vendor_name = {vendor_name[0][0]};
            $payment_text = {lang_payments_nr_invoice} . ' ' . [glo_invoiceNr] . ' ' . {lang_payments_date_invoice} . ' ' . [glo_invoiceDate_eu] . ' ' . $vendor_name;

            $sql_update_payment = "UPDATE payments SET
            payment_cbID = 1,
            payment_timingID = 1,
            payment_casusal_ID = 1,
            payment_ledgerID = 2,
            paymentDate = '[glo_invoiceDate]',
            paydescr = '[glo_descri]',
            amount_in = [glo_Invoice_Vat_incl],
            currency = [glo_currencyID],
            exchange_rate = [glo_exchange_rate],
            note = '$payment_text',
            cashin_type = 17,
            service_typeID = [glo_service_typeID],
            projectID = [glo_projectID],
            invoiceID = [glo_invoiceNr],
            invoiceDate = '[glo_invoiceDate]',
            vendorID = [glo_vendorID],
            amount_vat_escl = [glo_Invoice_Vat_escl],
            amount_vat_incl = [glo_Invoice_Vat_incl],
            amount_vat= [glo_tot_vat]
            WHERE InvoiceID = [glo_invoiceNr] AND invoiceDate = '[glo_invoiceDate]' AND vendorID = [glo_vendorID] ";
            sc_exec_sql($sql_update_payment);
            // Attention payment_casusal_ID is 1 - cashin_type = 17 ??
            [yes_no]=0;
            }
            else
            { echo "SON INSERT";
            $sql_vendor_name = "SELECT CompanyName FROM suppliers WHERE SupplierID = [glo_vendorID]";
            sc_lookup(vendor_name, $sql_vendor_name);
            $vendor_name = {vendor_name[0][0]};
            $payment_text = {lang_payments_nr_invoice} . ' ' . [glo_invoiceNr] . ' ' . {lang_payments_date_invoice} . ' ' . [glo_invoiceDate_eu] . ' ' . $vendor_name;

            $sql_inser_payment = "INSERT INTO payments
            ( payment_cbID, payment_timingID, payment_casusal_ID, payment_ledgerID, paymentDate, paydescr, amount_in,
            currency, exchange_rate, note, cashin_type, service_typeID, projectID, invoiceID, invoiceDate, vendorID,
            amount_vat_escl, amount_vat_incl, amount_vat)
            VALUES ( 1, 1, 1, 2, '[glo_invoiceDate]', '[glo_descri]' , [glo_Invoice_Vat_escl],
            [glo_currencyID], [glo_exchange_rate], '$payment_text', 17, [glo_service_typeID], [glo_projectID], [glo_invoiceNr],
            '[glo_invoiceDate]', [glo_vendorID], [glo_Invoice_Vat_escl], [glo_tot_vat], [glo_Invoice_Vat_incl] )";
            sc_exec_sql($sql_inser_payment);
            // Attention payment_casusal_ID is 1 - cashin_type = 17 ??
            [yes_no]=0;
            }
            Last edited by giovannino; 01-10-2014, 06:55 AM.

            Comment


            • #7
              Change the logic:

              - check if record exist
              - if yes, update
              - if no, insert

              One check if record exist, then update or insert.
              Best regards: - Reinhard -

              I use ScriptCase 8 Enterprise Edition, Version 8.(latest)

              Comment


              • #8
                Hi, thanks
                sorry but for me it's not so easy

                The result of query
                $sql_check_payment = "SELECT paymentID FROM payments WHERE InvoiceID = [glo_invoiceNr] AND invoiceDate = '[glo_invoiceDate]' AND vendorID = [glo_vendorID]";
                sc_select(check_payment, $sql_check_payment);
                $check_payment = {check_payment};

                if I try to assign $check_payment = {check_payment[0][0]}; but i got error

                so the test I did was the following but it do not work the same.

                if (isset({check_payment})

                { UPDATE }
                else
                {INSERT}

                Which is the right way to make a test like this. Everytime I loose lot of time but still I do not understand how make it work

                Comment


                • #9
                  Ok , at the end this version workd

                  $sql_check_payment = "SELECT paymentID FROM payments
                  WHERE InvoiceID = [glo_invoiceNr] AND invoiceDate = '[glo_invoiceDate]' AND vendorID = [glo_vendorID]";
                  sc_lookup(check_payment, $sql_check_payment);
                  $check_payment = {check_payment[0][0]};
                  echo "VAR ". " $check_payment = ". $check_payment ;

                  if (isset({check_payment[0][0]}))

                  Comment

                  Working...
                  X