How to show Real Name of User ID by INNER JOIN

How to show Real Name of User ID by INNER JOIN

Postby Ion2 » Fri Jan 22, 2016 12:36 am

Hi,

I tried to change how content is displayed in the Browse Form similar to this video: http://www.youtube.com/watch?v=Wlob5Fmxzrs&t=0m15s

I used the following code:
Code: Select all
IF(inventory_log_changed_by = '56a0ed05577939c', 'Name of Changer')

The error message was:
ChangeDisplay.png
ChangeDisplay.png (53.72 KiB) Viewed 3080 times


Then I tried the code from the Wiki: http://wiki.nubuilder.net/nubuilderv3/index.php/Forms#Display Example 2

The code was:
Code: Select all
DATE_FORMAT(inventory_log_changed_at,'%m-%d-%Y')

The error message was as shown above.

What am I doing wrong? What is the easiest way to show the real name of the Changer?

Greetings,
Timo
Last edited by Ion2 on Tue Jun 21, 2016 4:36 pm, edited 1 time in total.
Ion2
 
Posts: 41
Joined: Mon Jan 18, 2016 11:31 pm

Re: Customise Display

Postby admin » Mon Jan 25, 2016 2:17 pm

Ion2,

Your SQL statement will need to inner join to the zzzsys_user table to get the name.

Steven
admin
Site Admin
 
Posts: 3226
Joined: Mon Jun 15, 2009 9:53 am

Re: Customise Display

Postby Ion2 » Tue Jun 21, 2016 4:29 pm

Actually I had heavy problems getting this inner join to work. You can make inner joins in multiple ways. This was the only one that worked:

Code: Select all
SELECT inv.*
     , log_added_by_name
     , log_changed_by_name
  FROM inventory inv
       LEFT JOIN (SELECT zzzsys_user_id, sus_name AS log_added_by_name FROM zzzsys_user) zu1
          ON inv.inventory_log_added_by = zu1.zzzsys_user_id
       LEFT JOIN (SELECT zzzsys_user_id, sus_name AS log_changed_by_name FROM zzzsys_user) zu2
          ON inv.inventory_log_changed_by = zu2.zzzsys_user_id
order by inventory_log_changed_at DESC

I hope it can be helpful for somebody.

Greetings,
Timo
Ion2
 
Posts: 41
Joined: Mon Jan 18, 2016 11:31 pm

Re: How to show Real Name of User ID by INNER JOIN

Postby admin » Fri Jun 24, 2016 11:36 am

.
admin
Site Admin
 
Posts: 3226
Joined: Mon Jun 15, 2009 9:53 am


Return to General