PHP SQL - Creating FUNCTIONS/PROCEEDURES

Author

Creating FUNCTIONS/PROCEEDURES

Cerebral Believer

2006-09-16, 6:59 pm

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 not sure if FUNCTIONS can reference tables...

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.

2006-09-16, 6:59 pm

Cerebral Believer wrote:
>
> 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 not sure if FUNCTIONS
> can reference tables...

Read MySQL documentation:

http://dev.mysql.com/doc/refman/5.0...-procedure.html

Cheers,
NC

Cerebral Believer

2006-09-17, 8:01 am

"NC" <nc@iname.com> wrote in message
news:1158443426.110452.211810@e3g2000cwe.googlegroups.com...
> Cerebral Believer wrote:
>
> Read MySQL documentation:
>
> http://dev.mysql.com/doc/refman/5.0...-procedure.html

Thanks, I keep getting Error #1064, but the same syntax works fine for a
simple query.

CREATE FUNCTION Sum_001R RETURNS INT
SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information`
WHERE`Catalogue Number` = "FBDCD008";

I am perplexed.

Regards,
C.B.

Cerebral Believer

2006-09-17, 6:58 pm

"Cerebral Believer" <nospamthanks@hadenoughalready.com> wrote in message
news:0ccPg.28491$SH2.23209@newsfe4-gui.ntli.net...
>
> "NC" <nc@iname.com> wrote in message
> news:1158443426.110452.211810@e3g2000cwe.googlegroups.com...
>
> Thanks, I keep getting Error #1064, but the same syntax works fine for a
> simple query.
>
> CREATE FUNCTION Sum_001R RETURNS INT
> SELECT SUM(`Quantity Ordered`) FROM `Sales Detail Information`
> WHERE`Catalogue Number` = "FBDCD008";
>
> I am perplexed.

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.

Sponsored Links