Please enable JavaScript to view this site.

Navigation: Advanced topics > Events > Sample events > Database

Dynamic SQL Query

Scroll Prev Next More

 

Sometimes you need to present different data to different groups of users. Let's say you run a classified ads board and wish to display the data added over the past 7 days to regular users and all of the ads to the admin.

 

Add the following code to the After table initialized event.

1. For MS SQL Server database:

if (Security::getUserName()!="admin")
  $query->addWhere("DATEDIFF(day,DateColumn, GETDATE()) <= 7");

2. For MySQL database:

if (Security::getUserName()!="admin")
$query->addWhere("DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DateColumn");

3. For MS Access database:

if (Security::getUserName()!="admin")
  $query->addWhere("DATEDIFF('d',DateColumn, Now()) <= 7");

4. If you use Dynamic Permissions, the code should be different (MySQL example).

Note: If the user doesn't belong to the admin group, show only the past 7 days worth of data.

 

if (!Security::isAdmin())
  $query->addWhere("DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DateColumn");

See also:

Method: addWhere

About SQLQuery class

Security API: getUserName

Security API: isAdmin

SQL variables in SQL and Lookup wizards

Dynamic Permissions

 

Created with Help+Manual 7 and styled with Premium Pack Version 3 © by EC Software