SQL syntax error when getting data for a url

Question

I have the following SQL script:

$query = $wpdb->prepare(
    "SELECT user_login FROM wpdb->staff as staff
      join wpdb->users as users on staff.wp_user_id  = users.ID
       where full_name =%s",
    $usernametest
);
$userlogin = $wpdb->query( $query );

But I am getting the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '->ab_staff as staff join wpdb->users as users on staff.wp_user_id = users.' at line 1

Answer 1

Are you sure you didn't mean $wpdb->staff? By ommitting the $, the query now looks for a table called wpdb->staff, which isn't valid SQL

As an aside, had you considered using a custom post type/user meta instead of a custom table? Or even a custom taxonomy on users?

Note as well that if you never set $wpdb->staff, it won't exist, so you'd be better using the table prefix combined with a hardcoded table name