George, > >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.
=As a general comment, it is always dangerous to "replicate" when shifting platforms, better to reverse engineer and then implement anew and taking advantages of the strengths of the new tools. This particularly when moving into the relational field... > >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. =you will need to describe the 'internal calculations' before this question can be easily/sufficiently answered. However many people fail to appreciate that the (My)SQL language offers a lot of power/functionality. In your case you are going for the PHP combination so I will be quite surprised if you 'run out' of functionality! > >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. =as you can see, without giving a little more information, it is very difficult to give a satisfactory answer. How about listing your table definitions/schema. Almost any retrieval operation that does not select all of the records in a table will speed up when indexes are employed. If speed is a concern then that argues against the earlier suggestion of PostGres. =you are talking as if there are numerous queries. What's wrong with performing a join, or am I missing some significance? > >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. =Are you talking about functions implemented in PHP? Refer above, my earlier comments on the power of SQL, check out: SELECT DATE_FORMAT( colNm, format ) FROM tblNm in the manual at 6.3.4 Date and Time Functions. I'll be surprised if the long list of 'formats' doesn't give you more than enough options to keep (even Uni students) quiet! =dn -- 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]
