corrupt statistics

[romulus]

Now that garvin is in the house again, I can post bugs more often

My statistics in the admin are somewhat corrupt. The statistic for length of posts does show the length of all articles together but not the length of each individual post. Look at this screenshot: http://www.romulus23.de/uploads/fehler1.jpg

Also the statistic for most visited article or best rated article is empty. Look at this: http://www.romulus23.de/uploads/fehler2.jpg

Any idea?

[garvinhicking]

[quote="romulus"]Now that garvin is in the house again, I can post bugs more often
[quote]

Hehe. Yes, please, let that get a habit. We really do need some serious bug testers. If I haven't mentioned yet, thanks a lot for your contributions!

The statistics you showed are really somewhat corrupt. However they work fine on my MySQL 4.0.13 - if you happen to have some time, you might want to debug the queries in the statistics and print out their return/mysql_error() output?

Regards,
Garvin

[romulus]

No thanks required, but thank you also

How do I debug the sql queries? I only have rented webspace with a mysql db and have no access to the mysql log

[garvinhicking]

How do I debug the sql queries? I only have rented webspace with a mysql db and have no access to the mysql log[/quote]

You can debug the queries like this. Open your serendipity_event_statistics.php file and search for:

Code: Select all

$length = serendipity_db_query("SELECT SUM(LENGTH(body) + LENGTH(extended)) FROM {$serendipity['dbPrefix']}entries", true);
$length_rows = serendipity_db_query("SELECT id, title, (LENGTH(body) + LENGTH(extended)) as full_length FROM {$serendipity['dbPrefix']}entries ORDER BY full_length DESC LIMIT $max_items");

Then you can do a simple

Code: Select all

$length = serendipity_db_query("SELECT SUM(LENGTH(body) + LENGTH(extended)) FROM {$serendipity['dbPrefix']}entries", true);
$length_rows = serendipity_db_query("SELECT id, title, (LENGTH(body) + LENGTH(extended)) as full_length FROM {$serendipity['dbPrefix']}entries ORDER BY full_length DESC LIMIT $max_items");
echo "LENGTH ROWS: " . print_r($length_rows, true) . "<br />";
echo "MYSQL ERROR: " . mysql_error() . "<br />";

You could add those lines to all the queries, I hope most of the variable names are descriptive enough to get the idea what they're used for.

Good luck,
Garvin (BTW, if you need "live" support for that, try my ICQ at 2 139-2-2-4.2)

[romulus]

hmm I think I have the problem. I have used the RSS import to import my old articles and had checked the option to only fill normal body text, but not extended body text.

The normal sql statement in the statistics plugin (SELECT id, title, (LENGTH(body) + LENGTH(extended)) as full_length FROM {$serendipity['dbPrefix']}entries ORDER BY full_length DESC LIMIT $max_items) to query article length only delivers "null" as full_length for all articles. I have changed the sql a bit like that:

Code: Select all

"SELECT id, title, sum( LENGTH( body ) + LENGTH(EXTENDED ) ) AS full_length FROM s9y_entries GROUP BY title ORDER BY full_length LIMIT 1000"

Now I get for some articles the correct length, but for the most I get "0" (not "null").

Hmm seemed a little bit strange, so I looked into the s9y_entries table and queried the following sql:

Code: Select all

SELECT id, title, length( body ) , length( EXTENDED ) FROM `s9y_entries`  LIMIT 0 , 30

The result showed me for some articles that the length of the EXTENDED field was "null" and after some thoughts I could see it: its "null" only for articles I have not changed manually after the RSS import wrote them to the database. So the sql statement to import the articles if no extended body is to be filled is wrong.

I'm not THAT of an expert in sql/s9y so I'm leaving this bugfix for you

BTW: can you form me an sql statement that corrects the length of the EXTENDED field in my database without harming the other articles? Would be great! Thanks!

[garvinhicking]

Hi Romulus!

Thanks for debugging this for me )

You could please try this:

"UPDATE s9y_entries SET extended = '' WHERE ISNULL(extended)"

Of course, backup early, backup often. I haven't tested this.

Now I just need to fix the RSS import, brb.

[romulus]

now the counting of length of articles looks much better. Thx!

But the top "5 longest articles" and the visit statistics at the end of the page are still empty. For both I can't find any code in serendipity_plugin_statistics.php I can see only one variable for the longest article that doesn't get filled with a value.