I have a fairly straightforward problem, and I was hoping for some
advice. If anyone can come up with a more elegant solution to this
problem, I'd be grateful.
I'm developing a small web-based PHP application. One of its features
is that users can go to a certain page, enter in some data about a bunch
of files on a CD ROM, and then submit this information to a MySQL back
end. The data is simple string stuff like descriptions etc.
It's a "multi-part" form, and on the last page of the process, the data
(temporarily stored in hidden variables) is re-displayed back to the
user for confirmation. Once the user hits "Confirm", the SQL insert
executes and the data is added. No problem.
But one of the requirements is that the next page displays a label to
the user, which is what tells the user how to label the CD. This label
is a simple two-part string of numbers, of the form 55:555. The
application itself must intelligently determine the appropriate label.
The mechanism for doing this is simple -- the first two digits
("prefix"), before the colon, are determined by a dropdown list from the
"confirm" page. The hard part is the last three digits ("base"), after
the colon. The number that should appear is the next number in
ascending order, depending on which "prefix" was selected. This means
that if 108 is the highest current "base" value in "prefix" 45, and the
user chooses "prefix" 45 for this particular CD ROM, and submits the
form, then the application must return the number 45:109 to the user in
the next page. Whereas, if the "prefix" that the user selects is 32,
and prefix 32's highest "base" value is 12, then the returned number
should be 32:013. This seems easy to do with PHP, just query for the
current MAX value of "base" WHERE prefix=$user_defined_prefix, and
display this number + 1.
Of course, there is a catch. This new, returned string is also an
attribute of this record, and so needs to be stored somehow in relation
to it (a one-to-one relationship). I was thinking of creating this
table:
mysql> DESCRIBE storage;
+-----------+-----------------------+-----+---------+----------------+
| Field | Type | Key | Default | Extra |
+-----------+-----------------------+-----+---------+----------------+
| stor_id | mediumint(8) unsigned | PRI | NULL | auto_increment |
| stor_pre | smallint(5) unsigned | | 0 | |
| stor_base | smallint(5) unsigned | | 0 | |
+-----------+-----------------------+-----+---------+----------------+
and storing the stor_id as a column in the "files" table (where most of
the file information is located). Thus, part of the "submit" operation
performed by the user will also be constructing a new SQL statement to
insert a new "prefix : base" combination into this "storage" table. In
effect, this means that in the final part of my form, the following will
happen:
1) database is queried for highest "base" value in user-defined "prefix"
2) this number is bumped up by one in PHP
3) this new "base" value is used in a new INSERT statement into the
"storage" table, creating a new row (same "prefix", new "base")
4) PHP function mysql_insert_id() or MySQL function LAST_INSERT_ID() is
used to take the "stor_id" value that was auto-incremented by step 3 and
stores it in a temporary variable ($stor_id)
5) new INSERT statement to insert the descriptions of the files, using
$stor_id as one of the values so that there is a relationship between
the file and its location
6) Success message is echoed to the user, using the user-defined
"prefix" and the new bumped-up "base" value, telling user what to label
the CD (i.e., "45:109").
This seems like a lot of steps, and almost a kludge to me. But then,
this is the first web application I have written ever, and up until now
all of the database work has been simple SELECT or INSERT or UPDATE
statements -- nothing this big yet. Is it normal to have a script that
does all of this in different steps? Could I make better use of MySQL's
resources?
Thanks for your advice on these questions.
Erik
----
Erik Price
Web Developer Temp
Media Lab, H.H. Brown
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
