0

For a J5 site, using Cassiopeia, I have created (on a test site) a View using SQL in phpMyAdmin (which uses mySQL) that selects * from the content table linked by content.id and fields_values.item_id with this code:

CREATE OR REPLACE VIEW qpnpe_events_content_t1 AS
SELECT
a.*,
b.field_id,
b.value
FROM qpnpe_content AS a
INNER JOIN qpnpe_fields_values AS b ON
CAST(a.id AS CHAR(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci) = (b.item_id COLLATE utf8mb4_unicode_ci)
WHERE b.field_id=1 and b.value>now()
ORDER BY b.value ASC;

(The join also took a lot of working out with the help of my hosting service provider, Krystal, as content.id is an INT and fields_values.item_id is a CHAR).

The ultimate aim is to display the events based on the calendar picker date (i.e. the date of the event) in a blog. In my test site I have just four articles, all in the 'event' category, to which the calendar picker field is assigned and, of those four, only three have a date in the future. The resulting View correctly shows these three articles. The view also has an extra field added, which is the value of the calendar picker, e.g. 2028-02-06 19:00. Being a View, it will update dynamically when new articles are added.

In Cassiopeia, I have created a category blog override and added a database query, but I'm getting the error "0 Call to a member function get() on null" when I view the page.

It may be that I'm putting the query in the wrong place, or the 'load' instruction is wrong, or something else.

I've spent two days on this trying all sorts of different queries, including doing the entire database query from within the blog.php file, rather than in phpMyAdmin first, to no avail. This is the code in the blog.php file at the moment:

    <?php if (!empty($this->intro_items)) : ?>
    <div class="com-content-category-blog__items blog-items 
        <div class="com-content-category-blog__item blog-item events">
        <?php 
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);
        $query->select(["#__events_content_t1.*"])
        ->from("#__events_content_t1")
        ->order("value");
        $db->setQuery($query);
        $row = $db->loadRowList(); ?>
        <?php foreach ($row as &$item) : ?>
            <?php
            echo $this->loadTemplate('item');
            ?>
        <?php endforeach; ?>
        </div>
    <?php endif; ?>

I realise I may have modified the foreach statement incorrectly, too. This is the original:

<?php foreach ($this->intro_items as $key => &$item) : ?>

Can someone point out what I'm doing wrong, please?

0

Your Answer

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

Browse other questions tagged or ask your own question.