Question related to JOINS
Question related to JOINS
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.
This question has an accepted answers - jump to answer
Answers
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