REQ: Post read X times counter

I wrote a script which determines the popularity of a post (and outputs the most popular posts) if thats what you are looking for.
Here is the link to the forum discussion:
http://dinki.mine.nu/word/index.php?p=108&more=1&c=1

That is a great idea! Ill see what I can come up with! 🙂

Running into an error:

Warning: mysql_affected_rows(): A link to the server could not be established in /home/deanlog/public_html/blog/wp-includes/wp-db.php on line 173

$this->rows_affected = mysql_affected_rows();
I think it has something to do with the $wpdb call.
Is there a way to close the $wpdb connection?
I use it in another hack, so I could use this script the way it was created.
My revised script:

function add_count($p_number) {
global $wpdb;
$result = $wpdb->get_results("SELECT post_ID, postView_cnt FROM b2postView WHERE post_ID = $p_number");
$test = 0;
if($result){
foreach ($result as $result){
$newCount = $result->postView_cnt + 1;
$postquery = ("update b2postView SET postView_cnt = $newCount WHERE post_ID = $result->post_ID");
$results = $wpdb->query($postquery);
$this->results = false;
$test = 1;
}
}
else{
$postquery = ("insert INTO b2postView VALUES($p_number, 1)");
$results = $wpdb->query($postquery);
$this->results = false;
}
}


a few quick comments:
– great idea! 😉
– it’d help if everyone would write hacks to use $wpdb at all times. I have to modify all hacks otherwise, as I have a lot of debug/performance tracking systems embedded in wpdb… It also means there’s only one open of the database occurring per page.
-the addCount thing could use to do an atomic operation. Otherwise it’s possible two accesses will cancel each other out. I’m looking into this for other reasons, will report back when I figure it out.
– my other mod to this will/would be to have it store the data as an extra column in the wp_posts table, rather than creating a new one. The advantage being that the new data will come implicitly in the post loop without a second query or a join or anything. 😉 yeah, it means modifying the base structure of the table — but since I’ve already done that once… 😉 🙂
Some great work here — I’m looking forward to figuring out how to pull some data from my referral tracking db to ‘initialize’ my counts to something reasonable, then start it all up. Keep up the hacks guys!
-d
http://www.chait.net

I’m with you — and of course agree all your reasons are extremely legit. some quick notes back at ya (since we help the populace learn by tossing out examples):
for (1): I’ve seen cases where people were re-opening connections to the db — I noted that just for clarity, not that you were! 😉
for (2): there’s likely more overhead to the SQL lookup itself. also, as I mentioned I’ve got db debug stuff, which basically tracks every query call coming through wpdb including a string name of caller, good for tracking too-many-queries or not-enough-caching. 🙂
and for (3): all the more reason to enhance wpdb to expose needed functionality! 😉 In addition to coding guidelines (some of which I don’t agree with, especially some of the formatting! 😉 ), it also forwards the ability to remove dependence on MySQL in the future… 🙂 Almost worth starting a new topic on enhancing wpdb for that reason alone…
I just basically cloned your idea but embedded into the posts table. One update query does the +1, the count is already in $post when needed, and popular posts queries one db. But again, that’s great for someone like me who’s already hacked up the tables — for 99% of users out there, your approach wins hands down, no question… well, and I haven’t tested mine yet.
Bitch that I didn’t have this in place two months ago — reconstructing counts from referrer log wasn’t overly useful, as it’s easily missing dozens or even hundreds of views… but gotta start somewhere!
By the way Mark, want to reinforce again cool idea and sample! Keeps me thinking about methods for enhancing my site, and continues to push me to learn more SQL tricks. 😉 Keep up the good work!
-d
http://www.chait.net

@david: Dude, you are awesome! I really was not criticising you, but referring to some of the weaknesses of $wpdb I love your site, BTW Read it when I need some good tech-browsing material. 🙂
@logan: For my hack, try to modify it this way:
Replace every occurance of $row, $result and $results, with $row123, $result123 and $results123 respectively. Let us know what transpires.

Quick and (really) dirty fix: Find the mysql_fetch_array() function in question and add an @ in front of it, like so:
@mysql_fetch_array()