Unable to add new record

Archived from the Xataface Users forum.

TBriggs — Sat Jun 25, 2011 9:35 am

Using Xataface 1.1.5r2, PHP4, MySQL5.0. When trying to add a new record, I get the message:

Error: Could not load current record: 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 ‘” asc LIMIT 0,1’ at line 1 SELECT length(greyhounds.dog\_name) as \_\_dog\_name\_length,greyhounds.dog\_name,length(greyhounds.index\_no) as \_\_index\_no\_length,greyhounds.index\_no,length(greyhounds.county\_group) as \_\_county\_group\_length,greyhounds.county\_group,length(greyhounds.gpa\_tag) as \_\_gpa\_tag\_length,greyhounds.gpa\_tag,length(greyhounds.le\_tattoo) as \_\_le\_tattoo\_length,greyhounds.le\_tattoo,length(greyhounds.re\_tattoo) as \_\_re\_tattoo\_length,greyhounds.re\_tattoo,length(greyhounds.status) as \_\_status\_length,greyhounds.status,length(greyhounds.county\_tag) as \_\_county\_tag\_length,greyhounds.county\_tag,length(greyhounds.registered\_name) as \_\_registered\_name\_length,greyhounds.registered\_name,length(greyhounds.color) as \_\_color\_length,greyhounds.color,length(greyhounds.sex) as \_\_sex\_length,greyhounds.sex,length(greyhounds.kids) as \_\_kids\_length,greyhounds.kids,length(greyhounds.cats) as \_\_cats\_length,greyhounds.cats,length(greyhounds.smalldogs) as \_\_smalldogs\_length,greyhounds.smalldogs,length(greyhounds.birds) as \_\_birds\_length,greyhounds.birds,length(greyhounds.leash) as \_\_leash\_length,greyhounds.leash,length(greyhounds.crate) as \_\_crate\_length,greyhounds.crate,length(greyhounds.adoption\_date) as \_\_adoption\_date\_length,greyhounds.adoption\_date,length(greyhounds.fixed\_date) as \_\_fixed\_date\_length,greyhounds.fixed\_date,length(greyhounds.fixed\_by\_vet) as \_\_fixed\_by\_vet\_length,greyhounds.fixed\_by\_vet,length(greyhounds.received\_type) as \_\_received\_type\_length,greyhounds.received\_type,length(greyhounds.received\_date) as \_\_received\_date\_length,greyhounds.received\_date,length(greyhounds.received\_from) as \_\_received\_from\_length,greyhounds.received\_from,length(greyhounds.notes) as \_\_notes\_length,greyhounds.notes,length(greyhounds.newsletter) as \_\_newsletter\_length,greyhounds.newsletter,length(greyhounds.onr\_firstname) as \_\_onr\_firstname\_length,greyhounds.onr\_firstname,length(greyhounds.onr\_lastname) as \_\_onr\_lastname\_length,greyhounds.onr\_lastname,length(greyhounds.onr\_street) as \_\_onr\_street\_length,greyhounds.onr\_street,length(greyhounds.onr\_city) as \_\_onr\_city\_length,greyhounds.onr\_city,length(greyhounds.onr\_state) as \_\_onr\_state\_length,greyhounds.onr\_state,length(greyhounds.onr\_zip) as \_\_onr\_zip\_length,greyhounds.onr\_zip,length(greyhounds.onr\_homephone) as \_\_onr\_homephone\_length,greyhounds.onr\_homephone,length(greyhounds.onr\_workphone) as \_\_onr\_workphone\_length,greyhounds.onr\_workphone,length(greyhounds.email\_1) as \_\_email\_1\_length,greyhounds.email\_1,length(greyhounds.email\_2) as \_\_email\_2\_length,greyhounds.email\_2,length(greyhounds.drivers\_license) as \_\_drivers\_license\_length,greyhounds.drivers\_license FROM greyhounds WHERE greyhounds.dog\_name LIKE CONCAT(‘%’,’Test4442’,’%’) AND greyhounds.status LIKE CONCAT(‘%’,’Unknown’,’%’) AND greyhounds.color LIKE CONCAT(‘%’,’Unknown’,’%’) AND greyhounds.sex LIKE CONCAT(‘%’,’Unknown’,’%’) AND greyhounds.kids LIKE CONCAT(‘%’,’Unknown’,’%’) AND greyhounds.cats LIKE CONCAT(‘%’,’Unknown’,’%’) AND greyhounds.smalldogs LIKE CONCAT(‘%’,’Unknown’,’%’) AND greyhounds.birds LIKE CONCAT(‘%’,’Unknown’,’%’) AND greyhounds.leash LIKE CONCAT(‘%’,’Unknown’,’%’) AND greyhounds.crate LIKE CONCAT(‘%’,’Unknown’,’%’) AND greyhounds.received\_type LIKE CONCAT(‘%’,’Unknown’,’%’) ORDER BY greyhounds.dog\_name” asc LIMIT 0,1

