vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   MySQL Query to Count Photos in Photo Gallery and Update Forum DB (https://vborg.vbsupport.ru/showthread.php?t=157814)

howarde 09-14-2007 01:00 PM
MySQL Query to Count Photos in Photo Gallery and Update Forum DB
 
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

Quote:
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 09-14-2007 04:25 PM
Not tested and I am not sure about your query but you can start with something like this:

PHP Code:
$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
Code:
$total_photos[count]
Again, untested as I don't use photopost but it gives you something to play with.

howarde 09-15-2007 02:38 PM
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
Code:
<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>


All times are GMT. The time now is 07:57 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01009 seconds
  • Memory Usage 1,732KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (2)bbcode_code_printable
  • (2)bbcode_php_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (3)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete