Xataface Forums

Hi,

I'm trying to add a new record into one of my tables. (In this instance, called "ContractData".) If I use drop down menus to populate 2 of the fields (CompanyName and SiteName - which are taken from other tables) then everything is fine when I insert the record. However, if I have (instead of a drop down menu) a multiple select type menu (tick box, advmultiselect, etc) then it throws an error (even if I still only select one option)

Code: Select all

PHP Fatal error:  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1SELECT COUNT(*) as num FROM `ContractData` WHERE `ContractRecordID` = '8' AND `ContractType` LIKE CONCAT('%','5','%') AND `ContractLength` LIKE CONCAT('%','12','%') AND `ContractStartDate` LIKE CONCAT('%','2010-03-17','%') AND `ContractEndDate` LIKE CONCAT('%','2011-03-17','%') AND `ContractActive` LIKE CONCAT('%','NO','%') AND `ContractStatus` LIKE CONCAT('%','New','%') AND `ContractSupplier1` LIKE CONCAT('%','1','%') AND `ContractStandingCharge` LIKE CONCAT('%','1','%') AND `ContractDayRate` LIKE CONCAT('%','1','%') AND `ContractNightRate` LIKE CONCAT('%','1','%') AND `ContractCommissionType` LIKE CONCAT('%','1','%') AND `ContractCommissionRate` LIKE CONCAT('%','1','%') AND `CompanyName` LIKE CONCAT('%','PaulGray','%') ANDOn line 127 of file /var/www/html/public/dataface/Dataface/QueryTool.php in function printStackTrace()\n<br>On line 524 of file /var/www/html/pub in /var/www/html/public/dataface/Dataface/QueryTool.php on line 127


I really want to be able to select multiple options for SiteName so that each multiple SiteNames can have the same ContractID (I guess I may need some relationship stuff here)..but at the moment, I can't even use a multi select type box to populate even when only selecting one option.

Can anyone help at all?

Thanks

Paul

What version of Xataface/PHP/MySQL are you using?
and
can you post the relevant fields.ini file and table def?

Hi Steve, Sorry, should've really added these in the first place.

Xataface Version = 1.2.3b2 1700
PHP = 5.3.1
MYSQL = 5.1.4.2

The only stuff in tables/ContractData/ContractData.php is stuff relating to dates. Nothing that would affect this afaik.

tables/ContractData/fields.ini snippet

Code: Select all

[CompanyName]
widget:label = "Company Name"
;visibility:list = hidden
widget:type = select
vocabulary = CompanyName
order=15

[SiteName]
widget:label = "Site Name"
widget:type = select
vocabulary = SiteName


valuelists.ini snippet

Code: Select all

[CompanyName]
__sql__= "SELECT CompanyName FROM Customers ORDER BY CompanyName"

[SiteName]
__sql__= "SELECT SiteName FROM SiteData ORDER BY SiteName"


I should say...when it's like this, it works fine but if I change the SiteName section of fields.ini from widget:type = select to widget:type = checkbox or advmultiselect then it throws the error. I've only just noticed this as I really want to be able to select multiple SiteNames to do the insertion. Might it be failing due to there not being a relationship at all?

Cheers

Paul

Hi Steve, OK, I have the checkboxes now being display via a relationship but I still can't successfully select one or many of them. Here's the error now: This error is from a tail of my apache error_log. The web browser shows a similar log.

Apache error_log

Code: Select all

[Wed Mar 17 22:45:32 2010] [error]  PHP Warning:  explode() expects parameter 2 to be string, array given in /var/www/html/public/dataface/Dataface/QueryBuilder.php on line 502, referer: http://www.example.com/index.php?-action=new&-table=ContractData
[Wed Mar 17 22:45:32 2010] [error] PHP Warning:  Invalid argument supplied for foreach() in /var/www/html/public/dataface/Dataface/QueryBuilder.php on line 517, referer: http://www.example.com/index.php?-action=new&-table=ContractData
[Wed Mar 17 22:45:32 2010] [error] PHP Fatal error:  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1SELECT COUNT(*) as num FROM `ContractData` WHERE `ContractRecordID` = '9' AND `ContractType` LIKE CONCAT('%','5','%') AND `ContractLength` LIKE CONCAT('%','1','%') AND `ContractStartDate` LIKE CONCAT('%','2010-03-17','%') AND `ContractEndDate` LIKE CONCAT('%','2010-04-17','%') AND `ContractActive` LIKE CONCAT('%','NO','%') AND `ContractStatus` LIKE CONCAT('%','New','%') AND `ContractSupplier1` LIKE CONCAT('%','1','%') AND `ContractStandingCharge` LIKE CONCAT('%','1','%') AND `ContractCapacity` LIKE CONCAT('%','1','%') AND `ContractDayRate` LIKE CONCAT('%','1','%') AND `ContractNightRate` LIKE CONCAT('%','1','%') AND `ContractCommissionType` LIKE CONCAT('%','1','%') AND `ContractCommissionRate` LIKE CONCAT('%','1','%') AND `CompanyName` LIKE CONCAT('%','PaulGray','%') ANDOn line 127 of file /var/www/html/public/dataface/Dataface/QueryTool.php in function printStackTr in /var/www/html/public/dataface/Dataface/QueryTool.php on line 127, referer: http://www.example.com/index.php?-action=new&-table=ContractData


Error in Browser

Code: Select all

SELECT COUNT(*) as num FROM `ContractData` WHERE `ContractRecordID` = '9' AND `ContractType` LIKE CONCAT('%','5','%') AND `ContractLength` LIKE CONCAT('%','1','%') AND `ContractStartDate` LIKE CONCAT('%','2010-03-17','%') AND `ContractEndDate` LIKE CONCAT('%','2010-04-17','%') AND `ContractActive` LIKE CONCAT('%','NO','%') AND `ContractStatus` LIKE CONCAT('%','New','%') AND `ContractSupplier1` LIKE CONCAT('%','1','%') AND `ContractStandingCharge` LIKE CONCAT('%','1','%') AND `ContractCapacity` LIKE CONCAT('%','1','%') AND `ContractDayRate` LIKE CONCAT('%','1','%') AND `ContractNightRate` LIKE CONCAT('%','1','%') AND `ContractCommissionType` LIKE CONCAT('%','1','%') AND `ContractCommissionRate` LIKE CONCAT('%','1','%') AND `CompanyName` LIKE CONCAT('%','PaulGray','%') AND


Hope you can help.

Cheers

Paul

ahhh, sorted I was being a total tool....sorry, I'm still learning

My relationship was using a fieldname that already existed in the table. I've removed it now and it'll all great.

There is one thing though....(of course there is)...My checkboxes have now appeared in the top of the form when I click "New Entry"..is there anyway to get them to the bottom? Better still, Can I dynamically generate them based on what's selected from anther drop down? (I don't want much do I?)

Thanks

Paul