Foreign Key Display

[Alohajoe5]

Janusz,

Please see the attached screenshot. I get this error about 5 seconds after opening your spreadsheet.

[Janusz]

On my side I can open and edit without necessity to login to google.
Try to open maybe from other browser (Edge or ..) - no need for google account - normally should work.
If not possible to connect - send with any other spredsheet format.

[Alohajoe5]

Janusz,

Sorry, I've attached a picture of a spreadsheet. I couldn't get any other browsers to work with your google doc--it kept kicking me off. Also, I wasn't allowed to upload an xlsx so we're stuck with a screenshot. It's a fairly simple setup--I've entered some dummy data.

Reminder--a query worked in phpMyAdmin that was like this:

Code: Select all

SELECT
t2.AAAA t2.BBBB t2.CCCC t2.DDDD, t2.EEEE, t2.FFFF, t2.GGGG, t2.HHHH, t2.IIII, t2.JJJJ, t2.KKKK, t2.SrcID, t2.DestID, t1.ID, t1.AbbrevName as SourceIDAbbrev, t3.AbbrevName as DestIDAbbrev

FROM
Devices as t1, Channels as t2, Devices as t3

WHERE t2.SrcID = t1.ID and t2.DestID = t3.ID

I think the only difference between this and what I've put in the sample spreadsheet is the alias of "AbbrevName" for the Source and Destination.

[Janusz]

So in my opinion the simplest way is:
1. create view to copy/rename Devices columns in PHPmyadmin (I gave the name vDevices)

Code: Select all

SELECT Devices.ID as vID, Devices.AbbrevName as vAbbrevName FROM Devices

2. Create final query/view linking all 3 tables/views Devices, vDevices, Channels
You can make view in phpmyadmin or paste to nuBuilder (the one from p1 has to be on database level)

Code: Select all

select * from Channels left join Devices on Channels.Source_ID = Devices.ID left join vDevices on Channels.Dest_ID = vDevices.vID

Just for simplicity I was joining full tables but you can limit to any collumns.

There is as well some possibility to combine everything in one querry - but me personally prefer - to have it splitted

https://drive.google.com/open?id=1F2a-B ... fjIwrW5npp
the above view you can create directly with sql commands:

Code: Select all

or with mysql command:
CREATE VIEW vDevices AS SELECT Devices.ID as vID, Devices.AbbrevName as vAbbrevName FROM Devices
CREATE VIEW Results AS select * from Channels left join Devices on Channels.Source_ID = Devices.ID left join vDevices on Channels.Dest_ID = vDevices.vID

If some other people from the forum would have other suggestions please share - it's always interesting to know other possibilities.

[Janusz]

and short movie from trial in any nuBuilder from
https://drive.google.com/open?id=1pV9jk ... c4Qm27cjwt

[Janusz]

and additionally to confirm - your code is working ok in phpmyadmin

Code: Select all

select Channels.*, t1.AbbrevName as SourceIDAbbrev, t2.AbbrevName as DestIDAbbrev
from
Channels, Devices as t1, Devices as t2
WHERE
Channels.Source_ID=t1.ID and Channels.Dest_ID=t2.ID

but in nuBuilder there is following error

Code: Select all

[0] : 
===USER==========

globeadmin

===PDO MESSAGE=== 

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'ID' in field list is ambiguous

===SQL=========== 

SELECT ID,Short_Name,Address,Units
 from Channels, Devices as t1, Devices as t2
 WHERE Channels.Source_ID=t1.ID and Channels.Dest_ID=t2.ID LIMIT 0, 20

===BACK TRACE====

/var/www/html/TestDB/nuform.php - line 898 (nuRunQuery)

/var/www/html/TestDB/nuform.php - line 324 (nuBrowseRows)

/var/www/html/TestDB/nuapi.php - line 42 (nuGetFormObject)

personally I am trying to create all the views (except the very simple one) on database level - and later just to use them in nuBuilder as a table - and never had problem with it - sometimes I have several levels of views before having final data consolidation

[Alohajoe5]

Janusz,

Yeah It's weird. IDK why nuBuilder doesn't like the SQL. It's working fine in the phpMyAdmin but not in nuBuilder. Perhaps admin knows something about how nuBuilder handles the queries that might cause this outcome. The only thing that makes me hesitant to use a View is I believe that limits your ability to edit. I believe in a view, you can only edit one of the tables at a time. Finally, I'm not sure of what the implications would be for my audit-trail triggers.

[Janusz]

Hi,
Concerning tables updates via views for sure there are limitations - as you have several tables connected - and there migth be some ambiguity
https://dev.mysql.com/doc/refman/8.0/en ... ility.html
but you can easilly adjust your forms to update what can be updated and some fields to have in the read only mode - just to display only.
I did not check but for me MariaDB will behave exatly the same way if you will use views or putting the same code inside nuBuilder as all sql requests will be performed anyway by database engine.

Just to summarise - if I have form with data from several tables I am limiting possibility to modify all fields - some fields are read only - and typically I leave possibility to modify just only one table from one form - if I need to do more I have a button opening other forms. As well I am limiting insertion of new records via views. (ex. on the form combining everything I would have a button to Channels Form which will open new form and will be working only on channels table)
If you would leave all fields editable you will see that after change of some fields and save - fields on the form after save will become blank or not changed beacuse Maria DB will not perform requested operation.
In my opion it's not linked to nuBuilder or which place queries are defined - it's linked purelly to mySQL - and by design we should not allow to perform operations with ambiguities.
I am not a person working at all as software developer it's more hobby than profession (just had a need to write DB for the office of my wife ) - and implemeting above rules I know that it is working perfectly without any technical issues or problems of data loss for example - and I am making quite advanced forms and data analysis)

Maybe some other persons from the forum can share as well their experience.

[Janusz]

Please find enclosed one movie from your case:
https://drive.google.com/open?id=1__idT ... VqRbVh8GGY

you will see that on that form you can update only one table at one save operation.
and by form design we should forbid such cases putting some fields as read only

[Alohajoe5]

I seem to be having a problem with nuBuilder not "seeing" the View that I created. I made a view called Combined_Channels_Devices. I just wanted to confirm that I shouldn't have to do anything else? I listed the Primary Key from the Channels table as the Primary ID.