Thread: Sstored Procedures
Are there any good resources on stored procedures? I'm not very familiar with them, so if with regards to php is important, then resources with regards to php it is then. Thanks. Sean
i'm not sure if this is the best place to ask (if there is a better mailing list, please let me know)... i have a sql problem. i have the following query... $sql_notes = "SELECT notes.assembly_notes, notes.assembly_notes_contact_id " . "FROM t_product AS prod, t_link_product_assembly_notes AS link, t_assembly_notes AS notes " . "WHERE link.product_id = prod.product_id " . "AND link.assembly_notes_id = notes.assembly_notes_id ". "AND link.product_id = " . $product_id; it works great except for one problem. i want the assembly_notes_contact (the actual name) instead of the assembly_notes_contact_id. the result of this query is stored in an array and then i iterate through the array before displaying the data. assembly_notes_contact_id is a foreign key to the employee table's primary key. i want to be able to query the db and get the employee name (instead of his id) into the recordset array. the employee name is the same as the assembly_notes_contact. is this possible? if so, how? tia... __________________________________ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail
On Jun 28, 2005, at 6:41 PM, <operationsengineer1@yahoo.com> wrote: > i'm not sure if this is the best place to ask (if > there is a better mailing list, please let me know)... > > i have a sql problem. i have the following query... > > $sql_notes = "SELECT notes.assembly_notes, > notes.assembly_notes_contact_id " . > > "FROM t_product AS prod, t_link_product_assembly_notes > AS link, t_assembly_notes AS notes " . > > "WHERE link.product_id = prod.product_id " . > "AND link.assembly_notes_id = notes.assembly_notes_id > ". > "AND link.product_id = " . $product_id; > > it works great except for one problem. i want the > assembly_notes_contact (the actual name) instead of > the assembly_notes_contact_id. the result of this > query is stored in an array and then i iterate through > the array before displaying the data. > > assembly_notes_contact_id is a foreign key to the > employee table's primary key. i want to be able to > query the db and get the employee name (instead of his > id) into the recordset array. the employee name is > the same as the assembly_notes_contact. > > is this possible? if so, how? You just need to do another join to the employee table. $sql_notes = "SELECT notes.assembly_notes, notes.assembly_notes_contact_id,employee.name " . "FROM t_product AS prod, t_link_product_assembly_notes AS link, t_assembly_notes AS notes,t_employee as employee " . "WHERE link.product_id = prod.product_id " . "AND link.assembly_notes_id = notes.assembly_notes_id". "AND notes.assembly_notes_contact_id=employee.employee_id". "AND link.product_id = " . $product_id; You will, of course, have to change the table names, etc. to match your table descriptions. Sean
On Tue, Jun 28, 2005 at 02:20:35PM -0600, Sean wrote: > Are there any good resources on stored procedures? I'm not very > familiar with them, so if with regards to php is important, then > resources with regards to php it is then. Thanks. See "Server Programming" in the documentation. The chapter on PL/pgSQL will probably be the most useful: http://www.postgresql.org/docs/8.0/static/server-programming.html http://www.postgresql.org/docs/8.0/static/plpgsql.html (The above links are to the 8.0 documentation; use the documentation for whatever version of PostgreSQL you're running.) See also examples at sites like General Bits: http://www.varlena.com/varlena/GeneralBits/ You could also use a search engine to search for phrases like "CREATE FUNCTION" and "plpgsql". If you have specific questions about how to do something, then ask on the PostgreSQL mailing lists. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
sean, thank you for the help. your suggestion got me really close, but i had to make a slight modification to prevent dupe rows - one for each employee. i needed to put employee_id in my select and use it to equate to assembly_notes_contact_id. here is the final code... SELECT notes.assembly_notes_id, notes.assembly_notes, notes.assembly_notes_contact_id, emp.employee_id, emp.first_name, emp.last_name FROM t_product AS prod, t_link_product_assembly_notes AS link, t_assembly_notes AS notes, t_employee as emp WHERE link.assembly_notes_id = notes.assembly_notes_id AND link.product_id = prod.product_id AND notes.assembly_notes_contact_id = emp.employee_id AND link.product_id = $product i also tried to optimize the query by putting the quickest where clause first, second quickest second, etc... i realize this can be dynamic as more rows may change the total query/return times. thanks again for the guidance. --- Sean Davis <sdavis2@mail.nih.gov> wrote: > > On Jun 28, 2005, at 6:41 PM, > <operationsengineer1@yahoo.com> wrote: > > > i'm not sure if this is the best place to ask (if > > there is a better mailing list, please let me > know)... > > > > i have a sql problem. i have the following > query... > > > > $sql_notes = "SELECT notes.assembly_notes, > > notes.assembly_notes_contact_id " . > > > > "FROM t_product AS prod, > t_link_product_assembly_notes > > AS link, t_assembly_notes AS notes " . > > > > "WHERE link.product_id = prod.product_id " . > > "AND link.assembly_notes_id = > notes.assembly_notes_id > > ". > > "AND link.product_id = " . $product_id; > > > > it works great except for one problem. i want the > > assembly_notes_contact (the actual name) instead > of > > the assembly_notes_contact_id. the result of this > > query is stored in an array and then i iterate > through > > the array before displaying the data. > > > > assembly_notes_contact_id is a foreign key to the > > employee table's primary key. i want to be able > to > > query the db and get the employee name (instead of > his > > id) into the recordset array. the employee name > is > > the same as the assembly_notes_contact. > > > > is this possible? if so, how? > > You just need to do another join to the employee > table. > > $sql_notes = "SELECT notes.assembly_notes, > notes.assembly_notes_contact_id,employee.name " . > > "FROM t_product AS prod, > t_link_product_assembly_notes > AS link, t_assembly_notes AS notes,t_employee as > employee " . > > "WHERE link.product_id = prod.product_id " . > "AND link.assembly_notes_id = > notes.assembly_notes_id". > "AND > notes.assembly_notes_contact_id=employee.employee_id". > "AND link.product_id = " . $product_id; > > You will, of course, have to change the table names, > etc. to match your > table descriptions. > > Sean > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com