How to improve the efficiency of this small bit of code?

[Charles Hill]

This is a bit of code from a maintenance function in my blog mod that is still awaiting approval.  What this function should do is check to make sure that the total from the blog_categories table matches the actual total number of topics associated with that blog category.

Code Select Expand

<?php
$request = db_query("
SELECT categoryID, total 
FROM {$db_prefix}blog_categories", __FILE__, __LINE__);
if (mysql_num_rows($request) > 0) {
while ($row = mysql_fetch_assoc($request)) {
$topics = db_query("SELECT ID_TOPIC FROM {$db_prefix}topics WHERE categoryID = $row[categoryID] AND ID_BOARD = $modSettings[blogBoard]", __FILE__, __LINE__);
$topics = mysql_num_rows($topics);
if ($row['total'] != $topics)
db_query("
UPDATE {$db_prefix}blog_categories 
SET total = $topics 
WHERE categoryID = $row[categoryID] 
LIMIT 1", __FILE__, __LINE__);
}
}
?>

[karlbenson]

I think something like this.
(Untested)

Code Select Expand

<?php


// Query to get the categories and totals
$request = db_query("
SELECT count(*) as newtotal, t.categoryID, b.total as oldtotal
FROM {$db_prefix}topics as t
LEFT JOIN {$db_prefix}blog_categories as b ON (b.categoryID = t.categoryID)
WHERE t.ID_BOARD = $modSettings[blogBoard]
GROUP BY categoryID
ORDER BY categoryID ASC", __FILE__, __LINE__);

// Return out of function if no rows
if (mysql_num_rows($request) == 0)
return;

while ($row = mysql_fetch_assoc($request))
{
// Is there any difference between the new and old totals?  If so update it
if ($row['newtotal'] != $row['oldtotal'])
db_query("
UPDATE {$db_prefix}blog_categories 
SET total = $row[newtotal] 
WHERE categoryID = $row[categoryID] 
LIMIT 1", __FILE__, __LINE__);
}

?>

[Charles Hill]

Hmmmmm didn't work.

Tried a few changes and they didn't work either.  What's the difference between INNER JOIN and LEFT JOIN?

[karlbenson]

There was one bug i spotted + fixed

[Charles Hill]

There was one bug i spotted + fixed

I got an ambiguous mysql error because of SELECT count(t.ID_TOPIC) as newtotal, categoryID, b.total as oldtotal

I changed that to SELECT count(t.ID_TOPIC) as newtotal, b.categoryID, b.total as oldtotal and tried it... Didn't get any errors, but it still doesn't do what it's supposed to do.

[karlbenson]

I changed some more stuff.

[Charles Hill]

Nope still doesn't work.