Fetch category and article list joomla

Question

I am using joomla 2.5 and I want to fetch a list of distinct catagories and article list.

I'm using the following code to fetch all categories but I'm unable to get the category id to use it on another insert.

How can I fetch these distinct field ids also in select list given below.

$db = &JFactory::getDBO();
$query = "SELECT DISTINCT title FROM #__categories WHERE published = '1' ORDER BY level";
$db->setQuery($query);
$rows = $db->loadObjectList();
?>
<?php $catagories = (isset($this->settings['catagories']) ? $this->settings['catagories'] : "");?>
<select id="catagories" name="settings[catagories]">
<option value="default" selected="selected">---Default---</option>
<?php foreach ($rows as $row) {?>
<option <?php //if ($row->id == $catagories) { echo " selected=\"selected\""; } ?>value="<?php //echo $row->id;?>" >
<?php echo $row->title;?>
  </option>
<?php }?>
</select>

score 0 · Answer 1 · answered Oct 10 '12 at 10:37

The first step is to do a find/replace for 'catagories' to change to 'categories' to make sure that your code is speaking the same language as your database tables and -- a happy bonus -- that it is in English!

If I understand the question: you have a list of the distinct titles of categories but no IDs for these. You have no guarantee that each title corresponds to exactly one ID; indeed, if using 'DISTINCT' in your query is to have any effect on the rows returned, then there are at last two IDs corresponding to a single title.

This is quite unusual so you may want to review why (i) you have several eponymous (same-named) categories and (ii) this being the case, why they should not be returned for selection in your form.

If it turns out that you do need to provide a list of distinct category titles and for the form submission to do something with these, there must be IDs, then the choice of which ID to use per title is ambiguous.

Assuming that it does not matter which ID is used, so long as it matches the appropriate title, you can do the following:

<?php foreach ($rows as $row) {
$query = "SELECT id FROM #__categories WHERE title = " . $row->title . " AND published = '1'  LIMIT 1 ";
$db->setQuery($query);
$row->id = $db->loadResult();
?>
<option <?php if ($row->id == $catagories) { echo " selected=\"selected\""; } ?>value="<?php echo $row->id;?>" >
<?php echo $row->title;?>
  </option>
<?php }?>

For a more efficient solution, you could look into using UNION in your original query to provide an ID for each of the rows returned.

thanks joe for your time i tried it it's working fine with small errorcorrect code is — Rakesh Sharma, Oct 10 '12 at 12:05

score 0 · Accepted Answer · answered Oct 10 '12 at 12:05

<?php 
$db = &JFactory::getDBO();
$query = "SELECT DISTINCT title FROM #__categories WHERE published = '1' ORDER BY level";
$db->setQuery($query);
$rows = $db->loadObjectList();
?>
<?php foreach ($rows as $row) {
$query = "SELECT id FROM #__categories WHERE title = " . $row->title . " AND published = '1'  LIMIT 1 ";
$db->setQuery($query);
$rowid = $db->loadResult();
?>
<option value="<?php echo $rowid;?>" >
<?php echo $row->title;?>
  </option>
<?php }?>

Fetch category and article list joomla

2 Answers2