How do I create a new PHP variable for a blog override from the result of JDatabase join query?

Question

In a category blog override I am attempting to reflect the featured status of an article. In order to find out whether it is currently actively featured - i.e. not 'pending' or 'expired' I am querying the #__content_frontpage table where this information is held in the 'featured_up' and 'featured_down' columns. My intention is to use the two answers to create PHP if statements based on the dates and apply CSS accordingly.

In my blog_item.php override I have added this variable: $articleId = $this->item->id;

Then, attempting to follow the instructions on the 'Selecting_data_using_JDatabase' page in Joomla docs, I have added this code firstly for the 'featured_up' field/column:

$query = $db->getQuery(true);
$query->select($db->('f.featured_up'));
$query->from($db->quoteName('#__content_frontpage', 'f'));
$query->leftJoin('INNER' $db->quoteName('#__content', 'c') . ' ON ' . $db->quoteName('c.id') . ' = ' . $db->quoteName('f.content_id'));
$query->where quoteName($articleId) . ' LIKE ' . $db->quoteName('f.content_id');
$db->setQuery($query);
$result = $db->loadResult();

and then attempted to define a new variable like this: $up = $result;

But, with or without that last line of code I'm just getting a white page.

I tried again without the join:

$query = $db->getQuery(true);
$query->select($db->('f.featured_up'));
$query->from($db->quoteName('#__content_frontpage', 'f'));
$query->where quoteName($articleId) . ' = ' . $db->quoteName('f.content_id');
$db->setQuery($query);
$result = $db->loadResult();

Still no luck. What am I missing?

Answer 1

Your code contains a few errors, which is why you're getting a white page.

1) $db->('f.featured_up') needs to be replaced with $db->quoteName('f.featured_up')

2) You're missing a comma (,) after leftJoin('INNER'

3) Your WHERE clause is completely broken. Try this:

$query->where($db->quoteName($articleId) . ' = ' . $db->quoteName('f.content_id'));

In your Joomla Global Configuration, enabling error reporting will show you any PHP errors that may arrise from your code.

Might also be worth taking a look at the Joomla documentation for database queries

Answer 2

Firstly: the reason for the white page errors was that I was putting the code into the wrong file. Instead of adding it to blog_item.php it should be in blog.php. It worked once I'd moved the code.

Secondly, I then realised I didn't need the code in the blog at all (Joomla handles the display of featured/not featured/both in blogs just fine), I needed it in the actual full article view (reason at the bottom).

So, if anyone else wants to know how to do this - i.e. indicate whether an article is actively, currently featured, not pending or expired - this is what I did:

In phpMyAdmin, I created a view with SQL, combining #__content and #__content_frontpage as well as adding a new column:

CREATE OR REPLACE VIEW #_content_with_featured AS 
SELECT #_content.id AS f_id,#_content.title,#_content.featured,#_content_frontpage.featured_up,#_content_frontpage.featured_down,
(CASE
WHEN #_content.featured = '0' THEN 'no'
WHEN #_content.featured = '1' AND #_content_frontpage.featured_up > NOW() THEN 'no'
WHEN #_content.featured = '1' AND #_content_frontpage.featured_down < now() THEN 'no'
WHEN #_content.featured = '1' AND #_content_frontpage.featured_down IS NULL AND #_content_frontpage.featured_up < NOW() THEN 'yes'
WHEN #_content.featured = '1' AND #_content_frontpage.featured_up IS NULL AND #_content_frontpage.featured_down > NOW() THEN 'yes' 
WHEN #_content.featured = '1' AND #_content_frontpage.featured_up IS NULL AND #_content_frontpage.featured_down IS NULL THEN 'yes'
END)
AS f_current
FROM #_content
LEFT JOIN #_content_frontpage ON #_content.id = #_content_frontpage.content_id

Note:

1. In your database query you need to use the table prefix instead of the # as you may have more than one table prefix in your database.
2. Using 'OR REPLACE' in the first line allows you to tweak and re-run the query, which overwrites the previous view, in case you want to add or remove further columns.
3. Using a view means that any additions/deletions/modifications to the original two tables are dynamically updated. This would not happen if you created a new table instead. Additionally, if you name the view with the same prefix as your database tables, this enables you to query the view in Joomla.
4. In Joomla, you don't need to use the table prefix as Joomla knows the prefix as it's in your Global Configuration.

I changed this code in an article override html/com_content/article/default.php on line 54:

    <div class="page-header">

to:

<div class="page-header cf-<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName('f_current'))
    ->from($db->quoteName('#__content_with_featured'))
    ->where($db->quoteName('f_id') . " = " . $articleId);
$db->setQuery($query);
$result = $db->loadResult(); 
      echo $result;
?>">

which produces a new line of code that looks like this:

<div class="page-header cf-no">

or

<div class="page-header cf-yes">

The reason for doing this? My client has two menu items for events using the same category. One for future events - which are currently featured - and one for past events with reports added to the original articles but whilst still technically 'featured', they are 'expired'. Because they are all the same category, when clicking through to a full article display, they all have the same category root. So, while the blog items' URLs might be /diary/future and /diary/reports, all the articles go to /diary/future/[article]. I wanted article category modules (one listing future events and the other listing event reports) to be displayed in the sidebar on the article page depending on whether the article being viewed was a future event or a report. As all the articles are classed as featured, even if they are pending or expired, then I couldn't use the 'featured' field from #__content to control the display. I needed it based on the 'featured_up' and 'featured_down' dates as well.

I added both modules to the /events/future menu item and added a class of 'events' to the menu item. The 'future' module had a module class of 'future' added and the 'reports' module had a class of 'reports' added. Then the display (or not) of either sidebar module on the article pages is controlled with a little bit of JavaScript, which also hides the modules on the parent blog items:

if (document.querySelector(".events")) {
    let blogModOne = document.querySelector(".rightsidebar");
    if (document.querySelector(".blog-items")) {
        blogModOne.classList.add("d-none");
    }
    else if ((document.querySelector(".item-page")) && (document.querySelector(".featured-0"))) {
        let modOne = document.querySelector(".sidebarright.future");
        modOne.classList.add("d-none");
    }
    else if ((document.querySelector(".item-page")) && (document.querySelector(".featured-1"))) {
        let modTwo = document.querySelector(".sidebarright.reports");
        modTwo.classList.add("d-none");
    }
}