PHP script: query DB, store result in field in a seperate DB table

Help PHP newbie write a script containing mySQL query and result

[criscokid]

What I'm trying to do is create a PHP script (that I'll run at regular intervals via cron) that will query a mySQL database table and count how many reviews a user has made and then post the result for each user in a field in the user database table.

I'm new to PHP so your help would be most appreciated. What I've managed to come up with so far is the mySQL query:

SELECT 
 COUNT(rp_reviews.userid) AS FIELD_1,
 rp_reviews.userid
FROM
 rp_reviews
GROUP BY
 rp_reviews.userid

Other bits of info...
Reviews databse name: reviews
Forum database name: forums
Forum database field: userid
Forum database field: reviewsposted

I hope I've provided enough information / been logical enough in my posting for someone to help me out. Many thanks in advance.

[vincevincevince]

The basic structure is to loop through that query, reading each row using mysql_fetch_assoc(), and then at each iteration running an UPDATE or INSERT command for that user.

[borntobeweb]

Hi criscokid and welcome to WebmasterWorld. If you're not planning to do anything else with the count, you can do everything in one SQL statement:

UPDATE rp_forums
SET reviewsposted = (SELECT COUNT(*) FROM rp_reviews
WHERE rp_reviews.userid = rp_forums.userid)

Hope this helps.

[criscokid]

Thanks for the help so far. I'm trying to get my head around this but getting more confused at the same time.

My reviews table is in a different database to my forums table - I'm not sure if that makes any difference or not. The common link between each record in the two tables is the 'userid'.