1 (edited by MattF 2007-11-25 15:16)

Topic: Updating a db count

Another of my infamous quick jobs. big_smile Can anyone tell me why this isn't incrementing the topic count by 1 on each loop? I honestly can't spot where the problem is with this.

<?php

define('PUN_ROOT', './');
require PUN_ROOT.'include/common.php';
$topic_array = array(1, 2);

$result = $db->query('SELECT u.id AS uid, t.poster FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'users AS u ON u.username=t.poster WHERE t.forum_id IN ('.implode(', ', $topic_array).')') or error('Unable to fetch topic info', __FILE__, __LINE__, $db->error());

if (!$db->num_rows($result))
{
        message('No topic info');
}
else
{
        while($topic = $db->fetch_assoc($result))
        {
                $db->query('UPDATE '.$db->prefix.'users SET num_topics=num_topics+1 WHERE id='.$topic['uid']) or error('Unable to update topic counts', __FILE__, __LINE__, $db->error());
                echo 'Topic poster: '.$topic['poster'].' Uid: '.$topic['uid']."\n";
        }
}

?>

The echo is outputting the username and uid fine, but the count is not incrementing. Btw, that db lookup is only like that because I've been playing around. big_smile I've tried it using both just the username and then with the above using uid and/or username, so I know that lookup is excessive. big_smile

I'm at a total loss as to where the problem is with this one.


Cheers,

Matt

Re: Updating a db count

There is no num_topics column in users by default: did you mean num_posts?
Other than that, I see no issue, the code looks correct to me.
Yeah, and as you figured out you can safely not use that expensive join

3

Re: Updating a db count

It's an extra column that's been added to the users table for this modification:

http://www.punres.org/viewtopic.php?pid=21292#p21292

This is a run once script I just knocked up to pull all the topic counts for those designated forums to update that count in the new column. But......... it just won't work. big_smile I've triple checked everything. I've even made sure I'm checking the correct db and everything, but those counts just won't update via the while loop, yet both those vars are echoed to the screen correctly, and no db error is logged/printed. To say I'm at a loss as to why it's not working would be an understatement. big_smile

4

Re: Updating a db count

This is the original db lookup, without the uid lookup:

$result = $db->query('SELECT poster FROM '.$db->prefix.'topics WHERE forum_id IN ('.implode(', ', $topic_array).')') or error('Unable to fetch topic info', __FILE__, __LINE__, $db->error());

Then

username=\''.$topic['poster'].'\'

is used for the WHERE statement in the UPDATE query. I'm terrible at doing the join statements, so just thought this might be a nice easy statement with which to practice joins whilst I was trying to eliminate possible cockups on my part for this script not working. big_smile

I've managed to get a very basic grasp of join statements now, but still ain't got the script working as expected. big_smile

Re: Updating a db count

I just tested the original script you pasted, it worked perfectly for me

6 (edited by MattF 2007-11-25 18:32)

Re: Updating a db count

It won't do naff all for me. big_smile Just checked it on the test forum on the production server too, and still the same. Zilch.

Btw, is this code correct for the username only lookup? I'm just going to post the code on that other thread and give it up as a bad job getting it working here. big_smile

<?php

define('PUN_ROOT', './');
require PUN_ROOT.'include/common.php';
$topic_array = array(1);

$result = $db->query('SELECT poster FROM '.$db->prefix.'topics WHERE forum_id IN ('.implode(', ', $topic_array).')') or error('Unable to fetch topic info', __FILE__, __LINE__, $db->error());

if (!$db->num_rows($result))
{
        message('No topic info');
}
else
{
        require PUN_ROOT.'header.php';
?>
<div class="block">
        <h2><span>OUTPUT</span></h2>
        <div class="box">
                <div class="inbox">
                        <table>
<?php

        while ($topic = $db->fetch_assoc($result))
        {
                $db->query('UPDATE '.$db->prefix.'users SET num_topics=num_topics+1 WHERE username=\''.$db->escape($topic['poster']).'\'') or error('Unable to update topic counts', __FILE__, __LINE__, $db->error());
                echo '<tr><td>Topic poster: '.$topic['poster'].' - Topic count incremented.</td></tr>'."\n";
        }

?>
                        </table>
                </div>
        </div>
</div>
<?php

        require PUN_ROOT.'footer.php';

}

?>

Cheers ever so much for your help on this one. smile It's got me hellishly stumped why it won't work here though. big_smile


Thanks,

Matt

Re: Updating a db count

while (false !== ($topic = $db->fetch_assoc($result)))

can be

while ($topic = $db->fetch_assoc($result))

Otherwise, looks fine to me

8

Re: Updating a db count

Cheers. smile smile

Re: Updating a db count

Oh, I think you'll want to $db->escape the poster value from the database.

10

Re: Updating a db count

I've updated that code above with those two. Is that correct as it is now?

Just on a side note with the db->escape, when exactly should that be used? I've never been 100% sure whether it's only needed when inserting info into the db, or whether it should also be used in queries too, for escaping unknown input in a lookup? Or is it better to simply err on the side of caution and always use it for anything other than integer values when working with the db?


Thanks again,

Matt

Re: Updating a db count

It is not about inserting or selecting. It is about escaping input in a query. If you're surrounding something with a " or a ' and the input could potentially have a " or a ' in it, you need to escape it so that it can't "break out," which would be an SQL inject.

12

Re: Updating a db count

Smartys wrote:

It is not about inserting or selecting. It is about escaping input in a query. If you're surrounding something with a " or a ' and the input could potentially have a " or a ' in it, you need to escape it so that it can't "break out," which would be an SQL inject.

Thanks. smile That's one point where I've usually tried to err on the side of caution, but never knew the exact specifics. It's now a lot clearer. Cheers. smile

Re: Updating a db count

More info:
http://us.php.net/mysql_real_escape_string

Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

14

Re: Updating a db count

Cheers for that info. smile I'm now a lot clearer on the requirements/consequences.

Thanks. smile