hi,

I'm a freelance programmer who was recently contracted to develop a
database-driven database system for a company. The requirements for
this system are fairly strict:

1. MySQL must be used as the back-end database (the client does not
want to spend very much money, and needs a very fast database server).
The client seems to have a personal interest in using MySQL, and
insists that this be the back-end database that I use.

2. The front-end would *ideally* be something that can be incorporated
into a web-browser. E.g. HTML forms could be used as the front-end,
and submitting the forms could call a PHP/Perl/JSP/ASP script that
would update the database. The reason for this requirement is the
client wants to be able to set up client computers very quickly - i.e.
they don't want to have to call me to do some complex software
installation every time they buy a new system.

3. All the client computers (about 30 of them) are currently iMacs,
and the server is a Windows 2000 server, although this could change
down the road. Thus, the solution should be as platform-independent as
possible.

4. This is the most important requirement: The client interface should
feel as much like a desktop application as possible. I.e., if HTML
forms are used as the interface, there should be no "lag" time between
the time the user submits the form and the time that the results are
returned to the client's browser from the server. Obviously,
multi-user database like FileMaker and Oracle would be the best choice
to satisfy this requirement, but given previous requirements, such
solutions would be out of the question. Standard HTML to PHP/Perl
script solutions wouldn't really be adequate either, because of the
lag time that occurs when the page refreshes.

NOTE: I've thought about using Macromedia Flash or Java Applets as the
interface, but the development time would be considerable. I want an
environment that's easy to develop in.

5. Finally, the cost should be minimal. Using open-source software
would be most ideal, and this may be the reason behind requirement #1.
The client doesn't want to spend a lot of money on software outside of
my programming charges, and wants to be assured of free upgrades and
of the persistency of the software (e.g. what if the company that
makes FileMaker goes out of business? Open source software seems to
have a greater staying-power... consider Linux or Apache).

These are the broad parameters of the system. My question is this:
what combination of technologies do you feel would be best suited for
this project?

Please assume that the person programming this system is familiar with
every programming language out there, so technical know-how isn't an
issue. The system is to be used on an *intranet*, so it's a closed
system, but we want to leave open the possibility of putting the
system on the Web down the road.

The system will be used to perform all the normal database
functions: updating records, adding/deleting records, performing
searching, creating reports, etc.

If you could provide a few possibilities and describe the advantages
and disadvantages of each, that would be great.

Thanks.

---

Request for Question Clarification by snapanswer-ga on 23 Dec 2002 22:37 PST

Hi g8z,
This is an interesting question.  I spent a little time on it, but, it
seems that you have two requirements that are directly at odds with
one another.  All of the other contraints seem easy to accomodate.