The problem appears to be the double quote before “asc” near the end because the SQL statement works if used in PHPmyAdmin without this.

Thanks for any help you can give.

shannah — Sat Jun 25, 2011 12:23 pm

Are you using an __sql__ directive in your fields.ini file? If so can you post it?

TBriggs — Sat Jun 25, 2011 2:26 pm

I’m not using one in my fields.ini file.

I have some in my valuelists.ini file:

[Counties]
__sql__=”SELECT county_group FROM county\_group\_lookup ORDER BY county_group”

[Sexes]
__sql__=”SELECT sex_type FROM sex\_lookup ORDER BY sort_seq”

[Colors]
__sql__=”SELECT color FROM color\_lookup ORDER BY color”

[Answers]
__sql__=”SELECT answer_type FROM answer\_lookup ORDER BY sort_seq”

[Received]
__sql__=”SELECT receivedtype FROM receivedtype\_lookup ORDER BY receivedtype”

[Statii]
__sql__=”SELECT status_type FROM status\_lookup ORDER BY status_type”

TBriggs — Mon Jun 27, 2011 3:15 pm

Anyone?

shannah — Mon Jun 27, 2011 3:29 pm

What is the URL of the page that is giving this error. I’m interested in looking at the query string. Especially the part that says “-sort=xyz..”

-Steve

TBriggs — Mon Jun 27, 2011 6:51 pm

Steve - the page is secured - I’d have to fix that before you could get to it. But, I’m not sure what you mean by the “-sort=xyz…”?

shannah — Mon Jun 27, 2011 9:09 pm

I don’t need to access the page. I need to see what the URL looked like. The URL will look something like
index.php?-table=foo&-action=bar&firstname=steve&-sort=last_name+desc

I just what to set what GET parameters are being passed to the script that caused this error.

_Steve

TBriggs — Tue Jun 28, 2011 3:42 pm

Oh - Okay. Is this what you mean?

index.php?-action=new&-table=greyhounds

This is the URL showing when the New Record screen is being displayed.

shannah — Tue Jun 28, 2011 4:06 pm

Really? That’s the URL that is displayed in the address bar when you get this error message. The query is showing a very specific search for a whole bunch of stuff as well as a a sort. This doesn’t seem right.

TBriggs — Tue Jun 28, 2011 7:36 pm

That’s the URL when the New Record screen is displaying. When I click on the “Save” button I get the error message. At that point the URL just ends in …index.php

shannah — Wed Jun 29, 2011 9:55 am

Do you have any relationships or valuelists where the SQL includes an ORDER BY dogname clause? I’m trying to figure out where that query is coming from.

-Steve

TBriggs — Wed Jun 29, 2011 3:34 pm

This is my index.php file, could this be it?

<?
require_once ‘../Interface/xataface-1.1.5r2/dataface-public-api.php’;
if ( !isset( $_REQUEST[‘-sort’])){
$_REQUEST[‘-sort’] = ‘dog_name asc’;
$_GET[‘-sort’] = ‘dog_name” asc’;
}
df_init(__FILE__, ‘../Interface/xataface-1.1.5r2’);
$app =& Dataface_Application::getInstance();
$app->display();
?>

shannah — Wed Jun 29, 2011 3:37 pm

Look familiar?:

Code: Select all: $_GET['-sort'] = 'dog_name" asc';

TBriggs — Wed Jun 29, 2011 6:48 pm

