Search Recordset variable

Hi,

I have my recordset on the page which has some AS variables.

i.e. i calculate the users age based on date of birth with AS Age in my mysql query.

only problem is i want to use my search page to search this part of my recordset but it only gives me the option to do search by column.

can someone from WA help me manually code it do search this part of my recordset?

many thanks

This might be a little more involved that it seems. One work around that I can think of would be to refer to a particular column when you are setting up the DataAssist Search server behavior, then after the fact go into the DataAssist Search code that is written to the page and update the name of the column you selected to the name it is set to in your recordset.

If you can post back with a page that has the search on it and let us know the column you have selected and what you would like it to be I can show you how this change would be made.

Hi Eric,

Thanks for getting back to me.

This is my recordset as i tried doing what you said but it did not work:

SELECT clients.ClientID, clients.DateCreated, clients.App1Title, clients.App1FirstName, clients.App1LastName, clients.App1DOB, clients.App1Email, clients.App1House, clients.App1Street, clients.App1County, clients.App1City, clients.App1Zip, clients.App1Country, clients.App1Phone, clients.App1Mob, clients.InitialEnquiry, clients.Status, clients.ClientUserID, clients.referredby_RefID, clients.App2Title, clients.App2FirstName, clients.App2LastName, clients.App2DOB, clients.App2Email, clients.App2Phone, clients.App2Mob, clients.App2House, clients.App2Street, clients.App2City, clients.App2Zip, clients.App2Country, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(App1DOB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(App1DOB, '00-%m-%d')) AS App1Age, DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(App2DOB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(App2DOB, '00-%m-%d')) AS App2Age FROM clients ORDER BY clients.App1LastName"

Data assist search code from page:

$WADbSearch1->keywordComparison($KeyArr0,"".((isset($_POST["LastName"]))?$_POST["LastName"]:"") ."","AND","Includes",",%20","%20","%22","%22",0);
$WADbSearch1->addComparisonFromEdit("App1DOB","StartDOB","OR",">=",2);
$WADbSearch1->addComparison("App1DOB","".WAQB_getEndDate($_POST["EndDOB"]) ."","AND","<=",2);
$WADbSearch1->addComparisonFromEdit("App1Age","Age","OR","=",1);
$WADbSearch1->keywordComparison($KeyArr1,"".((isset($_POST["City"]))?$_POST["City"]:"") ."","OR","Includes",",%20","%20","%22","%22",0);
$WADbSearch1->keywordComparison($KeyArr2,"".((isset($_POST["Zip"]))?$_POST["Zip"]:"") ."","OR","Includes",",%20","%20","%22","%22",0);

this does not work, so not sure how to have it search the age.

everything else works except the date format which i have another post on.

if you could help asap i would appreciate as need to get this search page finished.

many thanks

Are you getting an 'unknowed column' error? If not then the query is probably executing correctly but does not have a proper value for the column alias you created.

When you test this query out can you reference your calculated columns and see the correct value for them? This will be the first step in getting it to work. Once that is done you should be able to reference the same name in the DA Search server behavior. If you have any further problems with it post back with the entire search page and I can add some debug code to print out the query that is being executed to get a better idea for how this is being handled.

hi eric,

yes i have an unknown column error. however i added in a column App1Age and App2Age in mysql database with type int.

however it wont have a value in the database as it is the query which is producing the value from the date of birth column.

when i test my recordset it shows fine with a value in the app1age.

also when i display it on the page next to that clients details it shows the correct value from the recordset.

all i want to be able to do is search the recrodset.

i there custom coding that is required in the backend or helper file?

if you can let me know asap as i need to be able to search this value on my page.

many thanks

You are creating a column alias and attempting to search that. As far as I know this should not be a problem. You should not have columns with these names in the table, that could cause other problems.

Post back with the entire search page and I can add some debug code to print out the query that is being executed to get a better idea for how this is being handled.

eric again i cannot send this on a public forum.

give me your email address or support ticket as this is a problem.

i have tried with a column and without so it does not work.

i took out the column did not work, put in the column and it did not work.

on my search page it searches the previous posted recordset with the previous posted search code generated by data assist.

it returns no results yet there is results as when i do not search it shows all the clients name, age, address and all can be search no problem other than the alias AGE.

thanks

I have opened a ticket for you on this. To update the ticket login and visit your support history.