Question related to JOINS

Gstg

I've read the section on joins:

https://editor.datatables.net/manual/php/joins#Table-aliases

And tried to do what I needed, but I'm not having any luck. What I'm trying to do is to use an active control inside of DataTables, to mark a record as a "Favorite", similar to:

https://editor.datatables.net/examples/api/checkbox.html

The main DataTable is "Profiles", and the current user is stored in $_SESSION['user']. There is a DataTable called "Profiles_Following" that has the primary key as user_id & following_user_id. We create a record in this file when this user selects a person to follow, and the record is deleted when the following active control is unchecked. All of this is working. The part I can't seem to get working is that while I'm reading the "Profiles" files, I need to read the "Profiles_Following" table (JOIN) to turn on the active control in the table to indicate that this is a "favorite" or not. If there is a record it needs to turn on, if not leave the active control turned off.

You can see this in the picture attached.

I tried doing the following code, but it doesn't seem to be working. Any help would be greatly appreciated. Thanks 8-)

session_start();

$userID=$_SESSION['user'];

/*
 * Editor server script for DB table profiles_prof_subj
 * Created by http://editor.datatables.net/generator
 */

// DataTables PHP library and database connection
include( "Editor-PHP-2.0.5/lib/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

if (isset($_POST['action'])) {
    echo '{}';
    return;
}

// --------------- SET ENCRYPTION - if not already set  -----------------------
$encr_iv=$_SESSION['encr_iv'];
$encr_key=$_SESSION['encr_key'];
// ---------------------------------------------------------------------------------

// Build our Editor instance and process the data coming from _POST
 Editor::inst( $db, 'profiles', 'user_name' )
    ->fields(
        Field::inst( 'profiles.user_name' ),
        Field::inst( 'profiles.ans_rating' ),
        Field::inst( 'profiles.introduction' ),
        Field::inst( 'profiles.ext_introduction' ),
        Field::inst( 'profiles.rate' ),
        Field::inst( 'profiles.ans_num' ),
        Field::inst( 'profiles.ans_num_reviewed' ),
        Field::inst( 'profiles.languages' ),
        Field::inst( 'profiles.key_words' ),
        Field::inst( 'profiles.pic_link' ),
        Field::inst( 'profiles.status' ),
//              Field::inst( 'profiles_following.status' ),
                Field::inst( 'profiles.wp_user_id' )->getFormatter( function ( $val, $data ) use ($encr_key, $encr_iv) {
                         return urlencode(openssl_encrypt($val, "AES-256-CBC", $encr_key, 0, $encr_iv));
                         } ),
                Field::inst( 'profiles.user_id' )->getFormatter( function ( $val, $data ) use ($encr_key, $encr_iv) {
                        return urlencode(openssl_encrypt($val, "AES-256-CBC", $encr_key, 0, $encr_iv));
                        } )
      )
      ->leftJoin( 'profiles_following',   'profiles_following.user_id=$userID and profiles_following.following_user_id=profiles.user_id')
//    ->leftJoin( 'sites', 'sites.id = users.site' )
    ->where('status', "Unavailable", '!=')
    ->where('status', "Pending", '!=')
   })
    ->process( $_POST )
    ->json();

Thanks again for any thoughts.

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

When you say it isn't working - in what way? Are you getting zero results in the table, or are no items being marked as being followed, or something else? I'm wondering if profiles_following.user_id=$userID should be a WHERE condition? Or is this a table of all profiles rather than just for one person?

Allan