Accessing the WordPress user ID in SQL

WP Data Access injects a session variable containing the current WordPress user ID into every database connection. This enables users to leverage the WordPress user ID directly within their SQL queries, database views, triggers, functions, and stored procedures.

To test this feature in the Query Builder, execute the following SQL statement:

select @wpda_wp_user_id

This query will return your current WordPress user ID.

Availability

The @wpda_wp_user_id session variable is available on all database connections managed by WP Data Access. It is compatible with both local and remote databases, including connections to external systems like SQL Server or MS Access via a proxy.

Usage

Using the Variable in Queries

The WordPress user ID is accessible in any SQL query:

select * 
from wpda_sas_student
where student_wp_user_id = @wpda_wp_user_id

Using the Variable in Default WHERE Clauses

You can also incorporate the variable into your default WHERE clauses for dynamic filtering.

Database Triggers, Procedures and Functions

The following example demonstrates how to store the WordPress user ID in the student_wp_user_id column automatically using triggers on the wpda_sas_student table.

✨ Example Insert Trigger

create trigger if not exists wpda_sas_student_insert before insert on wpda_sas_student
for each row
begin
	set new.student_wp_user_id = @wpda_wp_user_id;
end;
/

✨ Example Update Trigger

create trigger if not exists wpda_sas_student_update before update on wpda_sas_student
for each row
begin
	set new.student_wp_user_id = @wpda_wp_user_id;
end;
/

Using the session variable in stored procedures and functions follows the same principle.

Views

📌 IMPORTANT You cannot use session variables directly in a view definition using plain SQL. The following example will not work:

create view v_error as
select * from wpda_sas_student
where student_wp_user_id = @wpda_wp_user_id

📌 WORKAROUND To overcome this limitation, you can create a database function that returns the session variable's value.

Create the function wpda_get_wp_user_id using the Data Explorer:

• Open the Data Explorer.
• Click the Databases icon on the toolbar.
• Select your target database.
• Click the power icon.

📌 This is a one-time action required for each database where you need this functionality.

Once the function is created, you can use it successfully in a view:

create view v_access_wp_user_id as
select * from wpda_sas_student
where student_wp_user_id = wpda_get_wp_user_id()

Limitations

• The @wpda_wp_user_id session variable is only available within connections made by WP Data Access. Developers can access it from custom code using the plugin API.
• The wpda_get_wp_user_id() function can be called from external database tools. If the session variable is not set, the function will return NULL.
• The wpda_get_wp_user_id() function must be created separately for each database where it is needed.

Need More WordPress Values in SQL?

WP Data Access provides the @wpda_wp_user_id variable and wpda_get_wp_user_id() function. You can easily expose other WordPress values, such as a user's login name, using the wpda_dbinit hook.

The following PHP code snippet makes the current user's login available via the session variable @wpda_wp_user_login:

add_action(
	'wpda_dbinit',
	function( $wpdadb ) {
		if ( null !== $wpdadb ) {
			$suppress_errors = $wpdadb->suppress_errors( true );
			$current_user    = wp_get_current_user();
			$wpdadb->query( 'set @wpda_wp_user_login = "' . $current_user->user_login . '"' );
			$wpdadb->suppress_errors( $suppress_errors );
		}
	},
	10,
	1
);