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?

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

David Fritsch · Accepted Answer · 2014-08-27 15:27:55Z

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

mickmackusa · Accepted Answer · 2019-02-03 01:22:20Z

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>

Stack Exchange Network

Database query outputting twice

2 Answers 2

Linked

Hot Network Questions

Database query outputting twice

2 Answers 2

Linked

Related

Hot Network Questions