At 12:53 PM +0000 1/7/02, George Pitcher wrote:
>
>One of the features of the FMPro solution is that when a user is looking at
>a list of resources (in fact, university course packs) each row will display
>the number of associated records from the 'books' database. Now which is the
>best method to do this (speed/efficiency)? I have tried performing a
>row-level query on the related db but its very slow and times out before the
>second row can be displayed. Alternately, I could set the main db to
>increase/decrease a number field when adding or deleting books from the
>list. Which is recommended?
Not to interrupt with the current discussion, because some of the
issues brought up, will eventually be needed by you. But for the
issue at hand, what you want is SQL. I, too, am an avid FileMaker
Developer, who uses PHP/MySQL or FileMaker/Lasso, FileMaker/PHP for
web-based solutions.
I very much miss my FileMaker calculations, but SQL can handle many
of them. I actually find Filemaker's handling of string functions
(Filemaker calls them text functions) more flexible, and
full-featured then SQL. But with a lot of practice in SQL, you can
replicated (recreate FileMaker level functionality) by using SQL.
For your situation, it sounds like a calculation like the one below
will work. Not knowing what you considered resources, I've basically
mentally associated to categories.
Lets state that you have a library table that stores books, articles,
etc. A category table that stores categories. And a join table called
media_cat that stores all relations between library and category.
That being true every library_item can have one or more categories,
and you wish to create a count of the number of items in each
category. The following query will suffice, and should be adaptable
for your situation:
SELECT library.type, media_cat.category, categories.description,
COUNT(library.type) AS count
FROM categories, media_cat LEFT JOIN library ON
library.libraryID=media_cat.fileID
WHERE categories.category=media_cat.category
GROUP BY media_cat.category
ORDER BY media_cat.category
The results from this could be formatted to display like so:
Communications (with 9 documents)
Donor Relations (with 3 documents)
or like so:
* databases (with 2 items)
applications, query languages, theory, and implementation
* networking (with 1 items)
networks, telecommunications, protocols, routers, etc.
The above explains the more complicated query between three tables,
but you can also do this with a single table, or two tables.
Alnisa
--
.........................................
Alnisa Allgood
Executive Director
Nonprofit Tech
(ph) 415.337.7412 (fx) 415.337.7927
(url) http://www.nonprofit-techworld.org
(url) http://www.nonprofit-tech.org
(url) http://www.tech-library.org
.........................................
Nonprofit Tech E-Update
mailto:[EMAIL PROTECTED]
.........................................
transforming nonprofits through technology
.........................................
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
