2. PHPRunner General questions
  3. [SOLVED] Unknown column in field list in V11 but V10.92 works fine
This topic is locked
[SOLVED]

Unknown column in field list in V11 but V10.92 works fine

 		2/4/2025 8:04:40 AM
PHPRunner General questions
C
 ChrisOB author

I have the following SQL in my Agreements Edit page that works in both PHPRunner versions 10.92 and version 11 in as much as it returns the results in the grid as expected. When I click "save" in version 10.92 all is good and updated details are saved (only those from the Agreements table are enabled for editing).

When I click save in version 11 (11.0.14 Mac) I get an error: "Unknown column 'mobileTelNo' in 'field list'". If I remove the mobile number (or even remove and replace it) I still get the unknown coulmn but it will now say workTelNo as the unknown field and so on. So I'm suspecting it's do with the Customers table or the relationship between the two tables when it's being written back.

I can confirm that all fields exist in their respective tables and that it works as expected in PHPRunner V10.92 but not in V11.

Many thanks in advance for any pointers as it's driving me mad as I can't find any reason why it doesn't work - but I'm clearly missing something. The SQL is:

SELECT
Agreements.agreementNo,
Agreements.agreementDate,
Customers.customerName,
Customers.addressLine1,
Customers.addressLine2,
Customers.addressLine3,
Customers.addressLine4,
Customers.postCode,
Customers.mobileTelNo,
Customers.workTelNo,
Customers.notes AS customer_notes,
Agreements.round,
Agreements.sequence,
Agreements.serviceCode,
Agreements.serviceInterval,
Agreements.startDayNo,
Agreements.endDayNo,
Agreements.targetDay,
Agreements.targetTime,
Agreements.materialsChg,
Agreements.labourChg,
Agreements.totalChg,
Agreements.notes AS agreement_notes,
Agreements.specificDayNo,
Agreements.cancelled,
Customers.aZREF AS customer_AZRef,
Agreements.sourceCode,
Agreements.payBy,
Agreements.keyNeeded,
Agreements.keyCode,
Agreements.keyID,
Agreements.totalAmountPaid,
Agreements.totalAmountDue,
Agreements.totalBalance,
Agreements.customerOrderRef,
Agreements.dateClosed,
Agreements.suppressDeliveryAddress,
Agreements.paymentTerms,
Agreements.keep,
Agreements.scheduleDisplayStartDate,
Agreements.weedAndFeed,
Agreements.scarify,
Agreements.weekNumber,
Agreements.contracts,
Agreements.rideOnMowing,
Agreements.customerCode AS customercode_agreements,
Customers.email,
Customers.customerCode AS customercode_customers,
Agreements.endDate,
Agreements.startDate,
Customers.landLineTelNo
FROM
Agreements
INNER JOIN Customers ON Customers.customerCode = Agreements.customerCode
ORDER BY
Agreements.customerCode
Admin 2/4/2025

What build of PHPRunner 11 is this?
C
 		ChrisOB author 2/4/2025

Hi Sergey,

It's 11.0.14 Mac but have also tried with 11.0.14 on Windows and it's the same. I wonder if there is an issue somwhere that 10.92 let pass but 11 is more strict, but I can't find it.
Admin 2/4/2025

Chris,

thi doesn't answer the question. I'm not asking what version to you use, I'm asking the build number.

C
 		ChrisOB author 2/4/2025

Apologies, it's 42780.
Admin 2/4/2025

Thank you.

  1. Please make sure you have the latest build 42871
  2. Synchronize your database and project
  3. Perform Project -> Save and rebuild project structures.

If this still doesn't help contact support directly sending your project export ( Project -> Export project ).

C
 		ChrisOB author 2/5/2025

Thanks Sergey,

Unfortunately the same happens with build 42781, after synchronising the DB and Project->save and build project structures. I have opened a support ticket with the export.

Many thanks,

Chris
C
 		ChrisOB author 2/6/2025

Thanks to Sergey this is issue is resolved. So it helps others, the problem was that I have fields from a joined table (i.e. not the main table) on the in the "form" area of the Edit page of the main table. Most of these fields are for display purposes but one does get updated and it was this updating that was causing the error.

Sergey spotted the issue and sent me to this link: https://xlinesoft.com/phprunner/docs/update_multiple_tables.htm about updating multiple tables and unsettng joined fields.

The code that works for me is in the "Edit->Before record updated" event, and is:

// To updated the editable field from the joined table
$sql = "update Customers set aZREF='".$values["customer_AZRef"]."'";
db_exec($sql);

// Unset the joined fields so it's just the mian Edit table fields that get written back.
unset($values["addressLine1"]);
unset($values["addressLine2"]);
unset($values["addressLine3"]);
unset($values["addressLine4"]);
unset($values["postCode"]);
unset($values["mobileTelNo"]);
unset($values["landLineTelNo"]);
unset($values["workTelNo"]);
unset($values["email"]);
unset($values["customer_AZRef"]);

return true;