I have been trying everything to achieve this task, but it is rather difficult.

I am trying to do this in Wordpress as shortcode.
The shortcode itself is fine, I don't need any help with that, but just the output here which is giving me a difficult time. The Shortcode has an attribute that can be used to show the ID of the list using $listID

As an example, I have 3 database tables.
complete (variables: id, itemID, listID, userID)
item (variables: id, listID, description, creator)
list (id, name)

What I need to do is show a list from a shortcode variable, in this case database id 1 for the list. Then show the items in that list (using listID in item variable as well as wordpress user function to grab the ID of the logged in user and compare it to the creator variable of the item) -- and have the completed marked in there (using itemID, listID and the logged in user to wordpress to compare with the userID).

I tried this for SQL, but it returned nothing

global $wpdb;
$q_checked = $wpdb->prefix.'checked';
$q_item = $wpdb->prefix.'items';
$q_list = $wpdb->prefix.'list';
$q_results = $wpdb->get_results("
SELECT * FROM (($q_item INNER JOIN $q_list ON $q_item.listID = $listID)
INNER JOIN $q_checked ON $q_list.id = $q_checked.listID;

I also tried this with sql but it only shows from the two tables and not the third, and it will show all instead of excluding the completed.

$q_items = $wpdb->prefix.'items';
$q_checked = $wpdb->prefix.'checked';
$q_result = $wpdb->get_results("SELECT $q_items.title, $q_checked.userID FROM $q_items INNER JOIN $q_checked ON $new_items.id = $q_checked.itemID");

I thought about using a foreach, but none of the above would work well with that would it? Since you can only use one result. Maybe if I could do 2 separate foreach, 1 for the items in the list but exclude them if the id of the item matches the itemID in the completed database? Then do another foreach that would show the completed items for that list.

 <?php foreach($q_result as $i ) {
    $userID = $i->userID;
    $itemNAME = $i->title; ?>
    <?php if($userID === ''.$current_user->ID.'') { ?> <?php echo $itemNAME; ?><?php }?>
<?php }; ?>

I honestly think I should rethink the entire thing. Maybe I am overcomplicating it?

CodePudding user response：

This is a fairly standard MySQL query:

SELECT i.description, i.creator,
       IF(c.id IS NOT NULL, 'Completed', 'Not Completed') AS status
FROM item i
LEFT JOIN complete c
    ON c.itemID = i.id AND
       c.listID = i.listID AND
       c.userID = ?
WHERE i.listID = ?

The ? placeholders represent parameters that you would bind to the current user ID and list ID supplied by your shortcode. This performs a left join from items to completed items, and checks for a match on all join conditions. If a match is found, then a row will exist in c, and we mark the item as completed. Otherwise, the c.id will be NULL and we mark it as not completed.