Perhaps you can shed some light on the following aspect so that myself
or another researcher can help to identify solutions for you to
evaluate further.
(#2) web browser front-end vs. (#4) no lag time during web browser
re-draw

It seems like one or the other of these requirements needs to take
priority.

If you can live without a web browser front-end, there may be some
solutions.  Or, alternatively, if you can live with browser redraw
there are certainly solutions.  However, without choosing one or the
other, I think alternatives might be difficult to find, given that you
have also ruled out enabling technologies such as Flash, Java, and
presumably ActiveX which might otherwise overcome the redraw issue for
you.

Your additional thoughts on this would be of great help.  Perhaps you
would like the researcher to give consideration to techniques to
minimize the visual effect of redraws, in addition to listing
technologies.

---

Clarification of Question by g8z-ga on 24 Dec 2002 11:18 PST

hi,

Unfortunately, the conflicting requirements of #2 and #4 is exactly
why I posted this question to Google - I couldn't think of anything
that would work myself. Neither requirement can really be relaxed at
all.

I was considering the use of .NET, but I think that Windows platforms
are needed, and the development environment for .NET is very pricey.
Since .NET is being incorporated into all new Windows computers, this
would be a consideration if it weren't for the cost and for the fact
that all the current client computers are iMacs (I would reconsider if
someone can show me a free .NET environment for the Mac, and a free
.NET IDE).

I was also considering the use of DHTML layers as the front end and a
PHP/MySQL backend. I.e, the forms would be on layers, and when the
user clicks "Submit", a new layer would come to the forefront while
the back-end processing takes place. This would seem to minimize lag
time and also use a purely HTML + Javascript interface. However, my
understanding of HTML forms is that when submit is pressed, the entire
page refreshes (and all layers must be redrawn). Perhaps some
workaround for this would be possible?

I would consider an ActiveX or Java solution, but I think that the
development time would be excessive. I'd must rather use some tool
like Dreamweaver, which can quickly create dozens of templated pages.
Also, printing the results returned from a database query is extremely
easy if done in HTML, since the browser window has built-in scrolling
to fit the data. Whereas, if returned to a Java applet, things like
text scrolling would probably have to be programmed.

Hi, 

After reading all the comments here and your clarifications, the only
option you really have left is a flash front end for the user, php as
the bridge between MySQL. Flash forms are really very fast to setup.
You can probably make a good looking form in the same time it takes
you to put one together in Dreamweaver. Just do a bit of reading on
reusing templates and objects. Really, it's fast, and much easier to
support in the long run.

Setting up the PHP bridge to the MySQL database is equally as fast,
the server will of course need to have PHP, but there is no
configuration needed as the MySQL functions are now standard in PHP
installs.

Java would be an option if you didn't have the "non-lag" requirement.
Java is going to slow those iMac's down to a crawl, in my experience,
maybe you know some tricks to keep that from happening, but everything
I've seen in Java running on an iMac is really slow. Flash will be
faster and you won't need to worry about the browser crashing.

Cross platform is also the constraint you have which suggests the
flash/PHP option. This will run on any server that PHP will run on.
IIS Apache, whatever. Since the PHP is being called in the background
the Flash screen can change to the next needed entry point without
waiting for PHP to respond. You can also set it up to check the
existence of the server's response, and alert the user that the server
is down from the next page or anywhere in the program. Using the new
Flash MX with global variables and non-frame events, this is very
useful and fast to setup.

Another thing that will help speed up your process is that Flash will
read XML and MySql will give results to queries in XML format using
select statements.

The last thing I will point out here is that a Flash program doesn't
require a browser to run in. Using the app from the desktop will
function just as it would inside a browser window. So if you ever do
have browser problems, just eliminate the problem.

As for printing the reports and query results you can create in Flash
the ability to print these or the best way would be to drop out  to a
PHP created New Window with the results, and print from there. Sending
out a Javascript command from the Flash to it's hosting Browser
window, and tell it to create the new window with the given
parameters. This will work in Netscape 4.5 but not in the newer
Netscape browsers as they changed the back-engine of Netscape to the
Mozilla engine after that and messed up the javascript calling
function. As of this date, there is no fix for this yet in the newer
browsers.

Links

Hot Scripts.com PHP/Scripts and Programs / Flash and PHP
http://www.hotscripts.com/PHP/Scripts_and_Programs/Flash_and_PHP/

Macromedia Flash Technotes Overview 
http://www.macromedia.com/support/flash/ts/documents/flash_database.htm

Scrolling dynamically loaded text in Macromedia Flash
http://www.macromedia.com/support/flash/ts/documents/dynamic_text_scrollmx.htm

Understanding Flash applications
http://www.macromedia.com/support/flash/applications/app_design/app_design04.html

Loading data across domains
http://www.macromedia.com/support/flash/ts/documents/load_xdomain.htm

Flash : An example of communication between JavaScript and Macromedia
Flash
http://www.macromedia.com/support/flash/ts/documents/java_script_comm.htm

Flash - Validating Flash forms with Dreamweaver and the JavaScript
Integration Kit for Flash 5
http://www.macromedia.com/support/flash/programs/form_validations_tut/


I have no clue why, but there are very few Flash apt tutorials that
will show you the real power of this format in business apts, probably
because there are very few people who think business apts are really
all that special. But there are game tutorials and they will show you
the power of persistent variables and non-frame dependant events.

http://flashkit.com/tutorials/Games/



Thanks, 

webadept-ga

---

Request for Answer Clarification by g8z-ga on 04 Jan 2003 17:53 PST

What do you think about using DHTML layers instead? When the user
opens up a URL, about 10 different forms are loaded into memory, and
when a form is filled out, a thank-you layer comes to the foreground,
and the form data is submitted somehow without refreshing the entire
page. A navigation bar would be available to switch among the forms.

Do you think this would work... specifically, is it possible to submit
a form from a layer and getting a response without refreshing the
entire page?

---

Clarification of Answer by webadept-ga on 05 Jan 2003 15:26 PST

I've been looking into this since I saw the clarification request, and
I don't think there is. I may have missed something, but as far as I
can tell, once you submit the information to a cgi to update the
database, the page is going to refresh. You can build the separate
layers and gather all of the information before this happens, that is
possible, but once the "submit" button is pushed you are doing a POST
command, which sends control of the browser to the new page, be that a
CGI script or whatever.

I good example of what you are suggesting is at 
http://myadventuregame.com/

They have a really good interface for creating the games, and use what
you are talking about a great deal. But once the information is
submitted you get the page refresh.

Flash is able to do this in the background without getting the browser
involved.

---

Request for Answer Clarification by g8z-ga on 05 Jan 2003 16:29 PST

How about combining multiple HTML pages or HTML + Flash. E.g. you fill
out an HTML form and the POST data gets sent to a tiny (1 px by 1 px)
Flash movie embedded in the HTML page, so that the Flash movie does
the POST-ing, and the HTML page doesn't refresh.

Or, having two browser windows open, one with the form and one that
receives the form POST data (this second windows might be a tiny
window that sits in the background, or is minimized, which the user
never actually sees).

---

Clarification of Answer by webadept-ga on 05 Jan 2003 17:31 PST

You can do his with javascript, but the only problem here is that it
no longer works with the new versions of Netscape. If this is not a
bother to you, and can dig up my notes on how this is done. There
still doesn't appear to be a fix scheduled for this by either
Macromedia or Netscape themselves.

I had a project last summer that was going to use this type of
display, using Flash for the timing of image changes, and it was
working really good, until I tested it on the new Netscape. Quite
disappointing that. So I wound up doing the whole project in Flash.

webadept-ga

Unfortunately, there wasn't any real good answer that would satisfy
the requirements that I listed, but I think that the researcher did an
excellent job for the difficult situation that I outlined.

Perhaps there is some way to create some sort of cache in the browser
that shows an instant preview of what it will look like and then that
cached information is sent to the main server database while the user
is inputting the data? Unfortunately I'm not sure how to program that.

regards

Well, I'm not sure there is a good answer that can satisfy the
requirements.

I would probably go with the web browser front end, with PHP running
on Apache as a plug-in instead of CGI on the back end.  If you are
running on Windows NT/2000/XP, you may want to look at the e-novative
package which auto-configures PHP, MySQL, and Apache on Windows. Or,
you may want to look at the NuSphere package for Windows and Red Hat
Linux.  A search for PHP Triad may find other packages.

http://www.hotscripts.com/Detailed/18366.html
http://www.nusphere.com/cgi-bin/nsp.cgi/custsrvc/utils/free_download.htm
http://www.hotscripts.com/Detailed/6659.html

If you need to download Linux:
http://www.linuxiso.org/

Then, to help minimize the visual effect of lag time, I might try some
of these transition effects, which I believe will do the redraw "out
of view" of the user.  You would want to set a very short transition
time, but, setting a transition should ensure that a second page is
ready before the first page disappears.
http://internetbrothers.com/dhtml_transitions.htm
http://www.boogiejack.com/howx036.html
http://www.webdevelopersnotes.com/tips/html/20.php3
http://msdn.microsoft.com/library/default.asp?url=/workshop/author/filter/filters.asp

Thanks for the input. Unfortunately, the transition effects only
appear to work with IE. One thing that I inadvertently omitted from my
question was that my client's web browser of choice is Netscape 4.5. I
have no idea why, but I think they've simply been using Netscape for
years and just feel uncomfortable switching to anything else.

Perhaps using servlets, which are semi-compiled, would be faster than
running interpreted PHP scripts. This may reduce the lag time.
Otherwise, I'll probably try to figure out some sort of DHTML
solution, unless someone can suggest a better alternative. The
NuSphere packages seem useful as far as setup goes, but I don't think
that they would actually make the system operate any faster.

You are correct, the packages I pointed to are just meant to make
setup easier.  I believe they are setup to use PHP as a plugin instead
of CGI, which should help performance.  But, I'm sure you would have
ended up with that configuration whether you did it yourself or went
with the pre-packaged gear.

Also, you may want to look into the various PHP caches out there,
including the cache from the developers of PHP.  This prevents the
commands from needing to be reinterpreted each time, greatly improving
performance.

It still would seem that at some point the browser is going to redraw,
and that's going to create the visual sense of delay you are trying to
avoid.

In any event, I wish you luck on this interesting project.

In response to your need for a way to avoid an aparant round-trip
solution to the server, what you could do is use a hidden IFrame to
submit forms and return data to you.  This would elimate having to
worry about a 1x1 px flash movie.

I've used this hidden iframe technique to do exactly what you want to
do.  It's actually pretty simple.  Create a function in client side
javascript that will input the database query paramaters.  The
function would then write this information to a the hidden iframe form
and submit it.  The function could of course also change to a Thank
You Please Wait layer.  When the iframe returns with the results of
the query, it would invoke another function that would write the
results to a layer and then displays the results.

Good luck~!

iframes are IE-specific, right? Is there something analogous in Netscape 4.x or 6.x?

IFrames and DIVs are IE specific.    ILayers and Layers are netscape
specific.  You can use standard cross-browser techniques to
differentiate between the two.