Rats! Well spotted Steve!

The only thing that confuses we now is why did that only cause a problem when I tried to add a new record? I guess I don’t really understand what that code in the index.php file does?

Thanks for all your help.

shannah — Wed Jun 29, 2011 7:26 pm

Long story short, before manipulating the request vars I generally make sure that it isn’t a POST request so that nothing gets mucked with when we’re writing to the db.

Also your isset() test was done on the $_REQUEST var which includes $_POST request vars, but then you only update the $_REQUEST and $_GET vars - not $_POST.

The code I would use in your place is:

Code: Select all: if ( !$_POST and !isset($_GET['-sort']) ){ $_GET['-sort'] = $_REQUEST['-sort'] = 'dog_name asc'; }

I presume you’re using 1.1.5 because you’re still on PHP 4. Any plans to upgrade?

-Steve

shannah — Wed Jun 29, 2011 9:55 am

Do you have any relationships or valuelists where the SQL includes an ORDER BY dogname clause? I’m trying to figure out where that query is coming from.

-Steve

TBriggs — Wed Jun 29, 2011 3:34 pm

This is my index.php file, could this be it?

shannah — Wed Jun 29, 2011 3:37 pm

Look familiar?:

Code: Select all: $_GET['-sort'] = 'dog_name" asc';

TBriggs — Wed Jun 29, 2011 6:48 pm

Rats! Well spotted Steve!

The only thing that confuses we now is why did that only cause a problem when I tried to add a new record? I guess I don’t really understand what that code in the index.php file does?

Thanks for all your help.

shannah — Wed Jun 29, 2011 7:26 pm

Long story short, before manipulating the request vars I generally make sure that it isn’t a POST request so that nothing gets mucked with when we’re writing to the db.

Also your isset() test was done on the $_REQUEST var which includes $_POST request vars, but then you only update the $_REQUEST and $_GET vars - not $_POST.

The code I would use in your place is:

Code: Select all: if ( !$_POST and !isset($_GET['-sort']) ){ $_GET['-sort'] = $_REQUEST['-sort'] = 'dog_name asc'; }

I presume you’re using 1.1.5 because you’re still on PHP 4. Any plans to upgrade?

-Steve

TBriggs — Thu Jun 30, 2011 3:24 pm

PHP4 is what my hosting company loads by default. I’ve got 3 databases on this web site, all using a Xataface front end and I’m scared of breaking anything!

TBriggs — Sat Jul 02, 2011 12:31 pm

To be honest, I don’t know where that code in the index.php file came from! I thought Xataface put it in there all by itself.

I actually have another problem with this file. The interface was originally only accessing one table in the database, but now I’ve allowed access to another table. Unfortunately, it looks like Xataface is trying to enforce these sort rules to the other table, which doesn’t have the same fields. How do I make this piece of code only apply to the “greyhounds” table?

Thanks.

TBriggs — Sun Jul 03, 2011 12:57 pm

Ah - I found the answer in another thread. This appears to work:

Code: Select all: if ( !$_POST and !isset($_GET['-sort']) and @$_REQUEST['-table'] == 'greyhounds' ){ $_GET['-sort'] = $_REQUEST['-sort'] = 'dog_name asc'; }

shannah — Sun Jul 03, 2011 1:32 pm

The best way is actually to put this inside the beforeHandleRequest() method of the application delegate class, and to use the query as returned from Dataface_Application::getQuery() because that way it guarantees that the -table parameter has been set.

e.g.

Code: Select all: function beforeHandleRequest(){ $query =& Dataface_Application::getInstance()->getQuery(); if ( !$_POST and $query['-table'] == 'foo' and !@$query['-sort'] ){ $query['-sort'] = 'bar desc'; } }

*This code only works in PHP 5 or higher due to chaining.
**It is important to return the query by reference (i.e. =&) so that you can affect the query in place.

Steve

TBriggs — Thu Jul 07, 2011 4:40 pm

I’ve just noticed another issue with this installation. When I first log in to the database, the greyhounds table displays in list mode, but it’s not sorted. If I go to another screen and then go back to the list, it’s sorted by dog_name like it should be.

Any idea why it isn’t sorted the first time in?

Thanks,