This topic is locked
[SOLVED]

 Using lookup wizard to display info from other tables

8/24/2006 6:23:01 PM
PHPRunner General questions
jwoker author

I am using the lookup wizard to display information from other tables. My problem is that I would like to use t1.userid to show t2.firstname and then use t1.userid again to show t3.haircolor but using the lookup wizard i can only use t1.userid once.
My thought was to try using an alias in the sql query like SELECT `userid`,`userid` as `userd2`, `phone`, ... FROM t1 But that syntax doesn't work
Any help would be appreciated

J
Jane 8/25/2006

Hi,
you query looks right.

Please give me more detailed description of what you need to achieve and full query with tables structure.

jwoker author 8/25/2006

Here is the query that I got to work:
select `mdl_user_students`.id,

`mdl_user_students`.userid,

`mdl_user_students`.course,

`mdl_user_questionairedata`.id as `questionid`,

concat(`mdl_user_questionairedata`.purpose,' ',`mdl_user_questionairedata`.purposeother) as `qpurpose`,

`mdl_user_questionairedata`.purpose as 'editpurpose'

From `mdl_user_students` inner join `mdl_user_questionairedata`

ON (`mdl_user_students`.userid=`mdl_user_questionairedata`.userid)
I decided to use concat in the query rather than using lookup wizard for display.
Now my question is how can I Edit editpurpose which is one of the joined fields?
Then mdl_course is the master table to mdl_user_students and the list link from mdl_course to mdl_user_students works fine but the reciprocal link on the list page from mdl_user_students gives me the following error "Column: 'id' in field list is ambiguous" the query on the error page is:

select `id`, `fullname`, `shortname`, `capacity` From `mdl_course` WHERE ('mdl_course.id' != '1') and (`mdl_course`.`id`=6)

J
Jane 8/28/2006

Hi,
please see my answers below:

  1. it's impossible to edit joined field.

    Here is article about it:

    http://www.xlinesoft.com/phprunner/docs/us...sql_queries.htm

    You can create new field (`purpose1`) in the mdl_user_students table where purpose info is stored.

select `mdl_user_students`.id,

`mdl_user_students`.userid,

`mdl_user_students`.course,

`mdl_user_questionairedata`.id as `questionid`,

concat(`mdl_user_questionairedata`.purpose,' ',`mdl_user_questionairedata`.purposeother) as `qpurpose`,

`mdl_user_questionairedata`.purpose as `editpurpose`,

`mdl_user_students`.`purpose1`

From `mdl_user_students` inner join `mdl_user_questionairedata`

ON (`mdl_user_students`.userid=`mdl_user_questionairedata`.userid)



Then proceed to the Choose fields tab, for `purpose1` field select following checklists: Add, Edit, correspondingly for `editpurpose` field: List, Search, Adv Search, View, Printer, Export.

Proceed to Formatting tab and for `purpose1` field in Edit as settings dialog select Lookup wizard and set all reqired settings.
2. it seems that there is an error in your query. You should use tickles instead of single quotes:

select `id`, `fullname`, `shortname`, `capacity` From `mdl_course` WHERE (`mdl_course`.`id` != 1) and (`mdl_course`.`id`=6)

jwoker author 8/29/2006

2. it seems that there is an error in your query. You should use tickles instead of single quotes:



I fixed the tickles but am still getting the following error:

Error type 256

Error description Column: 'id' in field list is ambiguous

URL .../backend_dev/mdl_course_list.php?masterkey=11

Error file .../backend_dev/include/dbconnection.php

Error line 26

SQL query select `id`, `fullname`, `shortname`, `capacity`, `reportperiod` From `mdl_course` WHERE (`mdl_course`.`id` != 1) and (`mdl_course`.`id`=11)
As I stated before the link from mdl_course to mdl_user_students but from the mdl_user_students list page the recipricol link to mdl_course produces the error above.

J
Jane 8/29/2006

please zip and send to support@xlinesoft.com a full set of generated PHP files, your database creation script with sample data.

I'll find what's wrong with your project running it on my test box.

jwoker author 8/29/2006

please zip and send to support@xlinesoft.com a full set of generated PHP files, your database creation script with sample data.

I'll find what's wrong with your project running it on my test box.


I sent files with a partial dump of my database. Thanks for your help