1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.1 Query Help

Discussion in 'XenForo Questions and Support' started by BamaStangGuy, May 8, 2012.

  1. BamaStangGuy

    BamaStangGuy Well-Known Member

    I need two queries:

    1) Count posts of all members from May 1, 2012 and order them by most posts to least
    2) Count threads of all members from May 1, 2012 and order them by most threads to least

    It is for a contest I am running.

    Thanks :)
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    1) Run this query:

    Code:
    SELECT u.username AS username, COUNT(*) AS totalPosts
    FROM xf_post AS p
    LEFT JOIN xf_user AS u ON (u.user_id = p.user_id)
    WHERE post_date > UNIX_TIMESTAMP('2012-05-01 00:00:00')
    GROUP BY p.user_id
    ORDER BY totalPosts
    DESC;
    
    Example results:

    Screen shot 2012-05-08 at 6.23.03 PM.png

    2) Run this query:

    Code:
    SELECT u.username AS username, COUNT(*) AS totalThreads
    FROM xf_thread AS t
    LEFT JOIN xf_user AS u ON (u.user_id = t.user_id)
    WHERE post_date > UNIX_TIMESTAMP('2012-05-01 00:00:00')
    GROUP BY t.user_id
    ORDER BY totalThreads
    DESC;
    
    Example results:

    Screen shot 2012-05-08 at 6.26.11 PM.png
     
    ProCom and bousaid like this.
  3. BamaStangGuy

    BamaStangGuy Well-Known Member

    Awesome, exactly what I needed. Thanks Jake.
     
  4. BamaStangGuy

    BamaStangGuy Well-Known Member

    Hey Jake, when you get a chance can you add in a exclude clause for forumids?
     
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    1) Query:

    Code:
    SELECT u.username AS username, COUNT(*) AS totalPosts
    FROM xf_post AS p
    LEFT JOIN xf_user AS u ON (u.user_id = p.user_id)
    LEFT JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
    WHERE p.post_date > UNIX_TIMESTAMP('2012-05-01 00:00:00')
    AND t.node_id NOT IN (5,4,3,2,1,6,7,8)
    GROUP BY p.user_id
    ORDER BY totalPosts
    DESC;
    
    2) Query:

    Code:
    SELECT u.username AS username, COUNT(*) AS totalThreads
    FROM xf_thread AS t
    LEFT JOIN xf_user AS u ON (u.user_id = t.user_id)
    WHERE t.post_date > UNIX_TIMESTAMP('2012-05-01 00:00:00')
    AND t.node_id NOT IN (3,2,1,4,5,6)
    GROUP BY t.user_id
    ORDER BY totalThreads
    DESC;
    
     
    Crazy-Achmet and BamaStangGuy like this.
  6. Quiver

    Quiver Active Member

    So if I create a cron.php file containing, say:

    Code:
    <?php
    $username="mydbusername";
    $password="mydbpassword";
    $dbname="mydbname";
    $dbhost="mydbhost";
    $query="SELECT u.username AS username, COUNT(*) AS totalPosts
    FROM xf_post AS p
    LEFT JOIN xf_user AS u ON (u.user_id = p.user_id)
    WHERE post_date > UNIX_TIMESTAMP('2012-05-01 00:00:00')
    GROUP BY p.user_id
    ORDER BY totalPosts
    DESC";
    mysql_connect($dbhost,$username,$password);
    @mysql_select_db($dbname) or die(strftime('%c')." Unable to select database");
    mysql_query($query);
    mysql_close();
    echo strftime('%c')." ok!";
    ?>
    Would this work?

    I'm looking to have a cron that runs hourly, to find the top poster of the last hour, each hour. You with me? I want it to then save that value somewhere that I can access it from, as I'd then like it to display that user's ad in the header (as a reward for being the top poster). Sound confusing? Take a look at this: http://community.mybb.com/thread-124971.html
     
  7. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

  8. Quiver

    Quiver Active Member

    One other thing, I tested your above query.
    Works fine in phpMyAdmin but when I use create php code and try to run from a php file using mysql_query it says Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given

    I have no idea how to even get the current thing to work from php, never mind playing around with cache files.
     
  9. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Example code:

    Code:
    $db = XenForo_Application::get('db');
    
    $topPosters = $db->fetchAll("
    	SELECT...
    ");
    
    XenForo_Application::setSimpleCacheData('topPosters', $topPosters);
    
     
    Crazy-Achmet likes this.
  10. Quiver

    Quiver Active Member

    this is my current code:

    Code:
    <?php
    $con=mysqli_connect("mydbhost","mydbusername","mydbpassword","mydbname");
    // Check connection
    if (mysqli_connect_errno($con))
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
     
    $sql = "SELECT u.username AS username, COUNT( * ) AS totalPosts\n"
        . "FROM xf_post AS p\n"
        . "LEFT JOIN xf_user AS u ON ( u.user_id = p.user_id ) \n"
        . "WHERE post_date > UNIX_TIMESTAMP( \'2012-05-01 00:00:00\' ) \n"
        . "GROUP BY p.user_id\n"
        . "ORDER BY totalPosts DESC \n"
        . "LIMIT 0 , 30";
    $result=mysqli_query($con,$sql)
     
    // Associative array
    $row=mysqli_fetch_assoc($result);
    printf ("%s (%s)\n",$row["username"],$row["totalPosts"]);
     
    // Free result set
    mysqli_free_result($result);
     
    mysqli_close($con);
    ?>
    It's not working.
    As for the example code you gave me, how am I supposed to use that?
    According to W3Schools the above way is to make a PHP file run an SQL query.
    Though, to be quite honest with you Jake, I haven't got a clue what I'm doing. :p
     
  11. viper357

    viper357 Active Member

    Could this code be updated for 1.5.4 please, I would also like to use it but it's not working in phpmyadmin. Thanks. :)
     
  12. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    It should work the same in XF 1.5.4. Are you getting an error or something?
     

Share This Page