MySQL Table Question

doyler442

Hi

I'm using MySQL, C# and Visual Studio 2005

To create my tables and stuff I use MySQL Query Browser.

I have 2 questions

1. I have three fields in a table called Lancaster: Price, Management and Location. These are than populated with a rating between 1-5.

I am than going to add another field called Average - how can I get this field to take the values from the previous 3 fields and get the average of them.


2. I also need to know how I can query multiple tables to read the above field of Average and display the top 5.


Any help on these would be much apreciated.

Thanks

misterq

[1]
you can use a trigger to perform a post insert or update action, such as getting the average of the first 3 columns and updating the average value
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

[2] This is basic stuff. There is plenty of tutorials out there for this.
The top 5 can be listed by ordering by the average and limiting your query to the first 5 rows, using the limit clause

doyler442

misterq wrote: »

[1]
you can use a trigger to perform a post insert or update action, such as getting the average of the first 3 columns and updating the average value
http://dev.mysql.com/doc/refman/5.0/en/triggers.html

I just have a stupid question about this - I have been looking it up and it seems this is probaly what I need but where exactly would I put this code?


CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;

For example this is a simple example they give but where would I put it in my own code - is it in VS where I have code inserting to my Lancaster table?

I know I must change it around to suit my own tables but thats ok.

lynchie

doyler442 wrote: »

I just have a stupid question about this - I have been looking it up and it seems this is probaly what I need but where exactly would I put this code?


CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;

For example this is a simple example they give but where would I put it in my own code - is it in VS where I have code inserting to my Lancaster table?

I know I must change it around to suit my own tables but thats ok.

If you look at the sample quoted in the link above, you will see he creates the table first, then creates the trigger.. so would that not suggest that you create it the same time as you create your table? Why would you create a trigger every time you are inserting into the table?

doyler442

Yeah i see thats how they did that but if I'm using MySQL Query Browser can I just put that code in and get the same result?

Or do I have to go into the DB Command Line Client to do it?

And lastly does putting the code in once store it in my DB permantly?

lynchie

doyler442 wrote:

Yeah i see thats how they did that but if I'm using MySQL Query Browser can I just put that code in and get the same result?

Yes

doyler442 wrote:

And lastly does putting the code in once store it in my DB permantly?

Yes

doyler442

OK I have managed to get my trigger working

DELIMITER $$
CREATE TRIGGER average
AFTER INSERT ON lancasterhall
FOR EACH ROW BEGIN
INSERT INTO average SET Average = (NEW.Location + NEW.Price + NEW.Management) / 3;
END;
$$

This is taking the values inserted into the Location, Price and Management fields and adding them up and placing them into the Average field.

Could someone tell me how I could get this to add to the previous figure in the Average field?

misterq

by using update and adding the existing value to the new value for that column