3

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"
    }
4

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

  • Great! Thanks so much David for your answer and for all your advices. It's working great! I didn't know about the #__ trick: that's really cool! I will dig into the Joomla's query object to improve my codes! Thanks again David! – MagTun Aug 30 '14 at 10:04
  • 1
    Keep asking questions, and you'll probably keep getting cool tricks. I know a lot of the people that answer questions on here like to provide them! :) – David Fritsch Aug 30 '14 at 15:06
  • :)! There are so many things that are still a great mystery to me, that you can be sure I will stick around to ask questions and get more tricks! – MagTun Aug 30 '14 at 15:19
3

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');
  • 1
    Please don't forget about escaping columns and values ($db->quote() and $db->quoteName()) ;) – Lodder Aug 30 '14 at 22:04
  • 1
    @Lodder Sure, but when column names and values are not variable, it's not necessary to use $db->quoteName() and $db->quote() ;) – Farahmand Aug 31 '14 at 9:10

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.