Miles, Thanks for the response.
My description of the Filemaker solution was very simple. In fact it is 13 databases with some containing up to 18000 records. At the centre is a Transaction database containing 15000 'book' records. Each transaction record contains a pack ID field and this is what is queried to produce the 'count' but its painfully slow. The MySQL version of this table contains some 160 fields. In practice, this db table will grow by approx 300 records per week as new transactions are added by users (all UK universities). I also have other queries which need to be carried out to produce the information needed to provide good feedback to users. For instance, each transaction has a 'state': unsubmitted, submitted and complete. I want to be able to show the state of the pack as 'unsubmitted' when all transactions are 'unsubmitted', 'partially submitted' when some have been submitted and others not, 'submitted' when all transactions have been submitted and 'complete' when all transactions are at the 'complete' stage. So with the overall count, that would be 5 queries and I can think of at least one other query which would be needed to cover another process. If a single query is taking so long, what will 6 do to my database's overall performance? Would indexing help? George ----- Original Message ----- From: "Miles Thompson" <[EMAIL PROTECTED]> To: "George Pitcher" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, January 07, 2002 1:29 PM Subject: Re: [PHP-DB] Concept help required > > George, > > Suggestions interspersed below > > Miles > > At 12:53 PM 1/7/2002 +0000, George Pitcher wrote: > >Hi all, > > > >In trying to learn PHP (and MySQL), I am attempting to replicate a > >database-web solution built previously using Filemaker Pro and Lasso (on > >NT). I am currently working with PHP/MySQL on NT and will be moving this to > >Linux before the end of January. > > > >Filemaker is able to perform calculations internally and therefore I guess I > >will need to write functions to mimic this externally. Am I right here? > > MySQL doesn't have stored procedures, if that's what you mean. If they are > really important you might want to consider PostgreSQL. Double-check the > MySQL docs, and a newer version might have this. > > > >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? > > Don't do the second. You're creating a maintenance headache. > > I'm no SQL wizard. Are you fetching information from a courses table, and > displaying the number of associated books for each course to get results > like this, w/o formatting ... > > "The Athenian Galley" U. Stroke 2 > "Grecian Roots and Folk Clothing" C. Me. Bare 4 > > where the number at the end is the number of books for the course? > > select course_name, instructor_name, count(books) > from courses, books > where books.course_id = courses.course_id and (whatever other criteria you > are using to select the course) > > But something tells me that won't work and I'm no SQL wizard. Something > tells me that we're venturing into GROUP BY or HAVING territory. > > > >Dates > >I played around with my learning site over the holidays and found that I was > >not able to easily handle dates between the format required by users > >(dd/mm/yyyy) and that used by MySQL (yyyy-mm-dd) and therefore I wrote > >functions to parse the data both ways. All the example I could find on Dates > >used 'today' as the example. I want to be able to play around with stored > >dates. Is my function method the correct way or is there another way? > > Yes - but why can't we convert the world to that oh-so-simple date format > of year,month,day which sorts and indexes so beautifully and is completely > unambiguous. > > Cheers - Miles Thompson > > > >MTIA > > > >George in Edinburgh > > > -- > 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] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.307 / Virus Database: 168 - Release Date: 11/12/01 _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- 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]
