CODEWITHSUNDEEP
phpmysqlsql
user3620932
user3620932

Reputation: 1

Modifying a record when a new entry is created

I am currently creating a database for a sort of game/reviewer based application. Whenever a user submits a review of a restaurant or such it adds points to his score. A summary of the SQL script would be:

CREATE TABLE user
    userid CHAR(30)
    user_name....
    userpoints largeint
    etc.

The table for the reviews is here:

    Restaurantid largeint (auto incrementing)
    restaurantname CHAR(30)
    etc.

How do I program the app to give the points whenever a review is posted?

Upvotes: 0

Views: 41

Answers (4)

user2976773
user2976773

Reputation: 59

you can do it using triggers if you want the database to handle the problem or you would program it in php by using an insert query or update query.

Upvotes: 0

Dr R Dizzle
Dr R Dizzle

Reputation: 272

You are going to want to do a bit of research into triggers. Triggers allow you to run SQL statements when records are selected, updated, inserted etc.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

You could create one of these triggers to run on insert into the review restaurant table, and have it update the users table to add 1 to the userpoints row for that user.

Upvotes: 0

juergen d
juergen d

Reputation: 204894

Use a trigger that gets fired automatically on every insert in the reviews table:

delimiter |
CREATE TRIGGER review_trigger AFTER INSERT ON `reviews`
FOR EACH ROW BEGIN
   update user
   set userpoints = userpoints + 1
   where userid = NEW.reviewer;
END
|
delimiter ;

I assumed you have a column in your reviews table that relates to the user - I called it reviewer.

Upvotes: 2

Chris Pickford
Chris Pickford

Reputation: 8991

You can either create a Trigger on the Review table that will insert into user, or create a Procedure to handle both inserts which then gets called by your application.

Upvotes: 0

Related Questions