As for the RAD tool I'm open to suggestions, but I
think I've read about them all. PHP - MySQL platform.
Does it have to be PHP? If a java RAD is ok I would suggest
http://jdbforms.sourceforge.net/
It does not allow multiple inserts from one page, BUT you can:
1) tack in some html (text area or whatever) to the generated pages allowing the user
to enter as many titles as they are allowed to
2) get what the user has entered out of the response and write a wee bit of code to do
the insertion (in what dbforms calls an interceptor).
For example, in your case, I would create these tables:
Members (1 row per member)
------------------------------------
member_id member_name etc.
A Jones ...
B Smith ...
Primary key(member_id)
Titles (1 row per title)
--------------------------
title_id title_name etc.
1 Bullitt ...
2 Serpico ...
Primary key(title_id)
Member_title (1 row for each title associated with a member)
-----------------------------------------------------------------------
member_id title_id
A 1
A 2
B 2
Primary key(member_id, title_id)
Foreign key #1 (member_id)
Foreign key #2 (title_id)
Using this design I would:
1) use dbforms to generate the initial pages
2) combine / alter them so that:
A) Members is the parent form
B) Member_title is a child form on the parent page (connected by member_id of
course)
In this way, B (the child form) can:
1) show a list of all movies held by that member
2) put as many entry spots as the member has left
3) use a dbforms select box(part of the tag library) that will query Titles to give
the user a choice of titles
Now to enter additional titles you could:
1) have a seperate page - but that is a drag for the user to not see a title and then
have to go to another page to enter it and then return to the entry form where it
could now be selected. Works but...
2) better is to make a custom entry in the select box like "enter new title" and again add a
little html (textarea or whatever) so that when the user selects "enter new title" and adds
something to the box, you can in an interceptor take the parameter out of the request to both:
A) add the new title to title_id
B) take the title_id (see*** below) and insert it with the member_id into
member_title
So in one page the user can:
1) see the movies they have
2) select as many new ones as they are allowed to
3) enter new movie titles as necessary
4) update thier choices
Multiple updates are allowed so that part is easy.
Anyway, you can post to the dbforms list if you need an example of java code to insert
from your interceptor
Cheers,
*** to get the title_id you would have to do something like:
mysql> CREATE TABLE title_id_sequence (id INT NOT NULL);
mysql> INSERT INTO title_id_sequence VALUES (0);
then you would need to [but put this in your interceptor]:
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();
which in your interceptor when inserting the title comes out to be:
try {
PreparedStatement seq1 = con.prepareStatement("UPDATE title_id_sequence SET
id=LAST_INSERT_ID(id+1)");
PreparedStatement seq2 = con.prepareStatement("SELECT LAST_INSERT_ID()");
ResultSet rs = null;
int number;
//Connection con is provided from the interceptor
try {
seq1.executeUpdate();
rs = seq2.executeQuery();
rs.first();
number = rs.getInt(1);
rs.close();
rs = null;
seq1.close();
seq2.close();
seq1 = null;
seq2 = null;
} catch (SQLException e) {
e.printStackTrace();
} finally {
//CODE TO ENSURE EVERYTHING IS CLOSED
}
} catch (SQLException sqle) {
//
}
--
Shawn
Karma is immutable, so act accordingly!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
