2. PHPRunner General questions
  3. ambiguous WHERE clause with master-detail
This topic is locked

ambiguous WHERE clause with master-detail

 		12/29/2005 9:20:18 PM
PHPRunner General questions
T
 TheSaint author

We created a master-detail form. The master part has a custom sql with a join across several tables. Some of the column names are common so we qualify the sql with table names as usual. Works fine, but when you click on the detail link it fails with an "ambiguous" error because your code appends a WHERE to the master sql but does not qualify the column names with the table name. In this case a common column name such as "id" is ambiguous.
Any workaround and can you qualify the "where" clause you append for detail links in the next release?
ST
Admin 12/30/2005

Hi,
please try using this workaround.

Locate the following snippet in your detailtable_list.php file

if($masterkey)

{

$mastertableurl="...";

$mastertablename="...";
$where = AddFieldWrappers("id")."=".make_db_value(RemoveFieldWrappers("id"),$masterkey);

$strSQL = AddWhere($strSQL,$where);

}



and modify it whis way:

if($masterkey)

{

$mastertableurl="...";

$mastertablename="...";
$where = "detailtable.id=".make_db_value(RemoveFieldWrappers("id"),$masterkey);

$strSQL = AddWhere($strSQL,$where);

}


where detailtable is your actual details table.
T
 		TheSaint author 12/30/2005

Hi,
Thanks for pointing me there. As expected, qualifying the "where" works perfectly. Would you incorporate that fix in the new version of PHP runner, PLEASE that would enable us to use joins everywhere in custom sql without worrying that a current (or FUTURE!) column somewhere is ambiguous.

Hi,

please try using this workaround.

Locate the following snippet in your detailtable_list.php file
and modify it whis way:
where detailtable is your actual details table.
Admin 1/5/2006

Hi,
we fixed this issue in new PHPRunner 3.0 beta.

http://www.asprunner.com/files/phprunner30b.exe
Please try it out.
T
 		TheSaint author 1/23/2006

The beta is an improvment but broke some working programs. Now you qualify each name in the "select statement" with the table name. But when you qualify with the "WHERE" clause when searching, you always

use the main table name, so when searching on a column that does not belong to the main table, it fails.
Example:
Select table1.foo1 , table2.foo2 from table1 , table2 WHERE table1.id = table2.id
In this case, trying to use "foo2" for search fails.

Hi,

we fixed this issue in new PHPRunner 3.0 beta.

http://www.asprunner.com/files/phprunner30b.exe
Please try it out.
Admin 1/24/2006

Hi,
there is no way for PHPRunner to determine which table the foo2field belongs to.

However you can specify it manually.
Please modify GetFullFieldNamefunction in include\dbcommon.php file.

Replace this line:

if("table1"==$table && $field=="foo2") return "`table1`.`foo2`";



with the following:

if("table1"==$table && $field=="foo2") return "`table2`.`foo2`";