MySQL Query to Count Photos in Photo Gallery and Update Forum DB

[howarde]

Mostly what I'm looking for is something we can run as a scheduled task. Querying the DB like that for every thread view is time consuming. We had a script, but can't seem to locate it.

We have a field in our FORUM DB called "photos". I want to

UPDATE user
Set photos = count (photos from gallery DB)

but I'm not sure how to do that where I query the Gallery DB and insert the results into the FORUM DB.

Does that make more sense?

EDIT - 9/16/07 (automatically edited by vB.org)

Ok... I figured it out... Here it is.

PHP Code:

<?php
$link = mysql_connect ('server location', 'username','password') or die('I cannot connect to the database. Gallery');
mysql_select_db ("PhotoPost Gallery") or die('Could not connect: ' . mysql_error());

$result = mysql_query("Select userid, user, Count(*) AS num FROM photos Group BY userid;") 
or die(mysql_error());  

while($row = mysql_fetch_array( $result )) 
{
$userid = $row['userid'];
$num = $row['num'];

$link2 = mysql_connect ('server location', 'username', 'pw') or die ('I cannot connect to the database. Forum');
mysql_select_db ("vB Database") or die ('could not connect: ' .mysql_error());

$result2 = mysql_query("Update user SET photos = '$num' Where userid = '$userid';")
or die(mysql_error());

} 

?>

If anyone else wants to use this... insert your server locations, usernames, pw's, and database names in the appropriate spots. You also need to make sure your forum DB has a field in the users table called "photos"

To add this to user profile info, or on the postbit legacy - insert this

<if condition="$post['photos']"><div>Photos: (<a href="/gallery/showgallery.php?cat=500&amp;ppuser=$post[userid]">$post[photos]</a>) <a href="/gallery/showgallery.php?cat=500&amp;ppuser=$post[userid]">View Gallery</a></div></if>