Searching for same field twice in a detail table

Andy,
you can edit your SQL query on the Edit SQL query tab in the following way:

select `ID`,

And then use these two fields (language1 and language2) and All conditions option on the Advanced search page.

Hi Jane
As simple as that ! What a relief. Thanks for your outstanding support.

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=12020&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=12020&image=2&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=12020&image=3&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' />

It has taken me some time to get round to testing this (work... work...) but I don't seem to be able to get it to work. I have implemented the code as you suggest using "as" plus alias but when you complete a search specifying two different values for lang1 and lang2 and ALL CONDITIONS it returns NO RECORDS FOUND (There are records in there!)
e.g.

UNDERLYING TABLE BEING SEARCHED:

DETAIL TABLE

ID...FOREIGN ID...LANGUAGE.......FIELD4....FIELD5.....FIELD6

22....1.............FRENCH.......BLA.......BLA.......BLA

23....1.............GERMAN.......BLA.......BLA.......BLA
EDIT SQL QUERY:

select `adhoc_id`,

`userid`,

`adhoclastupdated`,

`adhocadmin_update`,

`adhoclang` as `adhoclang1`,

`adhoclang` as `adhoclang2`,

`adhocactpass`,

`ahdailyrate`,

`ahhourlyrate`,

`court`,

`nrpsi`

From `ling_adhoc`
ADVANCED SEARCH:

...

adhoclang1 CONTAINS FRENCH

adhoclang2 CONTAINS GERMAN

...
RETURNS NO RECORDS FOUND.
Help? Am I doing something wrong? If I change the advanced search to any condition then it returns records but they would then be listed like this:
ID...FOREIGN ID...ADHOCLANG1.......ADHOCLANG2....FIELD5.....FIELD6

22....1..................FRENCH...................FRENCH...........BLA.......BLA

22....1..................GERMAN..................GERMAN...........BLA.......BLA
I would like to arrive at a listing like this:
ID...FOREIGN ID...ADHOCLANG1.......ADHOCLANG2....FIELD5.....FIELD6

22....1..................FRENCH...................GERMAN...........BLA.......BLA
By the way this is using the latest release version 3.11. Thanks in advance for any insights...

Andy,
I guess you use AND search while your logic requires use of OR.

You can change search type right on the advanced search page.

Hi Sergey
I tried that but that just has the effect of picking out linguists with either of the two languages listed and not necessarily both.

Is there any way of searching a detail table

where language = search criterion A OR search criterion B but where foreign key is the same for both records!
Does that make sense?
Thanks for your input. It is appreciated.

Andy,
I see what you saying.
The List page displays records from the database table.

There is no records in your table where ADHOCLANG1 is FRENCH and ADHOCLANG2 is GERMAN, so the listing you are talkin about is impossible without modifying the List page.
I can recommend you to remove ADHOCLANG2 field from the List page to avoid the confusion.

Hi Alexey
Thanks. I shall do that but is there any way of doing a search and then a search within that search?

Do I need to learn how to use subqueries and if so how do you interface subqueries into the Advanced Search page?
Many thanks in advance for your insights and pointers.

I have managed to work out what the MYSQL query should be, which searches my detail table for the same field that occurs twice:

(This query is not part of PHPRunner but works directly on the MYSQL database)
This effectively queries the detail table once for the search criterion:

where `adhoclang` = "English"

and then searches within this refined data set for the search criterion:

WHERE `adhoclang` = "French"
GREAT! Problem is I don't know how to integrate it into phprunner so that instead of "English" and "French", I have variables so that I can search for any language combination.
Can any one help? (Please) <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=13304&image=1&table=forumreplies' class='bbc_emoticon' alt=':blink:' />

Andy,
you won't be able to integrate this query into generated PHP application without extensive modifications.
You can try the following:

1. Modify SQL query to include two language fields:
   select
   
   `FOREIGN ID`,
   
   `LANGUAGE` as `language1`,
   
   `LANGUAGE` as `language2`,
   
   from `DETAIL TABLE`
   
2. Modify ..._list.php page to add DISTINCT clause to SQL query generated by Advanced Search page.