Database query outputting twice

Question

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?

Answer 1

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

Answer 2

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>