2. PHPRunner General questions
  3. Search error
This topic is locked

Search error

 		2/2/2006 11:39:59 AM
PHPRunner General questions
F
 frocco author

Hello,
When I try to search on comments, I get this error.
Unknown column 'detail.Comments' in 'where clause'
Here is the select
SELECT `master`.`PO`, `master`.`Vendor`, `master`.`Project`, `master`.`Dept`, `master`.`Requisitioned`, `master`.`Ordered`, `master`.`User`, `master`.`Comments`, `detail`.`Received`, `detail`.`Qty`, `detail`.`Equipment`, `detail`.`date_installed` , `detail`.`item_type`, `detail`.`completed`, `master`.`completed` AS `completed1` FROM `master` INNER JOIN `detail` ON (`master`.`id` = `detail`.`id`) where upper(`detail`.`Comments`) like upper('%hann%')
Here is my Edit SQL Manually.

SELECT

`master`.`PO`,

`master`.`Vendor`,

`master`.`Project`,

`master`.`Dept`,

`master`.`Requisitioned`,

`master`.`Ordered`,

`master`.`User`,

`master`.`Comments`,

`detail`.`Received`,

`detail`.`Qty`,

`detail`.`Equipment`,

`detail`.`date_installed` ,

`detail`.`item_type`,

`detail`.`completed`,

`master`.`completed` AS `completed1`

FROM `master`

INNER JOIN `detail` ON (`master`.`id` = `detail`.`id`)
Thanks
Frank
Sergey Kornilov admin 2/3/2006

Frank,
to get your pages working please modify GetFullFieldNamefunction in include/dbcommon.php file.
Locate ths line there:

if("details"==$table && $field=="Comments") return "`details`.`Comments`";

and replace it with:

if("details"==$table && $field=="Comments") return "`master`.`Comments`";


Then repeat this for the rest of joined fields.
F
 		frocco author 2/3/2006

Frank,

to get your pages working please modify GetFullFieldNamefunction in include/dbcommon.php file.
Locate ths line there:

and replace it with:
Then repeat this for the rest of joined fields.


Thank you..
PHPRunner is great!
Frank
Z
 		zavka 2/6/2006

Hi
In PhpRunner 2 was possible search fields from inner joined tables. Why it is not possible in version 3 without modification of code. It will be changed?
Sergey Kornilov admin 2/7/2006

Hi,
PHPRunner 2 allowed searching for joined fields without modifications of code.

However this didn't work if these tables had fields with the same names.
With PHPRunner 3.0 you can use any SQL query with any joined and calculated fields with small modification of generated code.

PHPRunner doesn't know which field in the query comes from which table, so you should specify this manually.

I.e. if your query looks like:

select

table1.id,

table1.name,

table2.name as name2,

table1.quantity * table2.price as totalcost

from table1 inner join table2 on table2.id=table1.id



you can modify GetFullFieldName function this way:

if("table1"==$table && $field=="name2") return "table2.name";

if("table1"==$table && $field=="totalcost") return "table1.quantity * table2.price";


and make all the query fields searchable.
You didn't have any chance to use this query in PHPRunner 2.0
We plan to add a query builder into the next PHPRunner version to automate SQL query construction and avoid manual modification of the code.
Z
 		zavka 2/7/2006

Thanks a lot for answer <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=7910&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
W
 		webbiz 2/14/2006

It appears that "== $table" above.
T
 		TheSaint 3/10/2006

We plan to add a query builder into the next PHPRunner version to automate SQL query construction and avoid manual modification of the code.


In the current version when we create a simple multi table query with join, and qualify each column with table name, wouldn't it be fairly easy for you to modify the code to choose the correct table for the search operation?
Example:
Select table1.name , table2.date , table1.age from table1 , table2 WHERE ...
if I click search on "date" it is not ambiguous; you need to qualify the column with "table2" for search...
Thanks - Steve
Sergey Kornilov admin 3/13/2006

Steve,
please ensure that field type is Date in your Database.

Also ensure that you use latest update of PHPRunner. You can download it here:

http://www.asprunner.com/files/phprunner-setup.exe