ERROR Undefined offset: 0

Applications Forms
#1

Hi all,
I’ve still problem with ERROR Undefined offset: 0
If the select below do not find any record how have I to manage it without having the annoying error ?

$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]};

No one of following works

  1. if (isset({check_payment[0][0]})) // Row found
  2. if (isset($check_payment)) // Row found
  3. if(empty($check_payment)) // Row not found

after test I have to INSERT or UPDATE the record. With UPDATE I do not have any error.

#2

Try with debug on and look at the generated sql string. Is that string correct? (check it with phpmyadmin etc.).

#3

Hi, thanks…:wink:
I’ve tried (randomly…) and using sc_select seems to understand the if (false == {check_payment}) when it do not find an existing record.
Crossing fingers the message is not shown now.
The problem is that is always not clear to me the exact procedure to follow. Why one time works sc_lookup and the other one sc_select. Why sometime I have to use {var} and other {var[0][0]}. Probably for a programmer they are all pieces of cakes … but for a power user they are not so obvious.

$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 (false == {check_payment})
{
// Error while accessing database
}
elseif ({check_payment}->EOF)

… {Insert }

else

… {Update }

#4

I don’t know if it would make any difference but I would avoid to use duplicate names like $check_payment = {check_payment}; As the latest is the screen variable I can image that there might be conflicts. There’s a different between select and lookup. One call returns an array of values which is why you need to have the [0][0] aproach: a reference to a value in an array. the other is a cursor way so you get a record a time and need to traverse through the resultset with movenext.

#5

Hi,
Albert is right, you are walking on thin ice with your variable naming scheme. Your $check_payment and {check_payment} are the same variable/array.
You can test it:
sc_lookup(rs,‘SELECT something FROM mytable’);
print_r({rs});
print_r($rs);
Now to your problem. You are performing a variable allocation before you validate your result, that’s where your error is coming from. With $check_payment = {check_payment[0][0]} before the validation structure you are referencing an array regardless if it even exists. Hence an error when your select returns nothing.
You have to assign a value to a variable inside of your if-else structure. (If it is necessary at all.)

$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);

if({check_payment} === false) // something went wrong
{
errormessage;
}
elseif(empty({check_payment})) //nothing found
{
insert;
}
else // data found
{
update;
}
Depending on your situation you could probably use an INSERT INTO … ON DUPLICATE KEY UPDATE … and let the database take care of it.

Hope this clears the fog a little, or made it even thicker. :slight_smile:

jsb