Is there a way to update multiple tables with the same information?

Radon3k

I'm sure the title was confusing so please allow me to elaborate. I have a two tables in my database each with a common field: userid and reviews. The reviews table has a userid section to track which userid is submitting reviews. The users table has a reviews field which tracks the amount of reviews each user has submitted. How do I keep this updated accurately and automatically? More specifically, when a user submits a review it updates the reviews number in the users table. I'm sorry if this is confusing but I'm really not sure how else to explain it. Also, I put this in the Coding section instead of the database section because I believe I'll need PHP code as opposed to SQL syntax. Thanks. 🙂

chyan

make a query to your review table
you can count the user id by grouping the userid

after that you can using php to fetch count number for each user
and then store it to your user table for each user

Weedpacket

Dunno if your DBMS supports triggers. If it does you can write a trigger in your database that updates the second table when you insert into the first. Heavier going would be to write a stored procedure in your database that does both insertions and call that. Meanwhile, ask yourself whether it would not just be easier to calculate such totals when you actually want to use them, instead of every time they change with the consequent worry of whether or not they're out of date.

chyan

$query= "select userid , count(userid) as count from review
group by userid";

if(!result=mysql_query($query,$conn)){
echo mysql_error();
exit;
}

while($row=mysql_fetch_array($result)){
$user=$row[user];
$count=$row[count];

$query2="update user set review='$count' where userid='$user'";

}

Radon3k

Originally posted by Weedpacket
Dunno if your DBMS supports triggers. If it does you can write a trigger in your database that updates the second table when you insert into the first. Heavier going would be to write a stored procedure in your database that does both insertions and call that. Meanwhile, ask yourself whether it would not just be easier to calculate such totals when you actually want to use them, instead of every time they change with the consequent worry of whether or not they're out of date.

Sorry but the majority of that confused me. As for the last bit, when a user logs in and checks his/her profile I want them to be able to see how many reviews they have posted as well as which ones they have posted. I'm not sure what the best way for setting this up would be, however.