multi checkbox error when creating new record

Archived from the Xataface Users forum.

notesgnome — Mon Mar 21, 2011 6:02 pm

Firstly, cudos to you for creating such an app, it seems really good.

Now, I’m only starting on my journey with it, but have come across a problem I hope others can help me with.

System info:
Apache/2.2.16 (Ubuntu)
PHP Version 5.3.3-1ubuntu9
MySQL 5.1.49

If I create a new record, the page loads correctly; select boxes are correct, and the options for the multi-checkboxes are correct.

However, when I save it, and I select one or more of the multi-checkboxes, I just get a page with the following, and no record created:
SELECT COUNT(*) as num FROM QualityReport WHERE QualityReport.Agent LIKE CONCAT(‘%’,’1’,’%’) AND QualityReport.ContactDate LIKE CONCAT(‘%’,’2011-03-22’,’%’) AND QualityReport.QualityID LIKE CONCAT(‘%’,’1’,’%’) AND AND QualityReport.Reviewer LIKE CONCAT(‘%’,’2’,’%’)

Note the AND AND.

If I save it with no multi-checkboxes selected, it saves correctly, creating the record in the database. If I then update the record including multi-checkbox choices, it updates correctly. The problem only occurs if I select one or more options in the multi-checkbox when creating the record.

Any pointers as to where I’m going wrong would be MUCH appreciated.

shannah — Mon Mar 21, 2011 7:05 pm

Try updating your Dataface/QueryBuilder.php with the one from SVN. I think this may already be corrected in the latest dev trunk:
http://weblite.ca/svn/dataface/core/tru … uilder.php

shannah — Mon Mar 21, 2011 7:10 pm

Actually… this fix could also have been in the Dataface/Record.php file.
http://weblite.ca/svn/dataface/core/tru … Record.php
or the Dataface/Relationship.php file:
http://weblite.ca/svn/dataface/core/tru … onship.php

notesgnome — Mon Mar 21, 2011 11:56 pm

Thanks for the extra quick response. Alas, it does not seem to have resolved the issue.

I tried replacing just the QueryBuilder.php - no change
I then replace the other 2 files mentions - no change
I then copied the whole of the Dataface directory - no change.

If you need any more info to narrow down the problem area, just ask..

Thanks in advance.

cantlep — Tue Mar 22, 2011 8:17 am

Hi, I’ve had a similar issue in the past with this.

My relationship was using a fieldname that already existed in that table….Worth checking the same on your setup?

Paul

shannah — Tue Mar 22, 2011 8:35 am

Can you provide me with a minimal amount of information to try to reproduce this. e.g. the relevant table definitions, relationships.ini directives, and fields.ini directives, I’ll see if I can track it down.

-Steve

cantlep — Tue Mar 22, 2011 8:57 am

This was my one with the same AND AND error

viewtopic.php?f=4&t=5239

Cheers - Been away for a while. Good to be back

notesgnome — Tue Mar 22, 2011 9:04 pm

Here goes:
valuelists.ini
[Agents]
__sql__ = “SELECT ID, Concat(Firstname, ‘ ‘, Lastname) AS Staff FROM People ORDER BY Staff”

[Reviewers]
__sql__ = “SELECT ID, Concat(Firstname, ‘ ‘, Lastname) AS Staff FROM People ORDER BY Staff”

[Markdowns]
__sql__ = “SELECT ID, MarkDownOptions FROM QualityMarkDowns ORDER BY MarkDownOptions”

fields.ini
[ID]
visibility:list = hidden

[Agent]
widget:label = “Name of Rep”
widget:description = “Select the rep whose quality this refers to”
widget:atts:style = “font-size: 16pt”
widget:type = “select”
vocabulary = Agents
editvalues = 0

[ContactDate]
widget:label = “Date of Contact/Call”
widget:description = “Select the date of this call/contact”

[QualityID]
widget:label = “Quality ID”
widget:description = “Enter the ID of this contact/call”

[DeepDive]
widget:label = “Deep Dive”
widget:description = “Select to identify this quality as a Deep Dive, or a normal quality”
widget:type = “checkbox”

[MarkedDown]
widget:label = “Marked Down for”
widget:description = “Select all the reasons this quality has been marked down”
widget:type = “checkbox”
vocabulary = Markdowns

[Reviewer]
widget:label = “Name of Reviewer”
widget:description = “Select the name of the Reviewer”
widget:type = “select”
vocabulary = Reviewers

[DisputeChanged]
widget:label = “Changed after Dispute”
widget:description = “Has the score has been changed as a result of the dispute process”
widget:type = “checkbox”

[Notes]
widget:type = “htmlarea”

[Created]
widget:type = “hidden”
timestamp = insert

[Updated]
widget:type = “hidden”
timestamp = update

relationships.ini does not exist (is this the issue?) I see there was another issue where a definition in relationships existed elsewhere.

