George,
> > =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...
> Perhaps the use of the word 'replicate' was wrong. I am in fact
> re-engineering based on my knowledge of how the whole operation is performed
> (as I designed and built the original Filemaker/Lasso system) and trying to
> preserve the look and feel of the web pages.
Advertising
=makes perfect sense
> > =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!
> The original Filemaker (FM) databases use calculations stored internally.
> You define a field to store the result of a calculation. This could be
> something quite complex or a static number (or string) or data from a
> related database. Filemaker requires a separate database to represent the
> equivalent of a table in standard SQL databases. Some calculation fields can
> be indexed and some cannot (esp those containing related data). I expect
> that I can replace these calculations with functions which I define.
=it sounds as if you are still getting to grips with the advantages and power that SQL
and relational databases
bring to 'filing' tasks. There also is a terrible possibility of terminology
pollution/confusion. When I last
looked at FM (many, many moons ago), I consigned it to "file 13" as being too much of
a 'shoebox' style
'database', and my being more interested in something PC-ish that would run something
more like a
table-relational model (if not SQL), eg Paradox or even Access (make signs for
protection and mutter
incantations to ward off the evil eye...) The problem with 'shoe box' packages was
that they prefered
single-file solutions - it was difficult/impossible to 'relate' two files, except by
producing procedural code
in the package's language. Thus the data itself did not define the linkage, as it does
in the relational view of
the world (which I was more comfortable with coming from a mainframe-view of the
world). Relational databases
are made up of multiple related tables (for table you can read "file", in MySQL). Shoe
box databases are files.
Thus there is no "multiple" and without the code no 'relating'. Does this make sense?
Is it a fair portrayal?
That being the case, you need to jettison your current understandings of some terms
and FM concepts, to be able
to take on board MySQL and relational technology and terminology...
=let's try drawing a picture to 'see' the model you are contemplating using to upgrade
your system: imagine
three boxes in a row across the page. Label the left-hand one "MySQL", the center one
"PHP", and the one on the
right-hand side "HTML" or "browser". We can then add the following functional
descriptions: 'holds the data',
'handles the processing/calculations', 'displays output and collects user input'.
Working at this level it
should be really easy to now add a diagram for the existing FM setup; and further to
draw correspondences
between 'existing' and 'proposed'. However this is really simplistic, mainly because
you can shift a lot of
'calculation' that FM requires/embodies, out of the central box and over to "MySQL".
For example, the table
relationships, eg a link between the name of a 'package' in a list/table of packages,
and the names of the
(multiple) books within the package in a list/table of books. The 'functionality' can
be implemented using the
power of the SQL language (see talk of dates etc, below), but much will be 'hidden'
within the standard
functionality of the RDBMS engine and/or implicit in the relational model. (yes, back
to the 'more reading'
theme).
=If you want to persue this discussion a bit further, could you list the current FM
'databases'/files and
(briefly) describe how they 'fit' together?
> > =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.
> I think that the table definition list would be far too long for this list.
> Speed is an issue as at the moment I am doing all this under my own steam in
> my own time (partly to extend my skills) and hope to be able to persuade my
> bosses that this would be a beneficial move (they are very conservative).
> Performance improvements would help.
> > =you are talking as if there are numerous queries. What's wrong with
> performing a join, or am I missing some
> > significance?
> No but I probably am. I have been working almost exclusively with Filemaker
> for the past 7 years moving from Mac to Win NT in the process, with a short
> flirtation with MS Access and ASP. I'm really a SQL newbie and am gradually
> getting to grips with what is possible. JOINs are alien to me and I'll ned
> to read up and experiment with them to see how they work and how I can best
> use them.
=as above. If you are at a (?Scottish) university, you shouldn't have too much trouble
finding sources.
Elsewhere on this list you will see "MySQL" by DuBois recommended, also Welling and
Thomson's "PHP and MySQL Web
Development". However for your purposes almost any under-grad text introducing
relational databases and SQL will
probably suit. If you don't understand joins and the concepts offered, then you are
completely missing out the
point/power of relational technology; and what on earth we've been rabbiting on
about...
> > > >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!
>
> I wrote my own functions to handle dates in the way I am comfortable with.
> In dbdate() $input is the date pulled from MySQL and in revdate() $input is
> the dd/mm/yyyy date being grabbed from the users form data.
>
> function dbdate($input)
> {
> $today = explode("-",$input);
> $month = $today[1];
> $day = $today[2];
> $year = $today[0];
> $p_date = $day . "/" . $month . "/" . $year ;
> return $p_date;
> }
> function revdate($input)
> {
> $ztoday = explode("/",$input);
> $day = $ztoday[0];
> $month = $ztoday[1];
> $year = $ztoday[2];
> $revdate = $year . "-" . str_pad($month, 2, "0", STR_PAD_LEFT) . "-" .
> str_pad($day, 2, "0", STR_PAD_LEFT);
> return $revdate;
> }
> By the way, my site is for university staff not sudents.
=by exercising supreme self-control I shall not make any smart comment here...I've
worked in/for my Uni, as well
as being a (lousy) student.
> I welcome any comments and hope to learn by implementation.
=Sorry - this exercise was so unnecessary in that all of this functionality could be
handled by MySQL - however
there is no such thing as 'waste' when you're in a learning situation. You have
learned quite a bit about the
power and capabilities of PHP, which I'm sure is making you think of numerous
advantages over the possibilities
offered by FM.
=Returning to our 'diagram' for a moment, it is prevailing wisdom that the more
'processing' you can shift from
the traditional 'central box' to the (RDBMS) 'box on the left', the more efficient
will be the final result
(read: speed). Remember that whilst the purpose of a 'file' is to 'contain' data,
databases are designed to
'process' data as well. For example (using the two tables drawn from my weak
understanding of your application,
as above) the 'packages' table is something of a 'summary' view of the data contained
in the 'books' list (in so
far as a particular sub-set of the books pertain to a single 'package'). Thus if we
have package X, it is
child's play in MySQL to ask the system to retrieve the names of all of the books (A,
B, and C) in that package
for you:
Table: tbl_packages
1 X
2 Y
3 Z
Table: tbl_books
1 A
2 B
3 C
4 D
5 E
6 F
Relational Query (in SQL):
SELECT packageName, bookName FROM tbl_books, tbl_packages
WHERE tbl_packages.packageNr = tbl_books.packageNr AND tbl_packages.packageName = "X"
giving:
X A
X B
X C
(which you can then pick up in PHP to 'massage' and output as a report in HTML for
display in the
client-browser... - for the purposes of the example I have determined "X", but it
could quite happily have come
from a previous user input/the browser, into PHP which checked and formatted it before
throwing the generated
query at MySQL)
BTW: The first half of the WHERE clause defines the 'join', ie shows how the two
tables are to be 'related'.
This sort of definition can be replicated ad-nauseum so that many tables/lists can be
related to each other.
=Regards,
=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]
