Check of existing dataset

Developers Programming
#1

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)Scriptcase7_008wwwrootscriptcaseappZ_ASSOCIATIONorm_invoicesorm_invoices_apl.php (1651)

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

#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.

#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);
#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:



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?

#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;
}
#7

Change the logic:

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

One check if record exist, then update or insert.

#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

#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]}))