Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 8 of 8
  1. #1
    Senior Member medlington's Avatar
    Join Date
    Nov 2005
    Location
    Sheffield, UK
    Posts
    377
    Member #
    11968
    Liked
    4 times
    hi,

    im trying to adapt a joomla module to show random news items from the day but to still keep them in chronalogical order, heres the sql query that is being used:

    PHP Code:
            $query 'SELECT a.*, u.id, u.name, u.username,' .
                
    ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug,'.
                
    ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug'.
                
    ' FROM #__content AS a' .
                (
    $show_front == '0' ' LEFT JOIN #__content_frontpage AS f ON f.content_id = a.id' '') .
                
    ' LEFT JOIN #__users AS u ON u.id = a.created_by' .
                
    ' INNER JOIN #__categories AS cc ON cc.id = a.catid' .
                
    ' INNER JOIN #__sections AS s ON s.id = a.sectionid' .
                
    ' WHERE '$where .' AND s.id > 0' .
                (
    $access ' AND a.access <= ' .(int) $aid' AND cc.access <= ' .(int) $aid' AND s.access <= ' .(int) $aid '').
                (
    $catid $catCondition '').
                (
    $secid $secCondition '').
                (
    $show_front == '0' ' AND f.content_id IS NULL ' '').
                
    ' AND s.published = 1' .
                
    ' AND cc.published = 1' .
                
    ' AND a.created >= CURDATE() - INTERVAL ' $search_days ' DAY ' .
                
    ' ORDER BY ''RAND()';
            
    $db->setQuery($query0$count);
            
    $rows $db->loadObjectList();

            
    $i        0
    I've seen a few posts saying that I can add a line like this to the bottom:

    PHP Code:
    ORDER BY created DESC 
    but this doesnt seem to work.

    Can anyone help me with this?

    Thanks

  2.  

  3. #2
    WDF Staff MikeB's Avatar
    Join Date
    Jun 2010
    Location
    Lincolnshire, UK
    Posts
    88
    Member #
    22274
    Never had to get random and then order but in theory you could try this.

    Find
    PHP Code:
    ' ORDER BY ''RAND()'
    and replace with

    PHP Code:
    ' ORDER BY RAND(), created DESC'

    P.S. Nice location btw, I actually work in sheffield at a web design company
    Mike Barlow
    WDF Co-Owner

    Need help? PM Me!

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    You'll need to do a subquery, like so:

    Code:
    SELECT * FROM (
       SELECT a, b, c
       FROM my_tables
       ORDER BY RAND()
       LIMIT (0,10)
    ) AS random_news ORDER BY created DESC;
    By the way, in reviewing your code, I believe this line:

    AND f.content_id IS NULL

    is intended to be:

    AND f.content_id IS NOT NULL

    If so, you could eliminate it entirely by changing the LEFT JOIN above to this:

    INNER JOIN #__content_frontpage

    This query would also be extremely more efficient.

  5. #4
    Senior Member medlington's Avatar
    Join Date
    Nov 2005
    Location
    Sheffield, UK
    Posts
    377
    Member #
    11968
    Liked
    4 times
    Thanks for the advice,

    MikeB - I tried what you suggested but it didnt seem to do anything unfortunatly. Sheffield is nice, quite surprised to find another local on here though, I thought it was mainly folks from the USA on here.

    smoseley - Again thanks but Im a little confused as to how to use and where to put the sub query, Im only really starting to work with MySQL and have lots to learn yet.

    One other method I've been wondering about is to order the array that is getting created by the query but I've been trying to find out how to do this for a couple of days now but to no avail.

    Here is the var dump of the array if its any help:

    PHP Code:
    array(6) { [0]=>  object(stdClass)#525 (6) { ["link"]=>  string(46) "/headlines/8323-new-book-available-to-preorder" ["text"]=>  string(30) "New Book Available to Preorder" ["creationdate"]=>  string(25) "08:08PM - Fri 07 Sep 2007" ["created_by_alias"]=>  string(0) "" ["modified"]=>  string(19) "2007-09-07 20:08:27" ["name"]=>  string(0) "" } [1]=>  object(stdClass)#526 (6) { ["link"]=>  string(71) "/headlines/25464-washington-post-swine-flu-vaccine-will-contain-mercury" ["text"]=>  string(55) "Washington Post: Swine Flu Vaccine Will Contain Mercury" ["creationdate"]=>  string(25) "12:00AM - Tue 30 Nov 1999" ["created_by_alias"]=>  string(0) "" ["modified"]=>  string(19) "0000-00-00 00:00:00" ["name"]=>  string(8) "manager2" } [2]=>  object(stdClass)#527 (6) { ["link"]=>  string(82) "/headlines/16017-large-hadron-collider-must-hibernate-after-wrong-sort-of-big-bang" ["text"]=>  string(65) "Large Hadron Collider Must Hibernate After Wrong Sort Of Big Bang" ["creationdate"]=>  string(25) "12:00AM - Tue 30 Nov 1999" ["created_by_alias"]=>  string(0) "" ["modified"]=>  string(19) "0000-00-00 00:00:00" ["name"]=>  string(7) "manager" } [3]=>  object(stdClass)#528 (6) { ["link"]=>  string(57) "/headlines/17246-obamas-council-on-foreign-relations-crew" ["text"]=>  string(41) "Obama's Council on Foreign Relations Crew" ["creationdate"]=>  string(25) "12:00AM - Tue 30 Nov 1999" ["created_by_alias"]=>  string(0) "" ["modified"]=>  string(19) "0000-00-00 00:00:00" ["name"]=>  string(8) "manager2" } [4]=>  object(stdClass)#529 (6) { ["link"]=>  string(39) "/headlines/19264-a-question-of-identity" ["text"]=>  string(22) "A Question Of Identity" ["creationdate"]=>  string(25) "12:00AM - Tue 30 Nov 1999" ["created_by_alias"]=>  string(0) "" ["modified"]=>  string(19) "0000-00-00 00:00:00" ["name"]=>  string(8) "manager2" } [5]=>  object(stdClass)#530 (6) { ["link"]=>  string(117) "/headlines/17335-council-defies-judge-to-ban-family-again-from-adoption-because-father-once-smacked-a-child-for-swear" ["text"]=>  string(100) "Council defies judge to ban family AGAIN from adoption because father once smacked a child for swear" ["creationdate"]=>  string(25) "12:00AM - Tue 30 Nov 1999" ["created_by_alias"]=>  string(0) "" ["modified"]=>  string(19) "0000-00-00 00:00:00" ["name"]=>  string(8) "manager2" } } 
    I was thinking the problem was that the creation date variable is stored as a string rather than a number but I have tried converting this with strtotime() but again I just ended up getting very confused when it came to trying to order the array.

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    This should do it.
    PHP Code:
            $query 'SELECT * FROM (' .
                
    'SELECT a.*, u.id, u.name, u.username,' .
                
    ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug,'.
                
    ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug'.
                
    ' FROM #__content AS a' .
                (
    $show_front == '0' ' LEFT JOIN #__content_frontpage AS f ON f.content_id = a.id' '') .
                
    ' LEFT JOIN #__users AS u ON u.id = a.created_by' .
                
    ' INNER JOIN #__categories AS cc ON cc.id = a.catid' .
                
    ' INNER JOIN #__sections AS s ON s.id = a.sectionid' .
                
    ' WHERE '$where .' AND s.id > 0' .
                (
    $access ' AND a.access <= ' .(int) $aid' AND cc.access <= ' .(int) $aid' AND s.access <= ' .(int) $aid '').
                (
    $catid $catCondition '').
                (
    $secid $secCondition '').
                (
    $show_front == '0' ' AND f.content_id IS NULL ' '').
                
    ' AND s.published = 1' .
                
    ' AND cc.published = 1' .
                
    ' AND a.created >= CURDATE() - INTERVAL ' $search_days ' DAY ' .
                
    ' ORDER BY RAND()' .
                
    ' LIMIT (0,'.$count.')' .
                
    ' ) AS random_news ORDER BY created DESC ';

            
    $db->setQuery($query0$count);
            
    $rows $db->loadObjectList(); 

  7. #6
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Regarding the order - that happens in the database. Are you storing it as a VARCHAR or a TIMESTAMP or DATETIME field? The former won't sort properly. The latter two will.

  8. #7
    Senior Member medlington's Avatar
    Join Date
    Nov 2005
    Location
    Sheffield, UK
    Posts
    377
    Member #
    11968
    Liked
    4 times
    Thanks but I now get this error:

    Warning: Invalid argument supplied for foreach() in /home/david2009dev/public_html/modules/mod_fenrislatest/helper.php on line 201

    line 201 starts like this:

    PHP Code:
            foreach ( $rows as $row )
            {
                if(
    $row->access <= $aid)
                {
                    
    $lists[$i]->link JRoute::_(ContentHelperRoute::getArticleRoute($row->slug$row->catslug$row->sectionid));
                } else {
                    
    $lists[$i]->link JRoute::_('index.php?option=com_user&view=login');
                }
                
    $lists[$i]->text htmlspecialchars$row->title );
                
    $lists[$i]->creationdate JHTML::_('date'$row->modified$fdate);
                
    $lists[$i]->created_by_alias htmlspecialchars$row->created_by_alias );
                
    $lists[$i]->modified htmlspecialchars$row->modified );
                
    $lists[$i]->name htmlspecialchars$row->username );
                
    $i++;
            }; 
    IO've just checked the database and the created and modified columns are both DATETIME format

  9. #8
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    It's not returning any data. No error? I really hate using proprietary DB access tools. Can't see what's going on in the DB.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Search tags for this page

count id mysql return aleatory

,

count records then random in mysql

,

michael barlow 41 online social aliases

,

order by date random mysql server

Click on a term to search for related topics.
All times are GMT -6. The time now is 11:27 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com