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