reusing unique id's when deleting/inserting?
Hi all, Here are two tables I'm working with. I apologize if you are not using a monospaced font and they are messed up. This is used by a gradebook program. When a professor adds an assignment to a class he teaches, it inserts the information about the assignment into the assignments table, and inserts the grade each student receives in the student_assignment table. This happens at the same time so the unique ID is generated the same. When he deletes an assignment from the class, it deletes it from both tables. However, after this, if he adds an assignment again, the unique ID generated by insertion into the assignment table is one greater than it should be - i.e., it auto increments the id based on the id of the row that just got deleted, not on the last row that is actually in the table. When the assignment is inserted into the student_assignment table, the unique ID is auto incremented based on the last ID that is actually in the table, not on the row that just got deleted. The workaround I have found is to use last_insert_id() to find the ID that got inserted in to the assignment table, and use this to manually specify the ID for the student_assignment table. This has the effect of putting the ID's in sync, which is what I want, but it seems like there should be a better solution. Any suggestions? Thanks, Peter (tables below) mysql describe assignments; +++--+-+-++ | Field | Type | Null | Key | Default | Extra | +++--+-+-++ | id | int(30)| | PRI | NULL| auto_increment | | title | char(50) | YES | | NULL|| | class_num | char(6)| | | || | weight | float(4,2) | YES | | NULL|| | max_points | int(3) | | | 0 || | type | char(20) | YES | | NULL|| +++--+-+-++ 6 rows in set (0.00 sec) mysql describe student_assignment; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | stu_id| int(10) | | PRI | 0 || | assign_id | int(30) | | PRI | NULL| auto_increment | | grade | int(3) | YES | | NULL|| +---+-+--+-+-++ 3 rows in set (0.00 sec) - 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
Re: RDBMS question on coding expanding series-like fields
Hi Warren,
What I personally would do is simply include some sort of 'ID' field. In
other words, each question would have a unique ID. Question 1's ID would be
1, Question 2's ID would be 2, etc. Or however you wanted to number them.
You could even set this up as an auto_increment and have the next number in
sequence automatically generated when a new question is inserted into the
database.
Then, if you want to call something "Question 1", in your programming
language, simply prepend Question to the ID field. If I were doing this in
Perl, for example, I would do:
print "Question " . $db_query_hash-{'id'};
#where $db_query_hash is the hash of field names and values returned from the
#query, and 'id' is the field containing the id of the question.
The above example would output "Question 1" if the ID selected from the
database were 1.
The main point here is that putting a value like "Quest1" in a database is
(in my opinion) redundant. Simply make the name of the field "Question" and
make the type of the field "num".
Of course I have not been using MySQL that long either, so perhaps some
wizard will have a much smarter answer. :-)
Peter
On Tuesday 27 February 2001 11:24 am, you transmuted into bits these words:
Hi!
I have a Test Questions database. Right now I have a hard-coded limit of
200 questions in it; I actually made a table with field names like Quest1,
Quest2. . . Quest200. However, I know that I am not using the power of
MySql in setting it up this way. This is more an example of my own
ignorance than of the power of MySql!
Can someone explain how to set up a table with a fieldname that 'expands'
(maybe something like Quest[i] where "i" can be incremented as required).
Is there more than one way of doing this? Is there a place where I might
see some sample code?
I did look up 'enum' and 'set' in the manual but I don't feel confident
with my grasp of the limited explanations given of these. My feeling is
that perhaps 'enum' would be a candidate for what I need, as 'set' has a
limit on how big the set can get.I would like to have the possibility
of data expansion as needed.
Any tips whatever would be appreciated!
Thanks very much!
Cheers!
-Warren
-
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
-
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
mod_auth_mysql
Hi all, Does anyone know if mod_auth_mysql is still under active development, and if so, where can I find current information on it? I have searched the list archives here and indeed turned up 254 instances of people having problems with mod_auth_mysql. From the sounds of it, it doesn't look like an easily solved problem. Is there a better alternative to get MySQL authentication with Apache? I have looked at several HOWTOs on compiling apache with mod_auth_mysql, but none have yielded good results. I used the file http://www.mysql.com/Downloads/Contrib/mod_auth_mysql-2.20.tar.gz in the MySQL contribs, but this did not compile. I'm trying to compile it with Apache 1.3.17 and MySQL 3.23.33. Any suggestions as to a better/more current method, or a working method to build this module would be appreciated. Thanks! Peter - 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