The SQL table is:
FIELD TYPE COLLATION NULL KEY DEFAULT Extra PRIVILEGES COMMENT
————– ———– —————– —— —— ——- ————– ——————————- ——-
ID INT(8) (NULL) NO PRI (NULL) AUTO_INCREMENT SELECT,INSERT,UPDATE,REFERENCES
Agent VARCHAR(50) latin1_swedish_ci NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
ContactDate DATE (NULL) NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
QualityID VARCHAR(30) latin1_swedish_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
DeepDive VARCHAR(20) latin1_swedish_ci NO Quality SELECT,INSERT,UPDATE,REFERENCES
MarkedDown VARCHAR(30) latin1_swedish_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
Reviewer VARCHAR(50) latin1_swedish_ci NO (NULL) SELECT,INSERT,UPDATE,REFERENCES
DisputeChanged VARCHAR(3) latin1_swedish_ci NO NO SELECT,INSERT,UPDATE,REFERENCES
Notes MEDIUMTEXT latin1_swedish_ci YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
Created DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES
Updated DATETIME (NULL) YES (NULL) SELECT,INSERT,UPDATE,REFERENCES

There is only 1 table defined.

Any help would be greatly appreciated.

Simon

shannah — Wed Mar 23, 2011 10:37 am

What was the URL in the address bar when the error occurred.
Also.. for the table can you post an SQL create table statement so that it’s easier for me to just dump this into a database and try out?

-Steve

notesgnome — Wed Mar 23, 2011 2:49 pm

Steve,

as requested:

SQL:
CREATE TABLE QualityReport (
ID INT(8) NOT NULL AUTO_INCREMENT,
Agent VARCHAR(50) NOT NULL,
ContactDate DATE NOT NULL,
QualityID VARCHAR(30) DEFAULT NULL,
DeepDive VARCHAR(20) NOT NULL DEFAULT ‘Quality’,
MarkedDown VARCHAR(30) DEFAULT NULL,
Reviewer VARCHAR(50) NOT NULL,
DisputeChanged VARCHAR(3) NOT NULL DEFAULT ‘No’,
Notes MEDIUMTEXT,
Created DATETIME DEFAULT NULL,
Updated DATETIME DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=MYISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

URL when creating the new record:
http://10.1.1.107/Reporting/index.php?- … lityReport

URL when submitting the record:
http://10.1.1.107/Reporting/index.php

URL when submitting a record successfully (ie without anything in the multicheckbox):
http://10.1.1.107/Reporting/index.php?- … ully+saved.

URL when successfully updating a record (adding values for multicheckbox):
http://10.1.1.107/Reporting/index.php?- … ully+saved.

As always, if you require any more info (SQL for the other tables, etc), just let me know.

Thanks
Simon

shannah — Wed Mar 23, 2011 3:40 pm

Which of those pages does the error occur on?

-Steve

notesgnome — Wed Mar 23, 2011 6:55 pm

sorry my lack of clarity - when it occurs, it is the second one. The url is just:
http://10.1.1.107/Reporting/index.php

and ths only thing displayed is:
SELECT COUNT(*) as num FROM QualityReport WHERE QualityReport.Agent LIKE CONCAT(‘%’,’1’,’%’) AND QualityReport.ContactDate LIKE CONCAT(‘%’,’2011-03-22’,’%’) AND QualityReport.QualityID LIKE CONCAT(‘%’,’1’,’%’) AND AND QualityReport.Reviewer LIKE CONCAT(‘%’,’2’,’%’)

shannah — Fri Mar 25, 2011 10:31 am

Thanks for the examples. I’ve been able to reproduce the problem and solve it. I had to make changes to a few files to fix this issue. You’ll need to update the following files to their counterparts in the latest SVN:

Dataface/Application.php
Dataface/QueryTool.php
Dataface/QuickForm.php

You can get them from
http://weblite.ca/svn/dataface/core/trunk/

-Steve

notesgnome — Fri Mar 25, 2011 12:40 pm

Sir, you are a scholar and a gentleman.

The fix you provided worked perfectly. Many thanks for your extremely rapid help and solution.

shannah — Wed Mar 23, 2011 3:40 pm

Which of those pages does the error occur on?

-Steve

notesgnome — Wed Mar 23, 2011 6:55 pm

sorry my lack of clarity - when it occurs, it is the second one. The url is just:
http://10.1.1.107/Reporting/index.php

shannah — Fri Mar 25, 2011 10:31 am

Dataface/Application.php
Dataface/QueryTool.php
Dataface/QuickForm.php

You can get them from
http://weblite.ca/svn/dataface/core/trunk/

-Steve

notesgnome — Fri Mar 25, 2011 12:40 pm

Sir, you are a scholar and a gentleman.

The fix you provided worked perfectly. Many thanks for your extremely rapid help and solution.