Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Join on form

Post Reply
chrisb
Posts: 12
Joined: Mon Oct 29, 2012 5:18 am

Join on form

Unread post by chrisb »

I'm migrating to nuBuilder because I want an open source cloud replacement for open office. Right now, I'm having problems with joins on forms I've created using the form wizard. The following SQL query works fine in phpMyAdmin. (I've tried a left join also)

SELECT *
FROM judge
JOIN school
ON judge.id_school = school.id

When I use it in the SQL box on the form page, I get an SQL error page that goes on forever. Is there a reason the query works in phpMyAdmin, but in the nuBuilder it doesn't.

I'm trying to set this up so I can use columns other than the primary ID for browse columns and for other reporting/display needs
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Join on form

Unread post by admin »

chrisb,

I'm not sure of the answer but you will probably find it easy to break nuBuilder if you don't base all of your Forms on using primary keys.

Maybe posting a screen shot might help.

Steven
chrisb
Posts: 12
Joined: Mon Oct 29, 2012 5:18 am

Re: Join on form

Unread post by chrisb »

I've got the form part figured out now. The look UPS work great. My end users are going to be high school students and things need to be really simple and direct. On the browse screens, I'd like to display columns from other tables. My FKs are numbers because that's what I've always usedand been taught. But I'd like to generate browse screens that can display information based on a join. The sql query above joins twotables i'd like to display as one browse screen so information from the second joined table shows up beside my FK numbers.
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Join on form

Unread post by admin »

chrisb,

You can display any fields from joined tables on the Browse Form.

I don't think I understand your question.

Sometimes if you include screen captures it may help.

Steven
alejandrodgb
Posts: 1
Joined: Tue Jan 13, 2015 5:18 am

Re: Join on form

Unread post by alejandrodgb »

Admin, I have the same question as Chris B. Although I understand you can do it, when I insert the SQL statement below:
SELECT states_id, states_name FROM states JOIN campaigns ON states.states_id=campaigns.camp_state_id;

in the Display section of the Browse tab I get the following error:

===USER========
globeadmin
===PDO MESSAGE=====
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT states_id, states_name FROM states JOIN campaigns ON states.states_id=cam' at line 1
===SQL=======
SELECT campaigns_id,IFNULL(camp_name,''),IFNULL(SELECT states_id, states_name FROM states JOIN campaigns ON states.states_id=campaigns.camp_state_id;,'') from campaigns
===BACK TRACE=====
/var/www/html/nuapi.php - line 2019 (nuRunQuery)
/var/www/html/nuapi.php - line 1695 (nuGetBrowseRecords)
/var/www/html/nuapi.php - line 188 (nuGetBrowseForm)

In my case I want to display Campaign Name, State in the browse screen of the campaigns table. This table has the following structure: campaigns_id, camp_name, camp_state_id where camp_state_id is the PK of the states table which format is: states_id, states_name. Currently I am displaying the State as numbers (FK) and I want it to query the name from the states table.

Currently:
Campaign Name | State
Brisbane | 1
Sydney | 2

What I want to do:
Campaign Name | State
Brisbane | QLD
Sydney | NSW
massiws
Posts: 503
Joined: Thu May 24, 2012 2:08 am
Location: Milan, Italy
Contact:

Re: Join on form

Unread post by massiws »

alejandrodgb, your have write correctly the SQL statement in Campaign form:
General tab on Campaign form
General tab on Campaign form
General_tab.png (17.8 KiB) Viewed 15213 times
Browse tab on Campaign form
Browse tab on Campaign form
Browse_tab.png (24.62 KiB) Viewed 15213 times
Hope this helps,
Max
Post Reply