Re: update question number in mysql
Hi, Steven and christopher, Thanks a lot for your help. I think i have to store the question number in table. my talbe questionbank store the questions related more than one course. every course has six question sets. so i use question set id and question number as primary key. There is one problem is every time i delete one question for example delete question 5, i have to update all the question numbers after question 5 in this question set.(for example if there is 10 question in this set, i delete question 5 i have to update question numbers for question 6,7 8 9,10 to 5,6,7,8,9) it will take some time. but i think this is for administrator so maybe speed is not very serious issue. Thanks steven for his recommendation for the transactional table i will take a look at it. This is great mailing list. Thanks again bin cai --- Steven Hajducko [EMAIL PROTECTED] wrote: On Fri, 2002-04-12 at 13:52, Christopher Thompson wrote: You shouldn't be storing the questio number at all. You have denormalised your database and this is why you are experiencing this problem. Maybe I don't understand bin's problem, but storing the question # wouldn't seem to be de-normalizing. It would only matter on how he plans on storing it. It would seem to matter on a few things - 1) Is he planning on storing more than 1 exam? 2) Can an exam really have the same question #, twice? If yes, then - Are the questions split upon sections of the exam? Are the questions the same, but have subsections? ( 1a, 1b ? ) From the looks of it, he doesn't seem to want to store more than 1 exam anyways. If he did, he could use a 2 field primary key of exam name and question number. There just isn't enough information on what he wants to do. Instead, store a unique identifier with each one (an 'id' field, autonumbering) and then, when you pull back all the questions, order it by this ID field. Your questions won't have numbers assigned to them but you can do that in whatever app uses the questions. Why not use the question number? It's unique per exam. (Or should be?). I can see the case of doing this would help in his problem of the first and second query, but what if someone enters questions out of order? If he deletes a question, then tries to re-insert it, it would end up being the last question in the exam. You might want to look into using a transactional table bin. Then, you could BEGIN the change, then after your two queries are both completed, commit the change. Therefore, if your computer crashed in the middle of the query, the table would revert to the old form. Of course, the truth is that you _can_ do what you want just using SQL. But trust me, you don't want to. It would be at least O(n^2), I think, and would be a pain to write. On Friday 12 April 2002 2:38 pm, bin cai wrote: Hello, verybody, I am afraid to send my question again. i created a table in mysql database system to store exam questions which has the following column fields: questionnumber(integer),questionbody(String), answers (String) and etc. My problem is if i delete one row(question record) how can iupdate the questionnumber in the easiest way which means mysql can do for me. Is it possible? or i have to write a method to update questionnumber. if In second case i got another worry. for example, i wrote two querys,one is to delete this question record another query is to update the questionnumber,that is , all records whose questionnumbers are greater than the questionnumber of the deleted question should deducted by 1. if at the time After the first query is executed successfully and the second query is being executed, the computer is crashed. the second query will be rolled back. so the question is deleted but the questionnumber is not updated. this will result in a mess. If anybody can give me some hint i will appreicate very much Have a good weekend bin __ Music, Movies, Sports, Games! http://entertainment.yahoo.ca - 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?
update question number in mysql
Hello, verybody, I am afraid to send my question again. i created a table in mysql database system to store exam questions which has the following column fields: questionnumber(integer),questionbody(String), answers (String) and etc. My problem is if i delete one row(question record) how can iupdate the questionnumber in the easiest way which means mysql can do for me. Is it possible? or i have to write a method to update questionnumber. if In second case i got another worry. for example, i wrote two querys,one is to delete this question record another query is to update the questionnumber,that is , all records whose questionnumbers are greater than the questionnumber of the deleted question should deducted by 1. if at the time After the first query is executed successfully and the second query is being executed, the computer is crashed. the second query will be rolled back. so the question is deleted but the questionnumber is not updated. this will result in a mess. If anybody can give me some hint i will appreicate very much Have a good weekend bin __ Music, Movies, Sports, Games! http://entertainment.yahoo.ca - 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: update question number in mysql
You shouldn't be storing the questio number at all. You have denormalised your database and this is why you are experiencing this problem. Instead, store a unique identifier with each one (an 'id' field, autonumbering) and then, when you pull back all the questions, order it by this ID field. Your questions won't have numbers assigned to them but you can do that in whatever app uses the questions. Of course, the truth is that you _can_ do what you want just using SQL. But trust me, you don't want to. It would be at least O(n^2), I think, and would be a pain to write. On Friday 12 April 2002 2:38 pm, bin cai wrote: Hello, verybody, I am afraid to send my question again. i created a table in mysql database system to store exam questions which has the following column fields: questionnumber(integer),questionbody(String), answers (String) and etc. My problem is if i delete one row(question record) how can iupdate the questionnumber in the easiest way which means mysql can do for me. Is it possible? or i have to write a method to update questionnumber. if In second case i got another worry. for example, i wrote two querys,one is to delete this question record another query is to update the questionnumber,that is , all records whose questionnumbers are greater than the questionnumber of the deleted question should deducted by 1. if at the time After the first query is executed successfully and the second query is being executed, the computer is crashed. the second query will be rolled back. so the question is deleted but the questionnumber is not updated. this will result in a mess. If anybody can give me some hint i will appreicate very much Have a good weekend bin __ Music, Movies, Sports, Games! http://entertainment.yahoo.ca - 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
Re: update question number in mysql
On Fri, 2002-04-12 at 13:52, Christopher Thompson wrote: You shouldn't be storing the questio number at all. You have denormalised your database and this is why you are experiencing this problem. Maybe I don't understand bin's problem, but storing the question # wouldn't seem to be de-normalizing. It would only matter on how he plans on storing it. It would seem to matter on a few things - 1) Is he planning on storing more than 1 exam? 2) Can an exam really have the same question #, twice? If yes, then - Are the questions split upon sections of the exam? Are the questions the same, but have subsections? ( 1a, 1b ? ) From the looks of it, he doesn't seem to want to store more than 1 exam anyways. If he did, he could use a 2 field primary key of exam name and question number. There just isn't enough information on what he wants to do. Instead, store a unique identifier with each one (an 'id' field, autonumbering) and then, when you pull back all the questions, order it by this ID field. Your questions won't have numbers assigned to them but you can do that in whatever app uses the questions. Why not use the question number? It's unique per exam. (Or should be?). I can see the case of doing this would help in his problem of the first and second query, but what if someone enters questions out of order? If he deletes a question, then tries to re-insert it, it would end up being the last question in the exam. You might want to look into using a transactional table bin. Then, you could BEGIN the change, then after your two queries are both completed, commit the change. Therefore, if your computer crashed in the middle of the query, the table would revert to the old form. Of course, the truth is that you _can_ do what you want just using SQL. But trust me, you don't want to. It would be at least O(n^2), I think, and would be a pain to write. On Friday 12 April 2002 2:38 pm, bin cai wrote: Hello, verybody, I am afraid to send my question again. i created a table in mysql database system to store exam questions which has the following column fields: questionnumber(integer),questionbody(String), answers (String) and etc. My problem is if i delete one row(question record) how can iupdate the questionnumber in the easiest way which means mysql can do for me. Is it possible? or i have to write a method to update questionnumber. if In second case i got another worry. for example, i wrote two querys,one is to delete this question record another query is to update the questionnumber,that is , all records whose questionnumbers are greater than the questionnumber of the deleted question should deducted by 1. if at the time After the first query is executed successfully and the second query is being executed, the computer is crashed. the second query will be rolled back. so the question is deleted but the questionnumber is not updated. this will result in a mess. If anybody can give me some hint i will appreicate very much Have a good weekend bin __ Music, Movies, Sports, Games! http://entertainment.yahoo.ca - 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 - 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
