Creating Functions/Proceedures

[A_Tame_Lion]

Hi everybody,

I have the following statement:

SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE `Catalogue Number` = "FBDCD008";

I can run this as a query in the SQL window, and it works fine.

How could I turn this query into a FUNCTION or perhaps better still a PROCEEDURE?

I am using phpadmin 2.7.0, and My SQL 5.0.15. I would like to get the statement registered as a FUNCTION/PROCEEDURE so that I can so that I can select it from the "Function" selector on the "Insert" sub-tab in myphpadmin. I hope this will enable the column to always hold the current value for Quantity Sold (the sum total of "Quantity Ordered"). This is the only way I can think of achieving this goal, unless someone has another idea?

Any thoughts on this?

Regards,
C.B.

[A_Tame_Lion]

OK,

I found the answer, again quite simple, but requiring back-tick quotes (which I had not put round the proceedure name), which is not specified on the MySQL website, neither in the reference material or the examples. So this works:

CREATE PROCEDURE `008_sales`()
SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE `Catalogue Number` = "FBDCD008";

Anyone know how I can get a column to automatically run that procedure? Is that even possible?

Regards,
C.B.

[chazzy]

what do you mean by a column to automatically run that procedure?

you can create jobs in 5.1 i believe, but not before.

[A_Tame_Lion]

Chazzy,

Thanks for your reply. Let me explain my scenario again.

I have two tables `Sales Detail Information` and `Product Information`. In the `Product Information` table each record has a unique "Catalogue Number" which is the Primary Key. In this table I also have a column `Total Sold`, and I am trying to get the database to automatically calculate the total sold of each catologue number and input the value into this column. This can be done by summing the "Quantity Ordered" column in the `Sales Detail Information` table, where the "Catalogue Number" is as defined in the query below.

SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information` WHERE `Catalogue Number` = "FBDCD001R";

So what I mean by getting a column to "automatically run that procedure" is that I want the column to always provide the current "Total Sold" for a particular "Catalogue Number". If I run a query periodically, I can insert the returned value manually into a particular row, but if the query were expressed as a function or procedure, I was thinking that it might be possible to assign it to a particular column, so that the column value for "Total Sold" changes dynamically as the information in the `Sales Detail Information`changes. Hope this makes sense.

Basically I am struggling with finding the correct format for the function or procedure to enable it to perform the sum I want, and I am not sure if it is possible to input this function or procedure into the column somehow so that it produces a value - perhaps I am thinking to much along the lines of a spreadsheet?

I will also look into Jobs too.

[A_Tame_Lion]

I found the answer I am looking for using a VIEW statement. Before I had designed "totals" tables and thought I could put calculations within the columns of the table to draw results from the columns of other tables. Now I am realising I have to use VIEW statements, so the following worked for me:

create view total_sales
select `P`.`Artist` AS `Artist`,`P`.`Title` AS `Title`,`P`.`Format` AS
`Format`,`P`.`Type` AS `Type`,`P`.`Catalogue Number` AS `Catalogue
Number`,`P`.`Price` AS `Price`,sum(`SD`.`Quantity Ordered`) AS `Total Sold` from (`remotedb`.`Product Information` `P` left join `remotedb`.`Sales Detail Information` `SD` on((`P`.`Catalogue Number` = `SD`.`Cat Num`))) group by `P`.`Catalogue Number`;

C.B.