This topic is locked

Issue with varchar being interpreted as value

12/4/2016 1:09:02 PM
PHPRunner General questions
W
wijninga author

Hi,
I have an issue with the combination PHPRunner and MySQL. I have a table with a field that contains partnumbers, including partnumbers like 44E5083. The field type in MySQL is varchar.
Now when I hit a page with that partnumber I get the error message Illegal double '44E5083' value found during parsing. Googling the error message brought me to the believe that PHPRunner is interpreting the varchar as a value in scientific notification.
I changed my query to concert the varchar tot a CHAR, but the error keeps popping up. Has anyone seen this behaviour before and/or knows a solution or workaround?
Thanks!

HJB 12/4/2016

Replace 44E5083 with \'44E5083\'

W
wijninga author 12/4/2016



Replace 44E5083 with \'44E5083\'


Thanks for your reply Walk2fly!
That might be a solution for one partnumber, but I have lots of partnumbers that share that format. So I will have to fix it another way, I am afraid.

HJB 12/4/2016

Think, "custom field" with \' as prefix and \' as suffix should be a way out here,

or definition of a "mask" (https://www.jacoballred.com/wiki/php-mask-a-string/).

Am not familiar of what custom code to drop into the custom field, so we need to

wait for admin's input here any time from now.
P.S. https://xlinesoft.com/phprunner/docs/edit_as_settings_text_field.htm
If the part numbers have all the very same length, the pre- and suffix setting

might get placed this way.

admin 12/4/2016

You need to explain when exactly this error is happening. What "when I hit a page with that partnumber" means?
PHPRunner builds SQL queries based on data types in the database. The format of data itself is irrelevant.

kujox 12/4/2016

is this happening when you're doing some kind of import or restore using the actual data rather than a variable?

W
wijninga author 12/4/2016



You need to explain when exactly this error is happening. What "when I hit a page with that partnumber" means?
PHPRunner builds SQL queries based on data types in the database. The format of data itself is irrelevant.


The error happens when I access the page that lists all the partnumbers. Now the partnumbers are in a field called Partnumbers and has the type varchar. The error occurs when the page hits the partnumber like the one I mentioned above.
Now, I changed the field in the database from varchar to char, resynced the database but that didn't help.
I suspect that PHPRunner is not generating code that sees the field as char but instead creates code that tries to interpret the contents of the field instead of showing the contents. Something like missing quotes or something.

W
wijninga author 12/4/2016



is this happening when you're doing some kind of import or restore using the actual data rather than a variable?



No, I have a table with a field called partnumbers. One of the Partnumbers is like 44E5083. If I open the list page and the page hits that partnumber, it crashes with the above error message. It has nothing to do with importing or restoring, it is just when displaying the data from that table and hitting ANY partnumber which looks like it is an scientific number.

W
wijninga author 12/4/2016



You need to explain when exactly this error is happening. What "when I hit a page with that partnumber" means?
PHPRunner builds SQL queries based on data types in the database. The format of data itself is irrelevant.



This is the exact error message:
php error happened
Technical information

Error type 256

Error description Illegal double '44E5083' value found during parsing

URL 192.168.212.13/InventoryMutationsIncomingPO_list.php?pagesize=-1

Error file /var/www/html/connections/Connection.php

Error line 642

SQL query SELECT Partnumbers.Part_ID, concat(Partnumber,' - ',PartDescription,' - ',Manufacturer.ManufacturerDescription), Partnumbers.Partnumber, concat(CAST(Partnumber as CHAR(60)), ' - ', PartDescription, ' - ', Manufacturer.ManufacturerDescription) FROM Partnumbers INNER JOIN Manufacturer ON Partnumbers.Manufacturer_ID = Manufacturer.Manufacturer_ID WHERE (Partnumbers.Active =1) and (Partnumbers.Part_ID = 44E5083)

kujox 12/4/2016



This is the exact error message:
php error happened
Technical information

Error type 256

Error description Illegal double '44E5083' value found during parsing

URL 192.168.212.13/InventoryMutationsIncomingPO_list.php?pagesize=-1

Error file /var/www/html/connections/Connection.php

Error line 642

SQL query SELECT Partnumbers.Part_ID, concat(Partnumber,' - ',PartDescription,' - ',Manufacturer.ManufacturerDescription), Partnumbers.Partnumber, concat(CAST(Partnumber as CHAR(60)), ' - ', PartDescription, ' - ', Manufacturer.ManufacturerDescription) FROM Partnumbers INNER JOIN Manufacturer ON Partnumbers.Manufacturer_ID = Manufacturer.Manufacturer_ID WHERE (Partnumbers.Active =1) and (Partnumbers.Part_ID = 44E5083)


I can see the issue in the query, as it's a text field it should have '' around it, I'm sure PHPrunner should wrap them around the text automatically.

W
wijninga author 12/4/2016



I can see the issue in the query, as it's a text field it should have '' around it, I'm sure PHPrunner should wrap them around the text automatically.


Thanks Kujox, exactly my point! But there is something really strange going on. Because the field Part_ID is a unique int. The 44E5083 is in the Partnumber field.... So why is PHPRunner creating this strange and wrong query when accessing the list page...

lefty 12/4/2016



Thanks Kujox, exactly my point! But there is something really strange going on. Because the field Part_ID is a unique int. The 44E5083 is in the Partnumber field.... So why is PHPRunner creating this strange and wrong query when accessing the list page...


Have you tried without joining tables and just see if the partnumber throws error on your list page . I would start there . Which version of PHP do you use? Is it older than 5.5? on your server?

Also is that query in your mysql or is it exactly like that in your phprunner query ? If it is in your mysql table / sync in phprunnner and look at query. Anything different and do results work?

what are the partnumbers in all tables set as , I assume varchar in all tables?

W
wijninga author 12/4/2016



Have you tried without joining tables and just see if the partnumber throws error on your list page . I would start there . Which version of PHP do you use? Is it older than 5.5? on your server?

Also is that query in your mysql or is it exactly like that in your phprunner query ? If it is in your mysql table / sync in php and look at query. Anything different and do results work?

what are the partnumbers in all tables set as , I assume varchar in all tables?


I am using PHPRunner 9.6, so the most recent version.The error occurs both on PHP7 and PHP 5.4.I will try with a direct page without any joins and let you guys know.

lefty 12/4/2016



Thanks Kujox, exactly my point! But there is something really strange going on. Because the field Part_ID is a unique int. The 44E5083 is in the Partnumber field.... So why is PHPRunner creating this strange and wrong query when accessing the list page...


I think varchar have to be quoted so they are not parsed as numeric . ' field '. haven't tried this but something like this , but custom view on field partnumber $values = $data(sprintf('s%',["partnumber"])) where s% format's as a string. Maybe admin can chime in an get this correct for custom view.

admin 12/5/2016

Lots of confusions here.
Both CHAR and VARCHAR are text datatypes meaning that changing field type from VARCHAR to CHAR won't change anything. Also, PHPRunner doesn't make any decisions based on field value, only based on data type.
This is an issue with project configuration and you need to look closer into it. For instance, do you use Advanced Security like 'Users can see and edit their own data only' in this project?

jadachDevClub member 12/5/2016

You might want to check the view as config and make sure the validation isn't numeric.