How to sort my query's results by publish_up value?

Question

I have a .php script that retrieve fields from my database table and then displays them in a foreach loop (here are the relevant parts of the code):

<?php                   
$jquery1 = "SELECT `fulltext`,alias,metakey,publish_up,metadesc
            FROM kms_content
            WHERE catid=22 AND state= '1'";
$jdb1->setQuery( $jquery1 );
$jfeed1= $jdb1->loadObjectList();
foreach ($jfeed1 as $jitem1):
    ?>
    <li>
        <div>
            <?php                                 
            $oldLocale = setlocale(LC_TIME, 'en_US');
            echo strftime("%a %d %b %Y", strtotime($jitem1->publish_up)); 
            setlocale(LC_TIME, $oldLocale);
            ?>
        </div>
        <div><?php echo $jitem1->fulltext; ?></div>
    </li> 
    <?php
endforeach;
?>

So everything works great except that my array entries are sorted by id. I would like them to be sorted out by the date (by the publish_up value).

I have tried this code, but it's displaying a blank page with only the title at the top:

$jfeed1 = $jdb1->loadObjectList();

function date_compare($a, $b)
{
    $t1 = strtotime($a['publish_up']);
    $t2 = strtotime($b['publish_up']);
    return $t1 - $t2;
}    
usort($jfeed1, 'date_compare');
foreach ($jfeed1 as $jitem1):

I've "learned" php just by looking at codes and reading tutorials, so I am missing a lot of fundamental knowledge which is essential when I have to put theory into practice.

I am confused about the difference between an array and an object and I can't figure out what I am supposed to do to solve this warning.

---

EDIT:

<?php
    var_dump($jfeed1)
?>

gives me the first item:

array(22) {
    [0]=> object(stdClass)#318 (5) { 
        ["fulltext"]=> string(212) "Yesterday ..." 
        ["alias"]=> string(3) "237" 
        ["metakey"]=> string(7) "France" 
        ["publish_up"]=> string(19) "2011-11-15 09:06:39"
    }

Answer 1

There are probably other differences (or maybe not: https://stackoverflow.com/questions/2193049/php-objects-vs-arrays) between objects and arrays, but it is mostly a style difference (at least when using a stdClass object).

For objects, you use the syntax of $variable->property and for arrays you use $variable['property']. var_dump will usually show an array-style syntax, though that is not what you want to use if you see stdClass, meaning it is an object.

So your date_compare function should use $a->publish_up and $b->publish_up within the strtotime calls. That should get that to actually use the values and thus actually sort.

---

You can also skip all the sorting bit in PHP by just ordering in the SQL statement:

$jquery1 = "SELECT `fulltext`,alias,metakey,publish_up,metadesc FROM kms_content  WHERE catid=22 AND state= '1' ORDER BY publish_up DESC";
$jdb1->setQuery( $jquery1 );
$jfeed1= $jdb1->loadObjectList();

(Not sure exactly what order you want, so if it is backwards, change the DESC at the end to ASC (descending v. ascending...)

---

And finally, the obligatory "other Joomla best practices that you should use" section. (Obviously your code can work without these, but they leverage some of the nicer features of Joomla.)

The biggest one is to avoid putting the table prefix in the query. Always reference a table by starting with #__. If you ever share code between two sites, it is likely you'll change the prefix and that will be the only thing that needs to change in the code to make it work in the new site, so save yourself trouble and just always type #__.

Second, Joomla's query object is a great way to build queries and have it automatically work for other database types (even if you never use them now) and also just has it escape things for you. Definitely worth learning about! http://docs.joomla.org/Selecting_data_using_JDatabase

Answer 2

You can use the following query:

$jquery1 = $jdb1->getQuery(true)
                ->select('`fulltext`, alias, metakey, publish_up, metadesc')
                ->from('#__content')
                ->where('`catid` = 22')
                ->where('`state` = 1')
                ->order('publish_up desc'); // OR ->order('publish_up asc');