1

I have the following database query:

    $database = JFactory::getDBO();
    $database->setQuery("SELECT items.headline, items.image, items.copy, items.source 
                FROM ms95f_createalist_items items
                JOIN ms95f_createalist_categories categories
                ON items.heading = " . $this->item->id);
    $orderitems = $database->loadObjectList();

I call this in html using:

    <div class="feedList">
    <?php 
    $count = 0;
    foreach ($orderitems as $item) { ?>
        <div class="feedItem">
            <div class="number"> <?php echo $count; ?></div>
            <div class="headline"> <?php echo $item->headline; ?></div>
            <div class="image"> <?php echo $item->image; ?></div>
            <div class="copy"> <?php echo $item->copy; ?></div>
            <div class="source"> <?php echo $item->source; ?></div>
        </div>
        <?php $count++; } ?>
    </div>

It's working great, but I'm getting the output of each item twice. Any ideas where I'm going wrong?

  • 1
    If this is an extensions for Joomla, I would strongly suggest you use the database table prefix #__ instead of manually defining it (ms95f_) – Lodder Aug 27 '14 at 17:00
4

You are likely getting duplicate items because of the join of the categories table. And since you don't actually select anything from the categories table, why not just do this:

$database->setQuery("SELECT items.headline, items.image, items.copy, items.source 
     FROM ms95f_createalist_items items");

It also looks like you might have an id that you are using to limit the data, but it doesn't really make sense since you are using it in the join but then not referencing any of the categories columns. (As a random guess, are their two categories right now?)

So if you actually want to limit by the id, you probably actually want a WHERE clause:

$database->setQuery("SELECT items.headline, items.image, items.copy, items.source 
     FROM ms95f_createalist_items items
     WHERE items.heading = " . $this->item->id");
1

To fully embrace the Joomla Query Builder methods AND leverage the result set row indices to present the counter, here is my suggested snippet...

$db = JFactory::getDBO();
$query = $db->getQuery(true)
    ->select(["headline", "image", "copy", "source"])
    ->from("#__createalist_items")
    ->where("heading = " . (int)$this->item->id);
$db->setQuery($query);
$orderitems = $db->loadObjectList();

While SOURCE is a MySQL keyword, it is not a RESERVED keyword so it does not need to be wrapped in backticks. For that matter, none of the columns need $db->quoteName() applied to them; likewise with the table name.

Since there is only one table being referenced, a table alias is unnecessary.

The code displaying the data could be written like this:

<div class="feedList">
    <?php 
    foreach ($orderitems as $index => $item) { ?>
        <div class="feedItem">
            <div class="number"> <?=($index + 1)?></div>
            <div class="headline"> <?=$item->headline?></div>
            <div class="image"> <?=$item->image?></div>
            <div class="copy"> <?=$item->copy?></div>
            <div class="source"> <?=$item->source?></div>
        </div>
        <?php
    } ?>
</div>

Or without repeatedly bouncing in and out if php:

<div class="feedList">
    <?php 
    foreach ($orderitems as $index => $object) {
        echo "<div class=\"feedItem\">";
            echo "<div class=\"number\"> " , ($index + 1) , "</div>";
            foreach ($object as $prop => $val) {
                echo "<div class=\"{$prop}\"> {$val}</div>";
            }
        echo "</div>";
    } ?>
</div>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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