• 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

Brent W

Well-known member
#1
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 :)
 

Jake Bunce

XenForo moderator
Staff member
#2
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
 

Jake Bunce

XenForo moderator
Staff member
#5
Hey Jake, when you get a chance can you add in a exclude clause for forumids?
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;
 

Quiver

Active member
#6
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
 

Jake Bunce

XenForo moderator
Staff member
#7

Quiver

Active member
#8
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.
 

Jake Bunce

XenForo moderator
Staff member
#9
Example code:

Code:
$db = XenForo_Application::get('db');

$topPosters = $db->fetchAll("
	SELECT...
");

XenForo_Application::setSimpleCacheData('topPosters', $topPosters);
 

Quiver

Active member
#10
Example code:

Code:
$db = XenForo_Application::get('db');
 
$topPosters = $db->fetchAll("
SELECT...
");
 
XenForo_Application::setSimpleCacheData('topPosters', $topPosters);
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
 

viper357

Active member
#11
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:

View attachment 29294

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:

View attachment 29295
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. :)