Topic: How to update stats after an edit

[gerryg]

WP 2.7.1 PHP 5.2.4 WP-STATS plugin 2.40
When I create a new blog and publish, stats are fine. But if I edit and change the category for that post, the stats now show a broken link and bring up an empty page. Updating permalinks no change. I imagine you can't track all changes, so is there a way to refresh the wp-stats?

[GaMerZ]

You are talking about WordPress Stats or WP-Stats? The links are all real time and not cache.

[gerryg]

This was from the Top Ten Recent Stats after clicking wp-stats. Just checked and all of them are now bad for the posts, showing pages 5 or 6. The recent comments below them are still working.

[gerryg]

Further, the Ten Most/Highest Stats below do all work, even if listing the same item that is bad above. Other items below this also seem to work, so only the Top Ten Recent Stats - 10 Recent Posts do not work.

I know it worked right after I first published the post. I may have edited the others at some time.

[GaMerZ]

The link displayed is the new link or the old link? Did you try the date/time permalink?

[gerryg]

The bad links displayed show page 5 or 6, several duplicating. Actual pages should be around 320.
If I change permalinks to date/time from the default, it breaks everything in the blog.

Whatever you're using in "10 most commented posts" is working fine, but not in "10 recent posts" even for the same item.

[GaMerZ]

sorry but I don't get you and I can't reproduce the problem. I have a post and edit the category, change from "WordPress" to "Site" and I go to Top 10 recent post and I am able to display it without any problem.

To add, I don't see why changing categories of a post will cause permalink to break as they are not related at all. You may want to try it on a fresh copy of WP instead.

[gerryg]

On permalinks not related to categories, agreed. I'll try this in another wp blog to check. Don't see any issue in your code or the wp supporting routines, but I'll run your query directly and see what comes back. It's either there or the fetch of the post body.

Thanks for looking. I'll get back if I find anything on your end.

[GaMerZ]

Yea please do, lol this is a mysterious problem

[gerryg]

Finally got back to looking at this. Had to setup eclipse/pdt/xdebug so I could see what's happening. Used brand new wordpress install adding only wp-stats. Added 4 posts and deleted the original. So, the post IDs are 2,3,4,5. Added one comment. No other changes. Using default permalinks.

For the 10 recent posts you show the right names but all have an ID = 1.

In wp-stats.php your query there is:
SELECT $wpdb->posts.*, $wpdb->users.* FROM $wpdb->posts LEFT JOIN $wpdb->users ON $wpdb->users.ID = $wpdb->posts.post_author WHERE user_activation_key = '' AND post_date < '".current_time('mysql')."' AND $where AND post_status = 'publish' AND post_password = '' ORDER  BY post_date DESC LIMIT $limit

It appears to me that your query returns the ID field from BOTH wp_posts and wp_users and the return mapping overwrites the wp_posts.ID with the wp_users.ID. Since I used the admin with an ID=1, that's where the "1" is coming from.

There may be other situations where this works, but I only just started with their code and don't know enough about it to say. However, this scenario does seem clear. To check, I REVERSED and made the select order in the query:
$wpdb->users.*, $wpdb->posts.*
and now you are showing the correct links.

Overall, your screen design and functionality are exactly what I needed, so this effort was well spent. Now, I need to check the db tables to see if I broke anything else by doing this:-)

[GaMerZ]

How about try INNER JOIN instead of LEFT JOIN?

[gerryg]

You're nasty; I've been retired for 5 years and you're asking me to think too hard:-)
Well, in MySql INNER JOIN is equiv to CROSS JOIN. In this case, the only diff between that and LEFT JOIN is the handling of nulls in the mapping, if you had a post with no matching user. But that's a different issue. If we're sure one will always be there, the two are the same. If not and if you only want records that match the join criteria then INNER is better. But it won't effect the real issue here. Actually, it may or may not matter. Your WHERE has user_activation_key = '' and without digging further I don't know if that would match a null or not. If we're sure they maintain DB consistency between users and posts, it doesn't matter either way.

I haven't traced through the wordpress result mapping so I don't know if there may be other solutions. However, where you have:
  if($recentposts) {
    foreach ($recentposts as $post) {
I used the debugger on $post and I see only one ID field which is not table qualified. That implies that in any join of tables having an identical column name you will only receive the last such column, with the sequence determined by your SELECT statement.

So, one obvious solution is to explicitly name the columns in your SELECT for just the users table leaving all columns from the post table. That allows omitting the users.ID from the result table. Alternatively, just reverse the order as I did. I don't see any drawbacks to either approach.

[GaMerZ]

I can't explicitly name the columns as WP may need all of them, note that there is a $post global variable and these are pass to WordPress for handling.

You may be right on the null value, so I think a INNER JOIN will solve this problem as well. I think you could also change LEFT to RIGHT instead of changing the order as well.

[gerryg]

On not explicitly giving the columns names, I agree with you. On nulls, the best thing is always to run the other way...

However, LEFT/RIGHT doesn't change the column order in the result table and that ordering is what caused the problem. If you see any issues with my first suggestion let me know what and I'll look for another approach.

[GaMerZ]

Sure, I am just figuring out why the order matters as subsequently all my queries started with $wpdb->posts first

[gerryg]

It matters in the return mapping from wp-db. That takes the results table and maps each value into a variable with the same name as the table column. So, posts.ID maps into $ID then you get users.ID which also maps into $ID, overwriting the previous value. It only happens when the results table has two columns with the same name. A direct sql query in phpmyadmin shows the results table with two ID columns.

[GaMerZ]

I see, thanks for the explaination. I have reverse the order to wpdb->users.* then $wpdb->posts.*

[gerryg]

My pleasure. Just took a quick look at your other queries and don't see any similar issues. This was fun to fix and I'm going to do some minor tweaks to wp. If you happen on any more stubborn issues that need another set of "eyes" feel free to give me a shout via direct email. Us retired guys have to keep busy somehow.

[GaMerZ]

Sure thing, thank you so much!