[Fwd: [Fwd: Re: [Zope-DB] Need help passing table data as a variable to next SQL method]]

[Gil Pangilinan]

Thank you so much for your input.
    The dilemma I'm in is that my initial query of companies alone is 
tremendous, over 500.  To list the employees in the same table is just 
not an option.  What I've been experimenting with is to make the company 
name in the table a hyperlink.  But the troubles I'm having is passing 
the company name, id, and a couple other variables into another SQL 
method as arguments.  The method itself works fine, when I test it in 
zope it creates the 4 fields that need input.  The furthest I've gone is 
to get the hyperlink working to a ZSQL search interface  but again, the 
user needs to input the data manually which is very troublesome.  I'm 
trying to figure out how to automate this process.  This would help the 
user navigating the DB so that he could just click on the company and 
see the branches, and then click on a specific branch and then see the 
employees there. (Yes, I'm trying to do various levels of this.)

I appreciate all the help.
-Gil


Cliff Ford wrote:

> When you compose bit of Python code you need to get used to making 
> mistakes, and fixing them. In the examples below, I forgot to call the 
> queries in context, for example:
>
> for result in context.theQuery():
>
> But like I said, it was a skeleton rather than a working example.
>
> Cliff
>
> -------- Original Message --------
> Subject: [Fwd: Re: [Zope-DB] Need help passing table data as a 
> variable to next SQL    method]
> Date: Sat, 09 Oct 2004 09:31:03 +0100
> From: Cliff Ford <Cliff.Ford at ed.ac.uk>
> Reply-To: Cliff.Ford at ed.ac.uk
> Organization: University of Edinburgh
> To: zope-db at zope.org
>
>
> There are at least two approaches you could use, depending on how much
> data you are returning and how you want it displayed:
>
> 1. You could use a single SQL query to return Company and Employee data,
> by joining the Company and Employee tables. You don't say whether you
> are new to Python too, but it would be best to process the query results
> with a Python script. The query would look something like this:
>
> select Companies.CompanyName, Employees.* from Companies
> join Employees on Companies.CompanyID=Employees.CompanyID
> order by CompanyName, EmployeeName
>
> and the Python would look something like this:
>
> CoID = -1
> for result in theQuery():
>     if result.CompanyID != CoID:
>         # Company has changed so print some Company data
>         print "%d, %s" % (result.CompanyID, result.CompanyName)
>         CoID = result.CompanyID
>     # print the Employee data
>     print "%d, %s" % (result.EmployeeID, result.EmployeeName)
>
> 2. You could have two queries, passing the results of the first as
> parameters to the second. The queries might be:
>
> select * from Companies
> order by CompanyName
>
> select * from Employees where CompanyID=<dtml-sqlvar CompanyID type=int>
> order by EmployeeName
>
> The Python code would then look like this:
>
> for Company in CompaniesQuery():
>     #print out the Company data
>     print "%d, %s" % (Company.CompanyID, Company.CompanyName)
>     for Employee in EmployeesQuery(CompanyID=Comapny.CompanyID)
>         # print the Employee data
>         print "%d, %s" % (Employee.EmployeeID, Employee.EmployeeName)
>
> Note that I have not tested any of this - and it is just a skeleton. I
> hope it helps.
>
> Cliff
>
> Gil Pangilinan wrote:
>
>> Hello,
>> I'm fairly new to Zope, Python, and SQL so bear with me.  I have 
>> successfully created the first table to be viewed but now having 
>> trouble.
>>
>> The first SQL query returned a table of  each row that has 4 pieces 
>> of data.
>> What I'm struggling with now is taking each of those 4 pieces of data 
>> and passing them as arguments for the next SQL method I need to run.
>>
>> Just to give a generic example, this first table displays companies 
>> and their ID's.  I need to pass the company name and ID's to the next 
>> SQL method that will then bring up all the employees for that 
>> specific company and branch.  Any advice pointing me in the right 
>> direction will be greatly appreciated.
>>
>> Thanks,
>> Gil
>>
>> _______________________________________________
>> Zope-DB mailing list
>> Zope-DB at zope.org
>> http://mail.zope.org/mailman/listinfo/zope-db
>
>
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB at zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
>
>