CurrentUserName() in Custom Field SQL

[eduaguiar]

I created a custom field to measure the distance from where the user is to a customer's home. However, I need to include the user's username in the query. I'm using <?php echo CurrentUserName() ?> and it doesn't work.

((SELECT 
6371 * 2 * 
	ASIN(
		SQRT(
        POWER(SIN((RADIANS(LocalizacaoLog.Latitude) - RADIANS(SUBSTRING_INDEX(SUBSTRING_INDEX(lideranca.geo, ',', 1), '(', -1))) / 2), 2) +
        COS(RADIANS(SUBSTRING_INDEX(SUBSTRING_INDEX(lideranca.geo, ',', 1), '(', -1))) * COS(RADIANS(LocalizacaoLog.Latitude)) *
        POWER(SIN((RADIANS(LocalizacaoLog.Longitude) - RADIANS(SUBSTRING_INDEX(SUBSTRING_INDEX(lideranca.geo, ',', -1), ')', 1))) / 2), 2)
		)
    
	) 

FROM LocalizacaoLog
WHERE LocalizacaoLog.Usuario = '<?php echo CurrentUserName() ?>'
LIMIT 1
))

When I use Administrator, it works. However, I need to know the distance from the user who is logged in.

((SELECT 
6371 * 2 * 
	ASIN(
		SQRT(
        POWER(SIN((RADIANS(LocalizacaoLog.Latitude) - RADIANS(SUBSTRING_INDEX(SUBSTRING_INDEX(lideranca.geo, ',', 1), '(', -1))) / 2), 2) +
        COS(RADIANS(SUBSTRING_INDEX(SUBSTRING_INDEX(lideranca.geo, ',', 1), '(', -1))) * COS(RADIANS(LocalizacaoLog.Latitude)) *
        POWER(SIN((RADIANS(LocalizacaoLog.Longitude) - RADIANS(SUBSTRING_INDEX(SUBSTRING_INDEX(lideranca.geo, ',', -1), ')', 1))) / 2), 2)
		)
    
	) 

FROM LocalizacaoLog
WHERE LocalizacaoLog.Usuario = 'Administrator'
LIMIT 1
))

[arbei]

You cannot insert PHP code in the custom field expression, the SQL cannot be executed by your database. However, you may add a place holder in your SQL, e.g. ... xxx = '{CurrentUserName}' ..., then the database will treat it as string and will be able to execute it. During runtime, you may use Table_Load server event and the setSqlSelect() method to replace the '{CurrentUserName}', e.g.

$this->setSqlSelect(str_replace('{CurrentUserName}', CurrentUserName(), $this->getSqlSelect()))

Note: This is the idea only, do not just copy and paste, modify the approach as you need.

[DavidLi]

I use it very simply like this:

$currentUserName = CurrentUserName();
$sql = "SELECT ............
WHERE LocalizacaoLog.Usuario = '{$currentUserName}'";
$res = ExecuteScalar($sql);

I then display the result in table field using Server Events - Table specific - Row Rendered
it's such a simple non-programming solution
Hope to help

[arbei]

Note that the original post was about Custom Field, PHP variable cannot be used in the SQL expression for the custom field.