Unable to add new record

[TBriggs]

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]

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

[TBriggs]

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]

Anyone?

[shannah]

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]

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]

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]

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]

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]

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]

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]

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]

Look familiar?:

Code: Select all

$_GET['-sort'] = 'dog_name" asc';


[TBriggs]

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]

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