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

[howarde]

Our forum has a photo gallery (Photopost PHP) and on our user profiles, and on the postbit pages, it shows a link to the users gallery IF they have photos in it. There at one point WAS a script in our maintenance scripts that automatically ran to update this count (it was called update_photo_count.php - but it's nowhere to be seen on the drive, and probably hasn't been run since we moved ISP's 4 months ago). We have added a field to our user table in the FORUM DB called "photos"

Let's call the photo gallery DB "Gallery" and the vB DB "Forum"

If I run this query on the GALLERY DB

Select userid, user, Count(*) AS num from photos
Group BY userid;

I get the output I want (where num is the total of the photos in that users gallery) - my question is - how do I write a PHP script to UPDATE the FORUM DB to the user Table and insert that info into the photos field???

Thanks :)

[ericgtr]

Not tested and I am not sure about your query but you can start with something like this:


$total_photos = $db->query_first('
Select userid, user, Count(*) AS num from photos
Group BY userid;
');
$allphotos = number_format($total_photos ['count']);


Then call it up in your template with this

$total_photos[count]


Again, untested as I don't use photopost but it gives you something to play with.

[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
$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>