1

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?

2 Answers 2

3

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

5
  • Thanks @Lodder. I made the corrections but I'm still getting a white page. I now have Error reporting on maximum, but it's not showing anything. In Firefox developer tools I just get a 500 error notification. The link you sent is the page I was looking at this morning, but none of the examples explain how to create a new variable.
    – Gillian
    Feb 10, 2023 at 18:12
  • Commenting out $db->setQuery($query); means that I now see two PHP warnings: "foreach() argument must be of type array|object, int" and "Array to string conversion..." on the line $query->where($db->quoteName($articleId) . ' = ' . $db->quoteName('f.f_id'));. For the first warning, I added 'array' to this line: $query->select($db->quoteName(array('f.featured_up'))); but it made no difference to the warning. For the second warning, there can be 'Null' values in either or both 'featured_up' and 'featured_down' columns if no date has been entered. So how do I get around that?
    – Gillian
    Feb 11, 2023 at 14:18
  • I thought I might try reassigning ``$this``` to reference a database view called #__content_with_featured that I had created, which contains all the #__content columns as well as the #__content_frontpage data, but I got a red Fatal error page instead.
    – Gillian
    Feb 11, 2023 at 14:47
  • @Lodder does leftJoin('INNER' make sense? Is it an INNER JOIN or a LEFT JOIN? leftJoin() only accepts a single string argument. LIKE should not be used without wildcards -- if no wildcards, just use =. If $articleId is an integer, then it should not be quoted by quoteName(). It could be cast as an int or quoted by quote(). Feb 11, 2023 at 21:58
  • @mickmackusa You're right...didn't even notice that. leftJoin->('INNER' is incorrect.
    – Lodder
    Feb 11, 2023 at 22:32
1

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");
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.